Excel macro insert values instead of formulas

Question

Writing macros is like lego for me, a lot of trial and error (learning and newby)

Have a problem and hope someone can help me out.

So i have this working macro:

Sub kopieer()

On Error GoTo Err_Execute

Sheet130.Range("A4:BV101").Copy
Sheet4.Range("A2").Rows("1:1").Insert xlShiftDown

Err_Execute:

If Err.Number = 0 Then

    MsgBox "All have been copied!"

ElseIf Err.Number <> 0 Then

    MsgBox Err.Description

End If

End Sub

This macro copies something from sheet to sheet and inserts the rows. This is perfect. Now i want this macro to only copy the cells values instead of the complete cell contents (dont want the formulas)

I tried to include a PasteSpecial xlPasteValues but it looks combining this with Insert xlShiftDown makes it difficult.

Hope someone can help out :)

Thanks!

Answer 1

I think this is what you want to do: Shift as many cells down in Sheet4 as there are cells in the Sheet130 in the range A4:BV101 and after shifting you want to copy the same cells in the Sheet4. I am not sure you will be able to combine xlShiftDown with PasteSpecial (see allowed xlPasteType) but you can break the problem in two steps. First Shift the cells and then paste the values only. However if your cell formulas involve calls to random and related functions, this might not work.

Regardless, please make a copy of your sheet before you try this.

Sub kopieer()

    On Error GoTo Err_Execute

    Sheet130.Range("A4:BV101").Copy
    Sheet4.Range("A2").Rows("1:1").Insert xlShiftDown
    Sheet4.Range("A2").Rows("1:1").PasteSpecial xlPasteValues
    
Err_Execute:
    
    If Err.Number = 0 Then
    
        MsgBox "All have been copied!"
    
    ElseIf Err.Number <> 0 Then
    
        MsgBox Err.Description
    
    End If

End Sub

Answer 2

Insert Values with Error Handling

Option Explicit

Sub kopieer()

    On Error GoTo Err_Execute

    Dim rng As Range: Set rng = Sheet130.Range("A4:BV101")
    
    With Sheet4.Range("A2").Resize(rng.Rows.Count)
        .EntireRow.Insert xlShiftDown
        .Offset(-rng.Rows.Count).Resize(, rng.Columns.Count).Value = rng.Value
    End With
    
    MsgBox "All have been copied!"
    
ProcExit:
    Exit Sub

Err_Execute:
    MsgBox Err.Description
    Resume ProcExit

End Sub