paazan
03-08-2018, 05:37 PM
What I’m trying to do:
I have multiple sheets store in a folder, let’s say the path is “C:\desk\reports”. The number of Excel files will keep growing, and they are of the same setting including the number of sheets and types of information being stored.
What I want to do is combine the information in the same sheets existing in each and every one of these workbooks, let’s say the sheets are called “Data_Source”, into another workbook, which I store in another place. Let’s called it “Master Workbook”. Specifically, the data will be combined and put into a sheet called “Combined_data” in the Master Workbook.l Both of the data source sheet and the “Combined_data” sheet has the same headers so no need to copy but simply keep adding more rows.
The VBA will be run in the Master Workbook so that I don’t have to open each one of the data source workbooks when consolidating them.
Challenges:
1, Data source workbooks are protected workbooks, set up in the Review – Protect Workbook.
2, Data source is, again, stored in the same sheet called “sheet2” in every workbook, and are hidden
3, The data source in Data_Source sheets is liked and reading from other sheets in the same workbook.
Now, the code that I have is a loop going into that folder and open every one of them one by one, and copy past the cells over. But my code would copy the entire range with formula and formatting altogether. This causes two problems: 1, the rows empty but with formulas would be copied over as well, which will mislead the next loop in determining where’s the next empty row to start pasting 2, the copied formula tend to read from incorrect places.
Therefore, I only want values to be copied and pasted.
Please help to solve this. Any other suggestions to simplified the procedure would be greatly appreciated.
Thanks a lot!
21777
I have multiple sheets store in a folder, let’s say the path is “C:\desk\reports”. The number of Excel files will keep growing, and they are of the same setting including the number of sheets and types of information being stored.
What I want to do is combine the information in the same sheets existing in each and every one of these workbooks, let’s say the sheets are called “Data_Source”, into another workbook, which I store in another place. Let’s called it “Master Workbook”. Specifically, the data will be combined and put into a sheet called “Combined_data” in the Master Workbook.l Both of the data source sheet and the “Combined_data” sheet has the same headers so no need to copy but simply keep adding more rows.
The VBA will be run in the Master Workbook so that I don’t have to open each one of the data source workbooks when consolidating them.
Challenges:
1, Data source workbooks are protected workbooks, set up in the Review – Protect Workbook.
2, Data source is, again, stored in the same sheet called “sheet2” in every workbook, and are hidden
3, The data source in Data_Source sheets is liked and reading from other sheets in the same workbook.
Now, the code that I have is a loop going into that folder and open every one of them one by one, and copy past the cells over. But my code would copy the entire range with formula and formatting altogether. This causes two problems: 1, the rows empty but with formulas would be copied over as well, which will mislead the next loop in determining where’s the next empty row to start pasting 2, the copied formula tend to read from incorrect places.
Therefore, I only want values to be copied and pasted.
Please help to solve this. Any other suggestions to simplified the procedure would be greatly appreciated.
Thanks a lot!
21777