use cell value to open up workbook and copy contents into master sheet

Hello. I have an open workbook, in cell B3 I want to be able to write a month value that will open up a file that I have in a folder and copy all of the contents from the first tab into a tab in the open workbook that is named after the value in B3. The file is in a folder called "GL Import" and the name of the file is "april 2015 - gl detail". So if the value in B3 is april, it will open the file and copy, paste special values, into a new tab called april. Thanks!!

Just a quick question Do the Tabs named January....December already exist in your Master File

Mate I have attached a file that might help with your Problem. In order for me to get it to work tho I had to use Data Files without spaces and without "-" in the name. You would need to change the Path references to your path and adjust the ranges from that which I used in this example.

Lots of questions:

1) Why do you have to delete the spaces and the "-"? Not a big deal but just curious.
2) What is the "file exists" function? How do you create that function in vba?
3) For your data range, I just want to copy the whole sheet.
4) The code fails when it gets to set datato= range(MasterData). It says method range of object global failed.

1. The spaces issue is one I have come across previously and I did find a work around but I cannot recall how, perhaps someone else may know the answer to that
2. The FileExists is a function in the "Myfunx" module, I use this a lot to check whether a file is present or not
3. I am not sure how to do the whole sheet with this method, a quick fix is to make sure the range is adequately large enough
4. MasterData range will include your active workbook name, does this also contain spaces? (Refer to 1. above) .. The FileData will have to have your sheet name, I just used "Data" as a sheet name to test the code

Found the answer to the Spaces in Names Problem, note the placement of the single quotation characters in the amended code below

Please Login or Register  to view this content.