software

Hi
In cell A1 i have =cellquot;filenamequot;A1 giving me the
I want to run macro that will save new file with data in A1

Example. tab named wc0701. new file saved as wc0701Any idea's

regards

--
Message posted via OfficeKB.com
www.officekb.com/Uwe/Forums.a...l-new/200601/1

You could just drop the formula in A1 and use the worksheet name itself.

with activesheet
.parent.saveas filename:=quot;C:\quot; amp; .name amp; quot;.xlsquot;, ...rest of options
end with
quot;Brian Thompson via OfficeKB.comquot; wrote:
gt;
gt; Hi
gt; In cell A1 i have =cellquot;filenamequot;A1 giving me the
gt; I want to run macro that will save new file with data in A1
gt;
gt; Example. tab named wc0701. new file saved as wc0701
gt;
gt; Any idea's
gt;
gt; regards
gt;
gt; --
gt; Message posted via OfficeKB.com
gt; www.officekb.com/Uwe/Forums.a...l-new/200601/1

--

Dave Peterson

Hi Dave
Sorry not understand
The workbook is saved as quot;namequot;
each week one tab will be formatted and then saved as the quot;tab namequot; of in my
case as cell A1 owing to the formula in A1
I,m not fully ofay with VBA, however, can edit the existing macro with the
correct lines

Hope this makes sence

brian

Dave Peterson wrote:
gt;You could just drop the formula in A1 and use the worksheet name itself.
gt;
gt;with activesheet
gt; .parent.saveas filename:=quot;C:\quot; amp; .name amp; quot;.xlsquot;, ...rest of options
gt;end with
gt;
gt;gt; Hi
gt;gt; In cell A1 i have =cellquot;filenamequot;A1 giving me the
gt;[quoted text clipped - 5 lines]
gt;gt;
gt;gt; regards
gt;

--
Message posted via OfficeKB.com
www.officekb.com/Uwe/Forums.a...l-new/200601/1

with activesheet
.parent.saveas filename:=quot;C:\quot; amp; .name amp; quot;.xlsquot;, ...rest of options
end with

The dots in front of .parent and .name refer to the object in the previous with
statement. In this case, they refer to the active sheet.

So this says to use the activesheet and save the parent (the workbook that
contains that activesheet) to the C:\ folder as the name of that active sheet
(.name still refers to the activesheet).

If you tried this and got your file saved as quot;NAMEquot;, then I'd bet you didn't
copy|paste correctly (or changed somet stuff after).

The formula:
=cell(quot;Filenamequot;,a1)
returns the name of the worksheet that holds the formula.

But that name can be obtained directly from the worksheet name itself.

quot;Brian Thompson via OfficeKB.comquot; wrote:
gt;
gt; Hi Dave
gt; Sorry not understand
gt; The workbook is saved as quot;namequot;
gt; each week one tab will be formatted and then saved as the quot;tab namequot; of in my
gt; case as cell A1 owing to the formula in A1
gt; I,m not fully ofay with VBA, however, can edit the existing macro with the
gt; correct lines
gt;
gt; Hope this makes sence
gt;
gt; brian
gt;
gt; Dave Peterson wrote:
gt; gt;You could just drop the formula in A1 and use the worksheet name itself.
gt; gt;
gt; gt;with activesheet
gt; gt; .parent.saveas filename:=quot;C:\quot; amp; .name amp; quot;.xlsquot;, ...rest of options
gt; gt;end with
gt; gt;
gt; gt;gt; Hi
gt; gt;gt; In cell A1 i have =cellquot;filenamequot;A1 giving me the
gt; gt;[quoted text clipped - 5 lines]
gt; gt;gt;
gt; gt;gt; regards
gt; gt;
gt;
gt; --
gt; Message posted via OfficeKB.com
gt; www.officekb.com/Uwe/Forums.a...l-new/200601/1

--

Dave Peterson

Hi
I am not sure, hope this explains
Workbook saved as Yard Summary
52 shhets in workbook, one for each week
Weekly i update one sheet and copy sheet, then paste special to new sheet ,
format, and quot;save as quot;new sheet as per the tab name
Incidentaly, I do have the cell A1 in each sheet =Cell(quot;filenamequot;,A1) giving
the tab name in the sheet
Q. Is macro for the quot;save asquot; section possible?

regards
Dave Peterson wrote:
gt;with activesheet
gt; .parent.saveas filename:=quot;C:\quot; amp; .name amp; quot;.xlsquot;, ...rest of options
gt;end with
gt;
gt;The dots in front of .parent and .name refer to the object in the previous with
gt;statement. In this case, they refer to the active sheet.
gt;
gt;So this says to use the activesheet and save the parent (the workbook that
gt;contains that activesheet) to the C:\ folder as the name of that active sheet
gt;(.name still refers to the activesheet).
gt;
gt;If you tried this and got your file saved as quot;NAMEquot;, then I'd bet you didn't
gt;copy|paste correctly (or changed somet stuff after).
gt;
gt;The formula:
gt;=cell(quot;Filenamequot;,a1)
gt;returns the name of the worksheet that holds the formula.
gt;
gt;But that name can be obtained directly from the worksheet name itself.
gt;
gt;gt; Hi Dave
gt;gt; Sorry not understand
gt;[quoted text clipped - 19 lines]
gt;gt; gt;gt;
gt;gt; gt;gt; regards
gt;

