CODEWITHSUNDEEP
vbaexcel
noobie
noobie

Reputation: 15

insert copied cell values [vba]

I have the following code it copies a certain range and paste inserts it in the next worksheet, but it copies formulas, I want it to insert the amount of rows copied and paste values only.

Sub create_payroll()
'copies values from 'Driver' Worksheet (till last row) and pastes values into Invoice Data A14
Dim LastRow As Long
Application.ScreenUpdating = False
LastRow = Worksheets("Driver").Cells(Rows.Count, "B").End(xlUp).Row
Sheets("Driver").Range("A3:H" & LastRow).Copy
Sheets("Invoice Data").Range("A14").Insert xlShiftDown

Upvotes: 1

Views: 2189

Answers (2)

Tom
Tom

Reputation: 9898

Try using something like the following:

Sub create_payroll()
    'copies values from 'Driver' Worksheet (till last row) and pastes values into Invoice Data A14
    Dim LastRow As Long
    Dim srcRng As Range
    Application.ScreenUpdating = False

    With Sheets("Driver")
        LastRow = .Cells(.Rows.Count, "B").End(xlUp).Row
        Set srcRng = .Range("A3:H" & LastRow)
    End With


    With Sheets("Invoice Data")
        .Range("A14").Resize(srcRng.Rows.Count - 1, srcRng.Columns.Count).Insert shift:=xlDown
        .Range("A14").Resize(srcRng.Rows.Count, srcRng.Columns.Count).Value = srcRng.Value
    End With
End Sub

Upvotes: 1

cybernetic.nomad
cybernetic.nomad

Reputation: 6418

Or bypass the clipboard entirely:

Sheets("Invoice Data").Range("A14:H" & LastRow + 11).Value = Sheets("Driver").Range("A3:H" & LastRow).Value

To bypass the clipboard and insert rows use:

Sheets("Invoice Data").Rows("14:").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Sheets("Invoice Data").Range("A14:H" & LastRow + 11).Value = Sheets("Driver").Range("A3:H" & LastRow).Value

Upvotes: 3

Related Questions