"Consolidate multiple worksheets into one master sheet"

[Marwan69]

Hi Gents,

I'm struggling on the above subject guys. Any help!

Many thanks.

 

[ranman256]

try this...

  'copy all sheets into 1 master sheet
Sub aCopyAllSheetsInto1()
Dim shtTarg As Worksheet, sht As Worksheet
Dim r As Long, c As Long, s As Long
Dim sCol As String


Sheets.Add
Set shtTarg = ActiveSheet
shtTarg.Name = "Master"


For Each sht In Worksheets
   If sht.Name <> shtTarg.Name Then
        s = s + 1
        sht.Activate
        Range("a1").Select
        
        If s = 1 Then
           ActiveSheet.UsedRange.Select    'keep the header
        Else
            r = ActiveSheet.UsedRange.Rows.Count
            c = ActiveSheet.UsedRange.Columns.Count
            sCol = Chr(64 + c)
            Range("A2:" & sCol & r).Select
        End If
        Selection.Copy
     
        shtTarg.Activate
        If s = 1 Then  'keep header
           Range("A1").Select
        Else
           r = ActiveSheet.UsedRange.Rows.Count
           Range("a" & r + 1).Select
        End If
        
        ActiveSheet.Paste
        Application.CutCopyMode = False
   End If
Next
Set sht = Nothing
Set shtTarg = Nothing
End Sub

 

[Marwan69]

AMAZING ranman256!

Many thanks bro...