jrobin5881
Technical User
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
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