0

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.

BruceWayne
  • 22,923
  • 15
  • 65
  • 110
Knargle
  • 7
  • 2
  • 3
  • The errors are likely due to using the Select method. [It is advised to avoid using that statement](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros) – davidmneedham Jul 25 '17 at 20:11
  • Please remove the MACROS tag - it should not be used for VBA / MS-Office languages. – Bill Roberts Jul 25 '17 at 20:14
  • Maybe because the sub is called `ShitDown` :] – Slai Jul 25 '17 at 20:17

1 Answers1

2
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.

BruceWayne
  • 22,923
  • 15
  • 65
  • 110
  • This is completely bug-free (MASSIVE development!) but it shifts all rows down, which I don't want it to do. The only cells I need to shift downwards are the rows directly below the cells I'm inserting. Does that make sense? I assume 'EntireRow' is the problem here. – Knargle Jul 25 '17 at 21:23
  • @Knargle - Yah, check the edit (I just removed the `EntireRow` part) – BruceWayne Jul 25 '17 at 21:26
  • Bingo. Thank you so much! I need to spend some time seriously learning this. – Knargle Jul 25 '17 at 21:34
  • @Knargle - Happy to help! I suggest starting with [How to avoid using `.Select`/`.Activate`](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros) too. – BruceWayne Jul 25 '17 at 21:35
  • Apologies for posting again, but I have another query that is very similar to my earlier one... I want to do exactly the same thing but with a different starting range. Instead of C6:C10, I want to select all rows in column B below B2, UNTIL the first blank cell. Is that possible? – Knargle Jul 25 '17 at 23:45
  • @Knargle - Try `Set copyRng = bookingWS.Range("B2",Range("B2").End(xlDown))` – BruceWayne Jul 26 '17 at 01:47
  • That brings up 'Method 'Range of Object'_Worksheet failed' error :/ the bug appears on the set copyRng line. – Knargle Jul 26 '17 at 08:58
  • @Knargle oh sorry add `bookingWS.` before the second `Range()` – BruceWayne Jul 26 '17 at 13:45