Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Use VBA to save a file based on the tab name

Status
Not open for further replies.

jrobin5881

Technical User
Mar 10, 2004
228
US
I have a master file in a workbook of about 20 worksheets. I've been experimenting with a vba loop that moves selected sheets out of the master workbook and into a new workbook - and that is working fine. What I want to do is save and close the file based on the name of the first tab of the spreadsheet and I'm having trouble because VBA is using my variable as a literal. I get an error message if I try and move the quotes to make it a variable (that was set in the loop).

Heres the line giving me headaches
ActiveWorkbook.SaveAs "C:\Documents and Settings\JR\My Documents\Sheets(i)"


Any ideas? My test code is below:

Sub looper()

j = 4

For i = 1 To 3

Sheets(Array((i), (j))).Select
Sheets(i).Activate
Sheets(Array((i), (j))).Copy
ActiveWorkbook.SaveAs "C:\Documents and Settings\JR\My Documents\Sheets(i)"
Windows("Book13").Activate


j = j + 1

Next
 
You may try this:
For i = 1 To 3
Sheets(Array(i, j)).Select
Sheets(i).Activate
strName = ActiveSheet.Name
Sheets(Array(i, j)).Copy
ActiveWorkbook.SaveAs "C:\Documents and Settings\JR\My Documents\" & strName
Windows("Book13").Activate
j = j + 1
Next

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886
 
PH

Thanks so much. Works excellent. I was using activesheet.name but got an error message because I had it backwards.(activesheet.name =....) Now I see I should have simply turned it around and had the sheet name tied to the variable to be called later. Thank you again.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top