Macro to merge 2 sheets to one and keep header row

Hi, I'd say you'd need to try something like:

Sub cheeze83()
sheets("Totals").Visible = True 'jut in case the totals sheet is usually hidden
sheets("PO_1").select
range("A2:F2").select
range(selection, selection.end(xldown)).select
selection.copy
sheets("Totals").select
range("a2").select
selection.end(xldown).select 'this will go to the last row with data to ensure old data isn't overwritten
activecell.offset(1,0).select 'will go one cell down, to the first blank cell
activesheet.paste
sheets("PO_2").select
range("A2:F2").select
range(selection, selection.end(xldown)).select
selection.copy
Sheets("Totals").select
range("A2").select
selection.end(xldown).select
activecell.offset(1,0).select
activesheet.paste

End Sub

or saying that you could just change it around so it works the other way which would be like this

Sub cheeze83()
sheets("Totals").Visible = True 'jut in case the totals sheet is usually hidden
sheets("PO_1").select
range("A2:F2").select
range(selection, selection.end(xldown)).select
selection.copy
sheets("Totals").select
range("a65536").select
selection.end(xlup).select 'this will go to the last row with data to ensure old data isn't overwritten
activecell.offset(1,0).select 'will go one cell down, to the first blank cell
activesheet.paste
sheets("PO_2").select
range("A2:F2").select
range(selection, selection.end(xldown)).select
selection.copy
Sheets("Totals").select
range("A65536").select
selection.end(xlup).select
activecell.offset(1,0).select
activesheet.paste

End Sub

Ah, I'm assuming there is currently no data in the totals spreadsheet?

If this is the case take out the first

selection.end(xldown).select 'this will go to the last row with data to ensure old data isn't overwritten
activecell.offset(1,0).select 'will go one cell down, to the first blank cell

so you are left with

Sub cheeze83()
sheets("Totals").Visible = True 'jut in case the totals sheet is usually hidden
sheets("PO_1").select
range("A2:F2").select
range(selection, selection.end(xldown)).select
selection.copy
sheets("Totals").select
range("a2").select
activesheet.paste
sheets("PO_2").select
range("A2:F2").select
range(selection, selection.end(xldown)).select
selection.copy
Sheets("Totals").select
range("A2").select
selection.end(xldown).select
activecell.offset(1,0).select
activesheet.paste

End Sub

Hi Carcel,

Based on this recent post by me for A.Patel, try this:

Option Explicit
Sub Macro1()

    'Written by Trebor76
    'Visit my website www.excelguru.net.au
    
    'Starting at Row 2, copy all records from the 'PO_1' and 'PO_2' tabs for Col.'s A to F to next available row in Col. A of the 'Totals' tab.
    
    'http://www.excelforum.com/excel-programming-vba-macros/964890-macro-to-merge-2-sheets-to-one-and-keep-header-row.html

    Dim wstMySheet As Worksheet
    Dim rngCell As Range
    Dim lngEndRow As Long, _
        lngPasteRow As Long
    
    Application.ScreenUpdating = False
    
    For Each wstMySheet In ThisWorkbook.Sheets
        If wstMySheet.Name <> "Totals" Then
            lngEndRow = wstMySheet.Cells(Rows.Count, "A").End(xlUp).Row
            lngPasteRow = Sheets("Totals").Cells(Rows.Count, "A").End(xlUp).Row + 1
            Range(wstMySheet.Cells(2, "A"), wstMySheet.Cells(lngEndRow, "F")).Copy Destination:=Sheets("Totals").Cells(lngPasteRow, "A")
        End If
    Next wstMySheet
    
    Application.ScreenUpdating = True
    
    MsgBox "All applicable rows have now been copied to the ""Totals"" tab.", vbInformation, "Excel Guru"

End Sub

HTH

Robert

For my code - just change

activesheet.paste

to

activesheet.pastespecial paste:=xlvalues