matt-gilbert
Member
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.
Code:
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