Hello everybody!
I have a VBA problem which is probably simple for you guys but I could not manage to create something that works. I used the search before but didn't find anything that specifically fits or solves my problem so I was hoping that you can help me. Let me explain what I need to do first:
I have around 50 files in one folder. All files are the same (with different file names obviously). The files have the same structure, the same tabs, the same columns in the tabs. The only difference is the data stored within the tables in the tabs. Some have more rows than others and the values are different. Now I want to copy all rows in a specific tab that are marked with an "x" in column A and want them pasted in a new file so that the data from all 50 files is underneath each other in the new file.
Example:
I have Workbook1, Workbook2 and Workbook3. In each of these Workbooks, my data is stored in Sheet1. Now I want to copy rows 1 and 3 from Workbook1, row 2 from Workbook2 and rows 2 and 5 from Workbook3 into the new file underneath each other (because these are all the rows marked with "x" in column A), so that always the next empty row is used. In the end it should look like this:
Workbook1, row1
Workbook1, row3
Workbook2, row2
Workbook3, row2
Workbook3, row5
First I thought I can implement a macro in each of the Workbooks that opens the new file and then copy&pastes the data in the new file. However this did not work because my data in the new sheet always got overwritten when using this method with successive files. So my next idea was to create a macro in the new file that points to the Workbooks and imports the data from all the Workbooks. This did not work because all the Workbooks have macros due to which I need to manually click "ok" in two message boxes that appear when I open the files.
In the end I don't mind whether I create the macro in the Workbooks or the new file, I just want to extract the "x" marked rows from each of the Workbooks into the new file underneath each other.
I hope that the explanation of my problem is clear enough and that you can help me.
Thank you very much in advance, your help is very much appreciated.
Best,
T.
Bookmarks