Hello Everyone!
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 BCL, 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 the 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 SCL)
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_Final" 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 or cell comments.
If you need any additional information, please let me know.
Thank you so much for any support on this.