Copy Data From Another Workbook thats in the same folder as the openwork book

[jed38]

I have a macro written to copy data from a different workbook and paste transpose it to the one i have open.
Is there a way to change the reference to the file i want to copy data from to change based on the name of the file i have open or the working directory im in.

all the files are name by a 5 digit number then GA then 001
so the name would look like this and the # would change based on the project.

#####GA001.xlsx name of file i get the data
#####GG001.xlsm name of file the data is being pasted

i guess to explain it better I want the marco to look at the name of the #####GG001.xlsm file that the marco runs in to place the appropiate numbers in the #'s.

is this possible? the files will be located in the same folder. I dont know if it can pick up the directory im in

here is the code

Please Login or Register  to view this content.

This part of the Code is what i want to change the two files are located in the same folder the only thing that would change from project to project would be the first 5 digit number of the file name. the location path will change from project to project. I want to add the marco to the template file so for each project I dont have to go into the code and change the path. I think theres a way to pick up the working directory of the file and then search look for a file named like this #####GA001.xlsx.

Please Login or Register  to view this content.

If you need more explaination i can try to explain it better.

[Norie]

Where exactly do you need to change the code?

Why do you need to change the code?

Is it to open a different file?

[jed38]

I need this part of the code changed

Please Login or Register  to view this content.

to make it dynamic so when i go to a different project i dont need to change the marco every time. I have multiple projects and for each project I will have the two files that the code above calls out. But for each project that 5 digit number (82927 from the code above) at the start of the file name will change for both of the files. The path to the 2 files will be the same but that changes for each project.

so I would put the marco in the templete file so when i start a new project i will save that file to its location and I want the code to be able to known where to look based off the location of the file i have open (the #####GG001.xlsm file) that contains this macro.

does that make sense?

[jed38]

this is what i did i used the activeworkbook.fullname and activeworkbook.name to set the path to the open workbook then used the replace command to find the file i want to open.

pretty cool

Please Login or Register  to view this content.