--
Message posted via www.officekb.com

I would have thought that last suggestion would have done it for you.

quot;Brian Thompson via OfficeKB.comquot; wrote:
gt;
gt; Hi
gt; I am not sure, hope this explains
gt; Workbook saved as Yard Summary
gt; 52 shhets in workbook, one for each week
gt; Weekly i update one sheet and copy sheet, then paste special to new sheet ,
gt; format, and quot;save as quot;new sheet as per the tab name
gt; Incidentaly, I do have the cell A1 in each sheet =Cell(quot;filenamequot;,A1) giving
gt; the tab name in the sheet
gt; Q. Is macro for the quot;save asquot; section possible?
gt;
gt; regards
gt;
gt; Dave Peterson wrote:
gt; gt;with activesheet
gt; gt; .parent.saveas filename:=quot;C:\quot; amp; .name amp; quot;.xlsquot;, ...rest of options
gt; gt;end with
gt; gt;
gt; gt;The dots in front of .parent and .name refer to the object in the previous with
gt; gt;statement. In this case, they refer to the active sheet.
gt; gt;
gt; gt;So this says to use the activesheet and save the parent (the workbook that
gt; gt;contains that activesheet) to the C:\ folder as the name of that active sheet
gt; gt;(.name still refers to the activesheet).
gt; gt;
gt; gt;If you tried this and got your file saved as quot;NAMEquot;, then I'd bet you didn't
gt; gt;copy|paste correctly (or changed somet stuff after).
gt; gt;
gt; gt;The formula:
gt; gt;=cell(quot;Filenamequot;,a1)
gt; gt;returns the name of the worksheet that holds the formula.
gt; gt;
gt; gt;But that name can be obtained directly from the worksheet name itself.
gt; gt;
gt; gt;gt; Hi Dave
gt; gt;gt; Sorry not understand
gt; gt;[quoted text clipped - 19 lines]
gt; gt;gt; gt;gt;
gt; gt;gt; gt;gt; regards
gt; gt;
gt;
gt; --
gt; Message posted via www.officekb.com

--

Dave Peterson

not knowledgable enough with command line
got error with below
..parent.saveas filename:=c:/amp;.week1amp;quot;.xls
rian Thompson wrote:
gt;Hi
gt;I am not sure, hope this explains
gt;Workbook saved as Yard Summary
gt;52 shhets in workbook, one for each week
gt;Weekly i update one sheet and copy sheet, then paste special to new sheet ,
gt;format, and quot;save as quot;new sheet as per the tab name
gt;Incidentaly, I do have the cell A1 in each sheet =Cell(quot;filenamequot;,A1) giving
gt;the tab name in the sheet
gt;Q. Is macro for the quot;save asquot; section possible?
gt;
gt;regards
gt;
gt;gt;with activesheet
gt;gt; .parent.saveas filename:=quot;C:\quot; amp; .name amp; quot;.xlsquot;, ...rest of options
gt;[quoted text clipped - 21 lines]
gt;gt;gt; gt;gt;
gt;gt;gt; gt;gt; regards

--
Message posted via www.officekb.com

What's week1?

Maybe:

..parent.saveas filename:=quot;c:\quot; amp; week1 amp;quot;.xls
or
..parent.saveas filename:=quot;c:\week1.xlsquot;

What happened to using the name of the worksheet?quot;Brian Thompson via OfficeKB.comquot; wrote:
gt;
gt; not knowledgable enough with command line
gt; got error with below
gt; .parent.saveas filename:=c:/amp;.week1amp;quot;.xls
gt;
gt; rian Thompson wrote:
gt; gt;Hi
gt; gt;I am not sure, hope this explains
gt; gt;Workbook saved as Yard Summary
gt; gt;52 shhets in workbook, one for each week
gt; gt;Weekly i update one sheet and copy sheet, then paste special to new sheet ,
gt; gt;format, and quot;save as quot;new sheet as per the tab name
gt; gt;Incidentaly, I do have the cell A1 in each sheet =Cell(quot;filenamequot;,A1) giving
gt; gt;the tab name in the sheet
gt; gt;Q. Is macro for the quot;save asquot; section possible?
gt; gt;
gt; gt;regards
gt; gt;
gt; gt;gt;with activesheet
gt; gt;gt; .parent.saveas filename:=quot;C:\quot; amp; .name amp; quot;.xlsquot;, ...rest of options
gt; gt;[quoted text clipped - 21 lines]
gt; gt;gt;gt; gt;gt;
gt; gt;gt;gt; gt;gt; regards
gt;
gt; --
gt; Message posted via www.officekb.com

--

Dave Peterson