Setting user-defined file name as variable and passing to workbooks?

Hi everyone,

I am creating a macro that pulls data from three separate workbooks and combines that data into one book. To do this I have a user form where the source files are set and a destination file and path are assigned by the user. I have created the form which you can run by clicking the cmd button on the first sheet of the attached book. I have the user assign the destination and file name at the bottom of the userform and then the macro pulls that.

Here is the code from the userform:

Please Login or Register  to view this content.

This is only the beginnings of my macro so I am ignoring the other text boxes on the form for now and just trying to get a single source to work all the way through. Once I copy the template sheet, I need to paste it as the first sheet in the new book the user just created. But the macro hangs on the .copy line I think because the variable "DestSource" isn't working?


Any advice on how to reference the newly created workbook later in the code since the name changes each time?

I know I could put the "GetSaveAsFilename" into code like so and lose the destination from my userform:

Please Login or Register  to view this content.

But how would I make that new file the user creates a variable and later reference it to copy sheets into? This way seems more direct that the userform route, so maybe some of you experts could advise me on the most efficient method of acquiring the destination file and path from the user in a way that is referable throughout the macro.

Thanks for the help!

yea. I set the destination and file name to my desktop, click create the summary, and the new file opens renames to the name I provided, and then i get:

Run-time error '9':

Subscript out of range

When I click debug, the line after my comment is highlighted. And the userform is stuck, so I have to click the reset button in the visual basic editor to do anything.

So its working as I want up to the copy line.

oh wait...now that I am looking at this after taking a break...could the issue be that the variable "DestSource" is defined as a string? so it works fine as a file name and path but not as a location?

no thats not it