VBA help to add rows only to certain Columns

Viewed 40

I currently have this code that works to insert full rows. However, I was hoping to limit the inserting of the row to columns A:J. Bonus points to see who can help me figure out that if I delete column C why my current code doesn't work?

I tried to add .resize(1,10) but for some reason I keep getting errors, maybe the location in which I try to add this function. Any guidance/help is appreciated as always!

Here is my current code: VBA Code:

Sub Add_Job()
    
    Dim act As Worksheet
    Set act = ThisWorkbook.ActiveSheet
    bot_row = act.Range("Z1")
    
    act.Rows(bot_row & ":" & bot_row + (5)).Insert Shift:=x1ShiftDown
    act.Range("A3:J8").Copy
    act.Range("A" & bot_row & ":J" & bot_row + (5)).PasteSpecial xlPasteFormats
    act.Range("A" & bot_row & ":J" & bot_row + (5)).PasteSpecial xlPasteFormulas
    Range("B" & bot_row & ":B" & bot_row + (5)).ClearContents
    
    Application.CutCopyMode = False
    
End Sub

Edit: Updated Code that works now. However, I still don't understand if I delete column C, why it throws an error?

    Dim act As Worksheet
    Set act = ThisWorkbook.ActiveSheet
    bot_row = act.Range("Z1")
    
    act.Range("A" & bot_row & ":J" & bot_row + (5)).Insert Shift:=xlShiftDown
    act.Range("A3:J8").Copy
    act.Range("A" & bot_row & ":J" & bot_row + (5)).PasteSpecial xlPasteFormats
    act.Range("A" & bot_row & ":J" & bot_row + (5)).PasteSpecial xlPasteFormulas
    Range("B" & bot_row & ":B" & bot_row + (5)).ClearContents
    
    Application.CutCopyMode = False
0 Answers
Related