Combine sheets that have different data

[matt-gilbert]

Hi all, I have the following code which combines multiple worksheets in a workbook into one sheet called "COMBINED". I would like to modify this code so that it includes the source sheet name in column A of the "COMBINED" sheet and have all of the worksheets pasted from column B onwards. Any help would be appreciated. Thanks.

Sub Merge_Multiple_Sheets_Column_Wise()

Dim Work_Sheets() As String
ReDim Work_Sheets(Sheets.Count)

For i = 0 To Sheets.Count - 1
    Work_Sheets(i) = Sheets(i + 1).Name
Next i

Sheets.Add.Name = "COMBINED"

Dim Column_Index As Integer
Column_Index = Worksheets(1).UsedRange.Cells(1, 1).Column

Dim Row_Index As Integer
Row_Index = 0

For i = 0 To Sheets.Count - 2
    Set Rng = Worksheets(Work_Sheets(i)).UsedRange
    Rng.Copy
'    Worksheets("COMBINED").Cells(Row_Index + 1, Column_Index).PasteSpecial Paste:=xlPasteAllUsingSourceTheme
    Worksheets("COMBINED").Cells(Row_Index + 1, Column_Index).PasteSpecial Paste:=xlPasteValues
    Worksheets("COMBINED").Cells(Row_Index + 1, Column_Index).PasteSpecial Paste:=xlPasteFormats
    Row_Index = Row_Index + Rng.Rows.Count + 1
Next i

Worksheets("COMBINED").UsedRange.WrapText = False

Application.CutCopyMode = False

Range("A1").Select

End Sub

 

[p45cal]

Try changing:
Column_Index = Worksheets(1).UsedRange.Cells(1, 1).Column
to:
Column_Index = Worksheets(1).UsedRange.Cells(1, 1).Column +1

and directly before this line:
Row_Index = Row_Index + Rng.Rows.Count + 1

adding either:

Worksheets("COMBINED").Cells(Row_Index + 1, "A").Resize(Rng.Rows.Count).Value = Work_Sheets(i) 'this one should put the worksheet name in every row

or:

Worksheets("COMBINED").Cells(Row_Index + 1, "A").Value = Work_Sheets(i) 'this one shoud put the worksheet name only on the first row of the copied data.

 

[matt-gilbert]

Hi p45cal, works perfectly. I didn't quite articulate exactly what I wanted in my initial post but your first solution is exactly what I was after ie having the source worksheet sheet name in every row in column A. Many thanks! Matt