Pasting data to a master excel sheet

Make step 3 before 1 and 2
and then you can combine step 4 with step 2.
This shall be enough.


If that's not enough please attach a SMALL sample Excel workbooks (Master and sample data). Please don't attach a picture of one (no-one will want to re-type all your stuff before starting).

1. Make sure that your sample data are truly REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired solution is also shown (mock up the results manually).

3. Make sure that all confidential information is removed first!!


Unfortunately the attachment icon doesn't work at the moment. So, to attach an Excel file you have to do the following: Just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

Kaper says the right thing

Make step 3 before 1 and 2
and then you can combine step 4 with step 2.

As I understood, your algo would be like this:
1. Open master file
2. Loop through Excel files with iterator i which stands for current row of master file. Both current file and master file should be opened at the same time.
2.1 Open current Excel file
2.2 Loop through Workbook.Sheets collection using iterator j which stands for current column in master file (current cell).
2.2.1 Copy H4 value from 'j'th sheet
2.2.2 Paste it into master_sheet.Cells(i, j)
2.3 Close current Excel file properly!
3. Close master file properly

Ask if some extra help with code needed. And please post final code here as a solution.

I'd just add
2.1.1 Write current Excelf file name into master_sheet.Cells(i, 1)
and then change
2.2.2 Paste it into master_sheet.Cells(i, j+1)
or may be as output was expected in

I2 to T2

7 and 8 (if i and j starts from 1) shall be used respectively

As for my point of view - you can delete this line. It does nothing useful.

And kind reminder about forum https://www.excelforum.com/forum-rul...rum-rules.html

Don't quote whole posts -- it's just clutter. If you are responding to a post out of sequence, limit quoted content to a few relevant lines that makes clear to whom and what you are responding

For normal conversational replies, try using the QUICK REPLY box below.

If you're waiting that somebody will write the code entirely for you, you might be waiting for a long time.
I'll give you some clues. Here's what I've advised you to do:

1. Open master file
2. Loop through Excel files with iterator i which stands for current row of master file. Both current file and master file should be opened at the same time.
2.1 Open current Excel file
2.2 Loop through Workbook.Sheets collection using iterator j which stands for current column in master file (current cell).
2.2.1 Copy H4 value from 'j'th sheet
2.2.2 Paste it into master_sheet.Cells(i, j)
2.3 Close current Excel file properly!
3. Close master file properly

You should write and execute this procedure in master file. So it will be open by default. So you don't need to write the step 1.
Step 2 is actually just Do While <...> Loop cycle iterating on files in the folder. You've got that cycle in code but you haven't got a counter variable which is necessary to enable writing each file in different row. It will look like this:

Please Login or Register  to view this content.

Step 2.1 is there already in code, inside the loop
Step 2.2 is much the same as step 2. It's a loop inside a loop. Loop on j can just go through all the sheets. As sheets have indexing, we can use For loop here. Both loops look like this:

Please Login or Register  to view this content.

Hope that helps