Merge Multiple Sheets

yorkbay

New Member
Joined
Feb 7, 2021
Messages
16
Office Version
  1. 2013
Platform
  1. Windows
Hi I'm using this code from a previous thread that's too old to post my question.

I have multiple sheets and want to combine into one Master all of the data
It stops after the first sheet though and I need it to pull all sheets
Each sheet has some headers same and some different but they will all come from 1 set of static headers from an index sheet
Each sheet has it's own 'list of questions' in column A
Responses with headers start in column G of each sheet
Master needs to show all 530 questions (total of all questions on sheets) and no sheet has a duplicate question of another sheet
Master needs to also show each header and it's respective response to question sets
There could be duplicate headers i.e. sheet 1 may have header T1 and sheet 3 could also have header T1 or any combination of headers
Since the Index sheet contains a static list of what the headers could potentially be on each sheet, I first put them in my Master and used the below code, BUT because they can be in any order on the individual sheets this code brought wrong info into the Master:

Dim ws As Worksheet

For Each ws In Worksheets
If ws.Name <> "Master" And ws.Name <> "Response Drop Downs" And ws.Name <> "Audit Log" And ws.Name <> "Policy Rules Document Names" And ws.Name <> "Schedule Type Descriptions" And ws.Name <> "Welcome Instructions" And ws.Name <> "Index" Then
ws.UsedRange.Offset(1).Copy Destination:=Sheets("Master").Range("A" & Rows.Count).End(xlUp).Offset(1)
Next ws
End Sub

So since that one didn't work I removed all the headers in the Master and tried this code which I found in the same thread that said if there were not headers already there that this would work but it doesn't move to all sheets - only the first one.

Here is the code:
Sub Combine2()
Dim ws As Worksheet
Dim nr As Long

nr = 1
For Each ws In Worksheets
If ws.Name <> "Master" And ws.Name <> "Response Drop Downs" And ws.Name <> "Audit Log" And ws.Name <> "Policy Rules Document Names" And ws.Name <> "Schedule Type Descriptions" And ws.Name <> "Welcome Instructions" And ws.Name <> "Index" Then
' If ws.Name <> "Combined" Then
With ws.UsedRange
.Offset(IIf(nr = 1, 0, 1)).Copy Destination:=Sheets("Master").Range("A" & nr)
nr = nr + .Rows.Count - IIf(nr = 1, 0, 1)
End With
End If
Next ws
End Sub

Can someone please tell me what I'm doing wrong? Thank you.
 
Last edited:

Some videos you may like

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

Logit

Well-known Member
Joined
Aug 31, 2016
Messages
3,973
.
First .. please go back and edit your original post, placing all of your code in the INSERT CODE WINDOW. You can activate that small window
by clicking on the POST REPLY menu bar symbol : < / >

To your request ... I don't understand what you are saying about the headers on all the different sheets.
The following macro will copy all sheets (excluding the MASTER sheet) and paste that data into the MASTER sheet, one row below the other :

VBA Code:
Sub ConsolidateSheets()

Dim sh As Object
Dim lr As Long
Dim lr2 As Long

For Each sh In ThisWorkbook.Sheets
    If sh.Name <> "Master" Then
        lr = sh.UsedRange.Rows(sh.UsedRange.Rows.Count).Row
        lr2 = Sheets("Master").UsedRange.Rows(Sheets("Master").UsedRange.Rows.Count).Row
        sh.Range("2:" & lr).Copy Destination:=Sheets("Master").Range("A" & lr2 + 1)
    End If
Next sh

End Sub

The above code presumes the header on each sheet is located in row #1 ... so it excludes copying the first row.
Hopefully you can use the above macro, edited to exclude the same sheets you have already excluded, to accomplish your goal.
 

yorkbay

New Member
Joined
Feb 7, 2021
Messages
16
Office Version
  1. 2013
Platform
  1. Windows
Thank you for the info on the insert code window. I will go back and do that shortly. Here is the problem I am having with the code.
The Index Page lists all the headers available to the user
Because they are static and include all available choices I copied them to the Master

The user can then decide which header they want o give input for i.e. Full Time Hourly, Part Time hourly, Reduced Time Hourly, Full Time Salary, Part Time Salary, Reduced Time Salary, Temp etc.
Each of the other Sheets have 'topics' for which the user can give inputs to questions i.e. Sheet 1 is Punch and Schedules, Sheet 2 is Overtime, Sheet 3 is Holiday Premiums etc.
On each sheet the headers start in G1:EZ1 and can be in any order that the user chose to work on
For example, the user can decide from the Index which sheet to start with Overtime for RT Hourly and FT Hourly and this sheet may never have any of the Salary Headers because salary folks don't have any rules for overtime as they don't qualify
Therefore on any sheet there may be all of the headers or only some of the headers or none of the headers

When I run the code all sheets are below each other which is what I want, BUT the data all falls in Columns G:EZ under the wrong headers because of the fact that they are no in the same order on the sheets etc.

How would I change the code to accommodate this? I have tried all kinds of ways to manipulate without success and am probably outside of my expertise as I'm very new to all of this. Thank you in advance for your assistance!
 

Watch MrExcel Video

Forum statistics

Threads
1,127,768
Messages
5,626,771
Members
416,202
Latest member
donya ba

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top