variable substitution in a VBA statement

I had this statement that works fine:
 ActiveSheet.Copy after:=Workbooks("Tables.xlsx").Sheets(Workbooks("Tables.xlsx").Sheets.Count)

But now I need to use a variable string for the filename (called SaveFn)

this doesn't work
 ActiveSheet.Copy after:="Workbooks(" & SaveFn & ").Sheets(Workbooks(" & SaveFn & ").Sheets.Count)"

what is the proper way to do this?
 
derekackermanAsked:
Who is Participating?
 
zorvek (Kevin Jones)Connect With a Mentor ConsultantCommented:
Have you read my posts? I stated very clearly:

"You use variables the same way you use strings and other constants."

So this statement:

ActiveSheet.Copy after:=Workbooks(SaveFn).Sheets(Workbooks(SaveFn).Sheets.Count)

is the same as:

ActiveSheet.Copy after:=Workbooks("Tables.xlsx").Sheets(Workbooks("Tables.xlsx").Sheets.Count)

Assuming, of course, SaveFn = "Tables.xlsx".

Did you even try my code? I provided this code:

ActiveSheet.Copy after:=NewBook.Sheets(NewBook.Sheets.Count)

With this statement, again, being crystal clear:

"Another way to do this is to keep the variable NewBook and use it."

Kevin
0
 
zorvek (Kevin Jones)ConsultantCommented:
ActiveSheet.Copy after:=Workbooks(SaveFn).Sheets(Workbooks(SaveFn).Sheets.Count)

Kevin
0
 
zorvek (Kevin Jones)ConsultantCommented:
You use variables the same way you use strings and other constants.

Kevin
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
derekackermanAuthor Commented:

SaveFn is a variable

    SaveFn = "Table" & yy & ".xlsx"
    Set NewBook = Workbooks.Add
    With NewBook
        .Title = "CPU Data" & yyyy
        .SaveAs Filename:=SaveFn
    End With

later on in the process, this gets an error:

 ActiveSheet.Copy after:=Workbooks(SaveFn).Sheets(Workbooks(SaveFn).Sheets.Count)

0
 
zorvek (Kevin Jones)ConsultantCommented:
What is the error?

Another way to do this is to keep the variable NewBook and use it:

ActiveSheet.Copy after:=NewBook.Sheets(NewBook.Sheets.Count)

Kevin
0
 
derekackermanAuthor Commented:
are you reading my notes? "SaveFn" is a VARIABLE - NOT a filename, the statement goes to the error handler, have you tested any of the code you sent?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.