Use VBA to save a file based on the tab name

[jrobin5881]

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

 

[PHV]

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

 

[jrobin5881]

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.