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:
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:
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!