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