Forum Discussion
Copy data from one workbook to another. then delete the original data
Hi All,
I am trying to achieve the following in VBA.
I have a master workbook called Master.xlsm.
I have a number of workbooks called Engineer1 to X
Engineer workbook only contains 1 worksheet (Sheet1)
I would like to be able to copy all the contents from Engineer1 workbook for all the Engineer workbooks to the Master Workbook.
I want to say copy rows 2 to 10 from Engineer 1 workbook and then copy rows 2 to 10 from Engineer2 workbook to the the cells below the data from engineer1.
I have tried a few examples from the internet but I cannot get the code to work.
Please help. I have attached my base files for your easy reference.
Vonryan
The screenshot is very close to that given by working versions.
One thought is that the unit of interaction between PowerQuery and Excel is the Table. I think it may be possible work with Named Ranges or the UsedRegion of a Sheet but they are not the preferred options.
Unless you have already done it, I would suggest going through each of your four files and converting the data to a Table [Ctrl/T]. This will give a default name of Table1 but it can be renamed to give some more meaningful reference such as 'EngineeringTask' (the same name in each file). Then, within the Combine & Edit dialogue, select the Table name to be the basis for extracting data from the sample file.
Fingers crossed this takes us a step forward!
12 Replies
Your approach is possible but these days I would recommend the use of Power Query. Combining a set of files from a folder is core functionality of PQ and once further 'Engineer' files are added to the folder all that is needed is to click the refresh button. Ideally the data should all be contained in Tables
Data ribbon tab
Get Data
From File
From Folder
Choose 'combine'
Select the Table to import from each file
Edit as needed
Close and load to sheet within Master
Peter, thanks for your reply. I have tried your described method but I seem to be struggling.
I have one file Master.xlsm on D:\
I have four files Engineer1 thro Engineer4 on D:\Test\
I go to data ribbon and select New Query\From File\ From Folder and set the path to D:\Test
the query selects the 4 files and I press Combine and load.
THe combinne dialogue window appears and I select Example file as Engineer1. The prview pane show me the layout which is fine.
I press the OK button and the query starts running.
I then get a error message
[Expression.Error] The key didn't match any rows in the table..Please see attached screenshot
What am I doing wrong please
VonryanPeterBartholomew1
The screenshot is very close to that given by working versions.
One thought is that the unit of interaction between PowerQuery and Excel is the Table. I think it may be possible work with Named Ranges or the UsedRegion of a Sheet but they are not the preferred options.
Unless you have already done it, I would suggest going through each of your four files and converting the data to a Table [Ctrl/T]. This will give a default name of Table1 but it can be renamed to give some more meaningful reference such as 'EngineeringTask' (the same name in each file). Then, within the Combine & Edit dialogue, select the Table name to be the basis for extracting data from the sample file.
Fingers crossed this takes us a step forward!
