insert changing file name into vba code

[kramtelbuab]

I have the following code from recording a macro:

Please Login or Register  to view this content.

I am running the macro in open file "1st 05-01-13 MB.xlsx"
I want to go to file "2nd 05-01-13 MB.xlsx", copy the worksheet "Master" and insert it after the last worksheet in file "1st 05-01-13 MB.xlsx"

The file names will always begin with "1st" and "2nd" but the date and letters may change.
I would like to 'clean up ' the code so that it will pick up the correct file names regardless of the change in date and letters after the "1st " and "2nd ".

Any help would be appreciated.

[patel45]

Please Login or Register  to view this content.

[HaHoBe]

Hi, patel45,

AFAIK ThisWorkbook.Name will always include the extension of the file?

Ciao,
Holger

[kramtelbuab]

Thanks patel45.
But when I do this, wb2name returns PERSONAL.xlsb, so I think I am missing something still probably because i'm so new to vba. The next line of the sub works fine as it returns "1stSONAL.xlsb" from the assignment as expected. The macro is store in my PERSONAL so is why ThisWorkBook.name is returning PERSONAL.XLSB?

I am in open file "1st 05-01-13 MB.xlsx" when I call the main macro where the code I listed contained.
I want to go to file "2nd 05-01-13 MB.xlsx", copy the worksheet "Master" from it and insert it after the last worksheet in file "1st 05-01-13 MB.xlsx"

how do I get wb2name to return "2nd 05-01-13 MB.xlsx"?
Sorry to be so dense. Thanks

[patel45]

Put this code in a module of 1st 05-01-13 MB.xlsm, not in PERSONAL.xlsb, the 2 files must have xlsm extension

Please Login or Register  to view this content.

[HaHoBe]

Hi, patel45,

seems you´re having some kind of problem with this thread:

Please Login or Register  to view this content.

wb2name hasn´t been assigned when being used, and I would have expected wb1name there.

@kramtelbuab:
there is no check if the files are open in Excel but this code would assume the active workbook to be 1st while the code still may be hosted in Personal.xlsb:

Please Login or Register  to view this content.

I really do not like this approach very much and would rather work with GetOpenFilename for opening and setting objects for the workbooks.

Ciao,
Holger

[kramtelbuab]

Patel45: your code worked great once I put it in a module in the "1st" file. Thanks

HaHoBe: As I would like to have the code in the Personal, I tried your code. I got a Compile Error: type mismatch that highlighted "name" in the line

Please Login or Register  to view this content.

Not sure what this means. I will go and do some reading on GetOpenFilename as you recommended it as well.

thanks to you both for your patience and help

[HaHoBe]

Hi, kramtelbuab,

my bad (I had been working with Strings before). It should read

Please Login or Register  to view this content.

without name and will set up an object to the workbook.

@patel45:
will cause a RTE for sure - this is definitely not a thread you have a lot success with your guessing it seems.

Ciao,
Holger

[kramtelbuab]

Thanks to you both for your help. I was able to incorporate the lines from HaHoBe with successful outcome. Love this forum and the willingness of the contributors to help.