Consolidate multiple worksheets into a new sheet

[pkew22]

In Excel 2013, I have a workbook where i want to combine multiple sheets into one worksheet. I have the same headers in each sheet.

I would like to create a sheet called Consolidate and merge the data from the other sheets into this sheet. I consolidate the sheets every three days. When I do, I delete the current Consolidate sheet and run this macro. Can someone show me how to check to see if there is a sheet called Consolidate and delete that sheet if it exists before running the rest of the macro?

Sub mcrConsolidate()
'Consolidate all Unit Dept Worksheets into the Consolidate Sheet


Dim wkShtNum As Integer
On Error Resume Next
'Select first worksheet and add Consolidate sheet to the left
Sheets(1).Select
Worksheets.Add
Sheets(1).Name = "Consolidate"
'Activate first Unit Dept Sheet and copy headings
    Sheets(2).Activate 'Activates second sheet as Consol is now first
    Range("A1:AC1").Copy 'Copy Headings
    Sheets("Consolidate").Select
    Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False 'Paste col widths
        ActiveSheet.Paste  'Consolidate is now the activesheet
        Range("A1").Select
'Paste data in Consolidate


'Sheets("Consolidate").Range("A1").Paste Destination:=Sheets(1).Range("A1")


'Paste data from worksheet 2 to end to Consolidate
For wkShtNum = 2 To Sheets.Count
Sheets(wkShtNum).Activate
Range("A1").Select
Selection.CurrentRegion.Select
'Select everything but the header
Selection.Offset(1, 0).Resize(Selection.Rows.Count - 1).Select
'Find first empty cell in Column A and Paste data
Selection.Copy Destination:=Sheets(1).Range("A6553").End(xlUp)(2)
Next
End Sub

 

[pkew22]

I saw some code by Blade Hunter from a post from 2008. That helped a lot. I have amended this and am running two macros. One checks to see if the Consolidate sheet exists and deletes it and the other consolidates the sheets.

[COLOR=#222222][FONT=Verdana]Sub Consolidate()[/FONT][/COLOR]'Check to see if worksheet Consolidate exists and delete it if it does
'Consolidate Spreadsheet
        mcrDelConsolidate
        mcrConsolidate

End Sub


Private Sub DelConsolidate()
Dim wkSht As Worksheet
For Each wkSht In Worksheets
    If wkSht.Name = "Consolidate" Then
        Sheets("Consolidate").Delete
    End If
Next
End Sub


Private Sub mcrConsolidate()
'Consolidate all Unit Dept Worksheets into the Consolidate Sheet

Dim wkShtNum As Integer
On Error Resume Next
'Select first worksheet and add Consolidate sheet to the left
Sheets(1).Select
Worksheets.Add
Sheets(1).Name = "Consolidate"
'Activate first Unit Dept Sheet and copy headings
    Sheets(2).Activate 'Activates second sheet as Consol is now first
    Range("A1:AC1").Copy 'Copy Headings
    Sheets("Consolidate").Select
    
    Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False 'Paste col widths
        ActiveSheet.Paste  'Consolidate is now the activesheet
        Range("A1").Select
'Paste data in Consolidate


'Sheets("Consolidate").Range("A1").Paste Destination:=Sheets(1).Range("A1")


'Paste data from worksheet 2 to end to Consolidate
For wkShtNum = 2 To Sheets.Count
Sheets(wkShtNum).Activate
Range("A1").Select
Selection.CurrentRegion.Select
'Select everything but the header
Selection.Offset(1, 0).Resize(Selection.Rows.Count - 1).Select
'Find first empty cell in Column A and Paste data
Selection.Copy Destination:=Sheets(1).Range("A6553").End(xlUp)(2)
Next
End Sub