VBA copy data from multiple workbooks based on criteria

Jendjieh

New Member
Joined
Feb 9, 2022
Messages
8
Office Version
  1. 365
Platform
  1. Windows
Hello Everyone!

I'm new here and definitely new to VBA as well.
I would like to ask you for help with a simple task that I'm currently doing manually every time, but I believe it could be easily automated using VBA magic.

I have a "master file" workbook containing one big table that needs to be populated based on specific criteria. Data needs to be copied from multiple (sometimes 5 to 10, but also can be 20+) different workbooks, all saved in the same folder. There are two separate worksheets with similar tables and I need to fill both of them using the same files (each workbook has 2 sheets, I want to copy from sheet1 of each source file to sheet1 in the master file and then the same for each sheet2).

Sheet1 (it's actually called FCL, not sure if this info is important or not)
There is already some data in cells C6 - R6, same in every workbook, this is my criteria - I can create a unique ID in cell B6 (I'm thinking about vlookup formula here, would be easier to have it in one cell, but perhaps I'm wrong)
And then based on that criteria (let's say cell B6 as unique info) I need to copy - not an entire row, but cells S6 to DR6, ignoring blank cells, because I do not want to overwrite anything since I'm copying from multiple workbooks.
The same action for each row from 6 to x.
x - this number will change every time, is there a way to enter it at the start of a macro? Like a pop-up window where you can tell how many rows you have to copy?
Of course, my unique id cell will be moving as well from B6 to Bx.

Sheet2 (called LCL)
Criteria data in C6 - S6, - I can create a unique ID in cell B6 (same thing as in Sheet1).
Next, I want to copy - not an entire row, but cells T6 to DS6, ignoring blank cells, because I do not want to overwrite anything since I'm copying from multiple workbooks.
The same action for each row from 6 to x.

The master file will be called "Internal_Master" and source workbooks "Internal_(here I will enter country name)".
In addition, if that is even possible, I want to copy data but keep the original master file formatting (I mean cells formatting), but not copy as values, as there might be some formulas involved.

If you need any additional information, please let me know.

Thank you!
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
I have a "master file" workbook containing one big table that needs to be populated based on specific criteria. Data needs to be copied from multiple (sometimes 5 to 10, but also can be 20+) different workbooks, all saved in the same folder. There are two separate worksheets with similar tables and I need to fill both of them using the same files (each workbook has 2 sheets, I want to copy from sheet1 of each source file to sheet1 in the master file and then the same for each sheet2).
Look into using Power Query to provide a connection to the two workbooks. You can then set the query to update when you open the workbook. It might at first seem daunting, but it is actually quite simple and the most efficient way to accomplish your goal. It removes the need to write a macro.
 
Upvote 0
Guess I just need to learn VBA somehow and try to write a working macro for my problem.
Or continue with manual copy/pasting everything. Oh well.
 
Upvote 0

Forum statistics

Threads
1,223,903
Messages
6,175,284
Members
452,630
Latest member
OdubiYouth

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top