VBA COPY PASTE DYNAMIC RANGE EXCEPT HEADERS

[DeanP]

I am attempting to copy- paste special- values & formats for all the data EXCEPT row 1 (headers) from one sheet to another. After researching I am no wiser as to how to:

(a) exclude row 1 from the range
(b) use a dynamic range code for the rest of the data (it changes from month to month)

So far I have this:

Sub CopySheet()
Dim SourceRange As Range, DestRange As Range
Dim DestSheet As Worksheet, Lr As Long


With Application
.ScreenUpdating = False
.EnableEvents = False
End With


Set SourceRange = Sheets("NewData").Range(" ")


Set DestSheet = Sheets("2018 Details")
Lr = LastRow(DestSheet)


Set DestRange = DestSheet.Range("A" & Lr + 1)


SourceRange.Copy
DestRange.PasteSpecial _
Paste:=xlPasteValuesAndNumberFormats, _
operation:=xlPasteSpecialOperationNone, _
skipblanks:=False, _
Transpose:=False
Application.CutCopyMode = False


With Application
.ScreenUpdating = True
.EnableEvents = True
End With

Any help much appreciated.

[Leith Ross]

Hello DeanP,

Try this version of your macro...

Sub CopySheet()


    Dim SourceRange As Range
    Dim DestRange   As Range
    Dim RngBeg      As Range
    Dim RngEnd      As Range
    Dim DestSheet   As Worksheet


        With Application
            .ScreenUpdating = False
            .EnableEvents = False
        End With

        ' // NOTE: Be sure to fill in the starting cell before running the macro.
        Set SourceRange = Sheets("NewData").Range(" ")
        
        ' // Start at row 2. First row is assumed to be the header row.
        Set RngBeg = SourceRange.Cells(2, 1)
        Set RngEnd = Worksheets("NewData").Cells(Rows.Count, SourceRange.Column).End(xlUp)
        If RngEnd.Row < RngBeg.Row Then Set RngEnd = RngBeg
        
        Set SourceRange = Worksheets("New Data").Range(RngBeg, RngEnd)
            
        Set DestSheet = Sheets("2018 Details")
        
        Set RngEnd = DestSheet.Cells(Rows.Count, "A").End(xlUp)
        Set DestRange = RngEnd.Offset(1, 0)


            SourceRange.Copy
            DestRange.PasteSpecial _
                    Paste:=xlPasteValuesAndNumberFormats, _
                    operation:=xlPasteSpecialOperationNone, _
                    skipblanks:=False, _
                    Transpose:=False


        With Application
            .CutCopyMode = False
            .ScreenUpdating = True
            .EnableEvents = True
        End With


End Sub

[DeanP]

Thank you very much for this.

When I run this macro get a Compile Error: Sub or Function not defined.
Debugger highlights Sub CopySheet()
I have no idea how to correct this.

Grateful for any advice

[Leith Ross]

Hello DeanP,

The only reason I can think of for this error message is the macro code is being called from a button and the macro no longer exists. Walk me through what you have done from the beginning of this project.