Combining Worksheets into one Master Sheet with Sheet Name (VBA)

[mexmanz]

Hi there,

I have the following code which works perfectly to combine and stack multiple sheet column data (35 sheets) into one Master sheet.
What I also need is for the name of each sheet to be copied down column A against each data block coming from a sheet.
Below, it brings two columns of data and pastes into A:B of Master sheet
I need column A to have SheetName and B:C to have the data?

Many thanks

Option Explicit


Sub ConsolidateSheets()
'JBeaucaire (6/26/2009)
'Merge all sheets in a workbook into one summary sheet (stacked)
Dim cs As Worksheet, ws As Worksheet, LR As Long, NR As Long
Application.ScreenUpdating = False



Set cs = Sheets("Master")
cs.Activate
Range("A1:B" & Rows.Count).ClearContents

For Each ws In Worksheets
If ws.Name <> "Master" Then

NR = cs.Range("A" & Rows.Count).End(xlUp).Row + 1
LR = ws.Range("A" & Rows.Count).End(xlUp).Row
ws.Range("AA1:AB" & LR).Copy cs.Range("A" & NR)
End If
Next ws

Application.ScreenUpdating = True
End Function

Original code found here:
mrexcel.com/forum/excel-questions/404998-combining-worksheets-into-one-master-sheet.html

 

[DebugGalpin]

You can change your Ranges of cs to B to paste them there and then use ws as your name in the same logic looks like the below

Set cs = Sheets("Master")
cs.Activate
Range("A1:C" & Rows.Count).ClearContents

For Each ws In Worksheets
If ws.Name <> "Master" Then

NR = cs.Range("A" & Rows.Count).End(xlUp).Row + 1
LR = ws.Range("A" & Rows.Count).End(xlUp).Row
ws.Range("AA1:AB" & LR).Copy cs.Range("B" & NR)

cs.Range("A" & NR & ":A" & NR+LR) = ws

End If
Next ws

might need to just change that NR & LR bit slightly but should work

 

[AlphaFrog]

cs.Range("A" & NR & ":A" & NR+LR) = ws

End If
Next ws

might need to just change that NR & LR bit slightly but should work

Try this...
cs.Range("A" & NR & ":A" & NR + LR - 1) = ws.Name

Or this...
cs.Range("A" & NR).Resize(LR) = ws.Name

 

[mexmanz]

Absolutely lovely,
Thank you so much for swift reply.

I dropped in AlphaFrog's line and did the trick:

cs.Range("A" & NR & ":A" & NR + LR - 1) = ws.Name

Now looks like this:

Sub ConsolidateSheets()
'Merge all sheets in a workbook into one summary sheet (stacked)
Dim cs As Worksheet, ws As Worksheet, LR As Long, NR As Long
Application.ScreenUpdating = False

Set cs = Sheets("Master")
cs.Activate
Range("A1:C" & Rows.Count).ClearContents

For Each ws In Worksheets
If ws.Name <> "Master" Then

NR = cs.Range("A" & Rows.Count).End(xlUp).Row + 1
LR = ws.Range("A" & Rows.Count).End(xlUp).Row
cs.Range("A" & NR & ":A" & NR + LR - 1) = ws.Name
ws.Range("AA1:AB" & LR).Copy cs.Range("B" & NR)

End If
Next ws

Application.ScreenUpdating = True

End Sub
----------------------------

Remember to name the destination sheet 'Master'

All the best

 

[MKDeepala]

please help me to get the data of each cell of a sheet along with the 'sheet name' into 'Master' sheet? Thank you.