# Automating pulling data from several sheets into a master sheet

#### Younis

##### New Member
I helping a friend design a model that consolidate data from several sheets (+6) into a master sheet. His client asked him to design the model so that this process will be automated i.e. the user will fill the data in the input sheets and all the data will be pulled into the master sheet. The data sheets have similar column number and titles but the rows vary in numbers and also has empty between them. I have used the below formula to do this but the formula will not work when I introduce indirect function in it:

INDEX(INDIRECT("RCSA_"&\$E36&"_"&\$F36&"!\$C\$25:\$CV\$502"),AGGREGATE(15,6,(ROW(INDIRECT("RCSA_"&\$E36&"_"&\$F36&"!\$AM\$25:\$AM\$502"))-ROW(INDIRECT("RCSA_"&\$E36&"_"&\$F36&"!\$AM\$25"))+1)/(INDIRECT("RCSA_"&\$E36&"_"&\$F36&"!\$AM\$25:\$AM\$502")<>""),ROWS(INDIRECT("RCSA_"&\$E36&"_"&\$F36&"!AM\$25:AM25"))),MATCH('Consolidated RCSAs'!I\$35,INDIRECT("RCSA_"&\$E36&"_"&\$F36&"!\$C\$22:\$CV\$22"),0))

Specifically, I am not able to find away to make the range in this indirect function change INDIRECT("RCSA_"&\$E36&"_"&\$F36&"!AM\$25:AM25") as I pull the formula down.

The other issue with the above formula is that it has to automatically start copying from the next sheet once it finished copying all the data in the first sheet and so on.

### Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

#### RasGhul

##### Well-known Member
Hi Younis,

A formula solution would be difficult to make dynamic and maintain, if VBA is ok you can try this.

I'm not sure if it will handle data with blank rows and make sure to test on a sample copy file first;

Insert a Shape on a new sheet called "Master", right click Shape & assign macro

Code:
``````Sub Rectangle1_Click()

totalsheets = Worksheets.Count
Application.ScreenUpdating = False
For i = 1 To totalsheets
If Worksheets(i).Name <> "Master" Then
lastrow = Worksheets(i).Cells(Rows.Count, 1).End(xlUp).Row
For j = 2 To lastrow
Worksheets(i).Activate
Worksheets(i).Rows(j).Select
Selection.Copy
Worksheets("Master").Activate
lastrow = Worksheets("Master").Cells(Rows.Count, 1).End(xlUp).Row
Worksheets("Master").Cells(lastrow + 1, 1).Select
ActiveSheet.Paste
Sheets("Master").Range("A1").Select
Next
End If
Next
Application.ScreenUpdating = True

End Sub``````

Last edited:

1,102,043
Messages
5,484,361
Members
407,437
Latest member
alfaroM

### This Week's Hot Topics

• Finding issue in If elseif else with For each Loop
Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
• MsgBox Error
Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
• CELL FORMAT - IF CONDITION
My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
• Show numbers nearly the same
Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...