CODEWITHSUNDEEP
excelvba
Knargle
Knargle

Reputation: 7

Excel VBA Insert copied cells and shift cells down ERROR

Complete amateur here. I've been puzzling over this for hours, and I can't find anything to help me in any other thread. At my wit's end so sorry if this has been asked elsewhere.

I'm trying to create a ridiculously simple macro to do the following:

Go to Sheet2,
Select C6:C10
Copy
Go to Sheet3
Insert copied cells in B2 and shift the other cells down.

I did this just by recording the macro, but each time I do it, I get different errors. The error I currently have is 'Insert Method of Range Class Failed', but sometimes the error pops up at 'Selection.Copy'. This is the code I have:

Sub InsertCellsShitDown()
'
' InsertCellsShitDown Macro
'

'
Sheets("Booking Sheet").Select
Range("C6:C10").Select
Selection.Copy
Sheets("Sheet1").Select
Range("B2").Select
Selection.Insert Shift:=xlDown
End Sub

Any help would be hugely appreciated.

Upvotes: 0

Views: 5313

Answers (1)

BruceWayne
BruceWayne

Reputation: 23285

Sub InsertCellsShiftDown()
'
' InsertCellsShitDown Macro
Dim bookingWS As Worksheet, mainWS As Worksheet
Dim copyRng As Range

Set bookingWS = Sheets("Booking Sheet")
Set mainWS = Sheets("Sheet1")
Set copyRng = bookingWS.Range("C6:C10")

mainWS.Range("B2:B" & copyRng.Rows.Count + 1).Insert Shift:=xlDown
copyRng.Copy mainWS.Range("B2")
End Sub

How does this work? I assume you wanted to insert 5 rows, so from B2:B7, then put the data.

Upvotes: 2

Related Questions