Consolidate data from selected sheets into master sheet & automatically update data.

[helplessnoobatexcel]

Hi All,
I'm relatively new to VBA and have no idea how to write a macro code to run what I want it to do. I've read through multiple threads but am unable to find a code that does exactly what I want it to do.
Sub Merge_Sheets()

Dim startRow, startCol, lastRow, lastCol As Long
Dim headers As Range

'Set Master sheet for consolidation
Set mtr = Worksheets("Master")

Set wb = ThisWorkbook
'Get Headers
Set headers = Application.InputBox("Select the Headers", Type:=8)

'Copy Headers into master
headers.Copy mtr.Range("A1")
startRow = headers.Row + 1
startCol = headers.Column

Debug.Print startRow, startCol
'loop through all sheets
For Each ws In wb.Worksheets
'except the master sheet from looping
If ws.Name <> "Master" Then
ws.Activate
lastRow = Cells(Rows.Count, startCol).End(xlUp).Row
lastCol = Cells(startRow, Columns.Count).End(xlToLeft).Column
'get data from each worksheet and copy it into Master sheet
Range(Cells(startRow, startCol), Cells(lastRow, lastCol)).Copy _
mtr.Range("A" & mtr.Cells(Rows.Count, 1).End(xlUp).Row + 1)
End If
Next ws

Worksheets("Master").Activate

End Sub
This is the closest I have gotten to which would compile ALL sheets into one master sheet. Any ideas on how I can modify/add on to this code? Thanks!! I'm gna pass out ;-;

 

[Joe4]

Your code seems to work fine for me, on the simple example I set up.
How exactly is it not working for you?

Maybe you can show us some examples of how your sheets/data are stuctured.
MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

One minor thing. It shouldn't cause any errors, but this line does not do what you think it does:

Dim startRow, startCol, lastRow, lastCol As Long

That will declare "lastCol" as a Long Integer, but all the other variables in that statement will be treated as Variant.
Each variable needs to be declared explicitly, either like this:

Dim startRow As Long, startCol As Long, lastRow As Long, lastCol As Long

or like this:

Dim startRow As Long
Dim startCol As Long
Dim lastRow As Long
Dim lastCol As Long

 

[helplessnoobatexcel]

Your code seems to work fine for me, on the simple example I set up.
How exactly is it not working for you?

Maybe you can show us some examples of how your sheets/data are stuctured.
MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

One minor thing. It shouldn't cause any errors, but this line does not do what you think it does:

Dim startRow, startCol, lastRow, lastCol As Long

That will declare "lastCol" as a Long Integer, but all the other variables in that statement will be treated as Variant.
Each variable needs to be declared explicitly, either like this:

Dim startRow As Long, startCol As Long, lastRow As Long, lastCol As Long

or like this:

Dim startRow As Long
Dim startCol As Long
Dim lastRow As Long
Dim lastCol As Long

Hi Joe, it's not that the code isn't working for me, it works fine, it's just I would like to modify it but I am unsure of how to do so. As of now, it would automatically consolidate all sheets in the excel workbook into one master sheet but I would like to be able to select the sheets that would be consolidated & allow the data in the master sheet to be updated in real time.. Any suggestions on how I can do so, preferably through VBA macro (reduce admin work for copying & pasting) would be very greatly appreciated .

 

[Joe4]

So, are you looking for a run-time option that the user can select any sheets at any time, and it would just run against those sheets?
If so, we would need a way to input/select the sheets.
Some options include:
- have a list of all the sheet names somewhere on the master sheet, where you can select which sheets you want it to process through
- have some sort of indicator on each sheet that indicates whether or not to include it

If the sheet to include or exclude is a static list that never changes, we could program that right into the code.

 

[Joe4]

If you want to try to run it through just the selected sheets, have a look at the code found here: Loop through selected sheets with VBA - Excel Off The Grid

 

[Joe4]

So all you should have to do is change this line of your code:

For Each ws In wb.Worksheets

to this:

For Each ws In ActiveWindow.SelectedSheets

 

[helplessnoobatexcel]

So, are you looking for a run-time option that the user can select any sheets at any time, and it would just run against those sheets?
If so, we would need a way to input/select the sheets.
Some options include:
- have a list of all the sheet names somewhere on the master sheet, where you can select which sheets you want it to process through
- have some sort of indicator on each sheet that indicates whether or not to include it

If the sheet to include or exclude is a static list that never changes, we could program that right into the code.

Yep, exactly. I would also like the master sheet to be automatically updated when new changes are made to the selected sheets if possible.

 

[helplessnoobatexcel]

So all you should have to do is change this line of your code:

For Each ws In wb.Worksheets

to this:

For Each ws In ActiveWindow.SelectedSheets

Perfect! I have changed my code and now I am able to select which sheets I want! Thanks a lot for your help Joe!

 

[Joe4]

Perfect! I have changed my code and now I am able to select which sheets I want! Thanks a lot for your help Joe!

You are welcome!
Glad I was able to help!