Reference user created work book later in code? GetSaveAsFilename

**magicbob007** · 06-19-2012, 08:35 PM

Hi gurus,

I am starting to construct a bit of code that will filter criteria in a table on one sheet, copy the filtered data, and paste it to a new workbook that that user specifies the name of. So far I have this:

Please Login or Register  to view this content.

Everything works fine up to line 26:

Please Login or Register  to view this content.

I am using explicit references to the source workbook and worksheet for now. I know that the "sFileName" variable stores the path and file name the user specifies, but how can I reference the newly created workbook if using the variable "sFileName" isn't allowed? How do I go about activating the new spreadsheet since the name will be provided by the user and is not explicit? I suppose I could reference the workbook index but I would prefer a more fool proof method that directly references the new workbook the user just provided.

Thanks for the help,

Magicbob007

**rylo** · 06-19-2012, 08:44 PM

Hi

As the variable has the full file name, including the path, you will have to strip out the workbook name part from the full. One way

Please Login or Register  to view this content.

rylo

**shg** · 06-19-2012, 08:46 PM

You can use the workbook variable:

Please Login or Register  to view this content.

**magicbob007** · 06-19-2012, 08:46 PM

I actually just had a light bulb moment when I realized I store the active workbook name as a variable, so I came up with this and it gets the job done since its executed in the right order:

Please Login or Register  to view this content.

seems to happen every time I post something, I figure it out ten seconds after clicking submit even though I spend half an hour trolling the forum for a solution first. Thanks though!!