PDA

View Full Version : VBA COPY PASTE DYNAMIC RANGE EXCEPT HEADERS



DeanP
11-25-2018, 02:42 PM
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
11-25-2018, 06:45 PM
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
11-26-2018, 03:22 AM
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
11-26-2018, 11:06 AM
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.