Merge Multiple Sheets

[yorkbay]

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.

 

[Logit]

.
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 :

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]

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!

 

[Logit]

.
Maybe this will give insight :

Excel VBA - search columns by header and paste into new sheet