save file using information from a cell

[Lynn]

I have a .xls workbook called Data Load.
I have a sheet called Macro (holding my radio buttons assigned to macros).
I have another sheet called item_mass.

I want to keep my .xls workbook as Data Load.xls.
Within the macro I wanted to move/copy the item_mass sheet and then save it
as the name in cell D3 of the Macro sheet.

'Select Sheet
Sheets("item_mass").Select
'Copy to new workbook
' Sheets("item_mass").Copy
Dim fname
With ActiveWorkbook
fname = .Worksheets("Macro").Range("D3").Value & ".csv"
..SaveAs "C:\Documents and Settings\a01760\Desktop\Data_Loads\Prod
Trans\130\fname"
End With

ActiveWindow.Close
Sheets("Macro").Select

This is not working.

 

[Bob Bridges]

Looking at your code and ignoring what you say about it, what I see is this:
a) You get a file name (like "Data Load2", perhaps) from Macro!D3, and add
".csv" to it. Then b) you do a SaveAs, using "fname" as the file name.
Note: The file name is "fname", not "Data Load2.csv" (or whatever). That's
because you have "fnote" inside the quotes. You probably want that SaveAs to
look like this:

.SaveAs "C:\Documents and Settings\a01760\Desktop\" & _
"Data_Loads\Prod Trans\130\" & fname

Now, for the rest, you say you want to move/copy another sheet before you do
the SaveAs. But nothing in your code tries to do that; you select that
sheet, but the only other statement that refers to it is commented out, and
it wouldn't copy it TO anywhere, just "take a snapshot" so to speak. If you
want to copy that worksheet somewhere, we'll have to talk about the details a
bit more. Forget selecting it; what you need to copy it somewhere is a
statement something like this:

Sheets("item_mass").Copy After := Workbooks("W.xls").Worksheets("S2")