CODEWITHSUNDEEP
excelvbainsertrange
Jared K
Jared K

Reputation: 11

VBA Insert row instead of copy

I'm trying to run before I can crawl. I have pieced together this code, but I need it to Insert at row 24, not copy.

Dim sh4 As Worksheet, sh5 As Worksheet, lr As Long, rng As Range
Set sh4 = Sheets("est")
Set sh5 = Sheets("gaf letter")
lr = sh4.Cells(Rows.Count, 1).End(xlUp).Row
Set rng = sh4.Range("a1:a" & lr)
rng.EntireRow.Copy sh5.Rows("24:24")

I've attempted using .Insert, but it comes up with Method Insert of object Range Failed. The code works fine if I wanted to just copy, but I need it to insert and shift the remaining rows below it, down.

Upvotes: 1

Views: 329

Answers (2)

user3598756
user3598756

Reputation: 29421

just go

With Sheets("est")
    .Range("A1", .Cells(.rows.Count, 1).End(xlUp)).EntireRow.Copy
    Sheets("gaf letter").rows(24).Insert shift:=xlDown
    Application.CutCopyMode = False
End With

Upvotes: 0

CDP1802
CDP1802

Reputation: 16174

Option Explicit ' declare all variables

Sub InsertRows()

    Dim sh4 As Worksheet, sh5 As Worksheet
    Dim lr As Long, rng As Range
    Set sh4 = Sheets("est")
    Set sh5 = Sheets("gaf letter")
    
    Application.ScreenUpdating = False
    With sh4
        lr = .Cells(.Rows.Count, 1).End(xlUp).Row
        Set rng = .Rows("1:" & lr)
        rng.Copy
        sh5.Rows(24).Insert shift:=xlDown
    End With
    Application.ScreenUpdating = True
    Application.CutCopyMode = False

End Sub

Upvotes: 1

Related Questions