-1

I'm trying to write a basic code where I'm copying a variable amount of cells from the previous sheet and INSERTING those copied cells in a new sheet, where it pushes the data I've already put in the new sheet down.

This is a weekly task where I open a new sheet, paste some new information in it, then run the code to copy the previous weeks sheet and insert the copied cells into the new sheet. 

`ActiveSheet.Previous.Select
Range("A2:K2").Select
Range(Selection, Selection.End(xlDown)).Copy
ActiveSheet.Next.Select
ActiveSheet.Cells(2, 1).Select
Selection.Insert Shift:=xlDown`

For some reason, the Selection.Insert Shift:=xlDown command crashes my excel. I've read a lot about how to solve this problem but most solutions revolve around running the code in the same sheet. But since it's a new sheet every week, I can't set it to a certain relevant worksheet. ie i'm trying to avoid using Dim, Set and With to define a sheet.

Nico D
  • 1
  • 1
  • 6
    You really want to [avoid using activate and select](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) in your VBA – cybernetic.nomad Mar 06 '19 at 20:17
  • 2
    @cybernetic.nomad "i'm trying to avoid using Dim, Set and With to define a sheet" - sounds like OP is already aware of the cure, just refusing treatment. – Mathieu Guindon Mar 06 '19 at 20:35
  • @MathieuGuindon lol i know but could that really be why it's crashing 6 lines of code in? The entire macro is only 14 lines. That being said I started teaching myself vba this year so feel free to educate me. – Nico D Mar 06 '19 at 21:21
  • @cybernetic.nomad ok and in your opinion there's no way around that? You think that's certainly why the code is crashing? – Nico D Mar 06 '19 at 21:33
  • 1
    Plenty of reasons for it to fail. Does the source and/or destination sheet contain a `ListObject`, i.e. an actual *table*? If so, you don't need to work out the last row, and you might not be able to insert/shift cells either. That would be *one* reason for it to throw. Again, without knowing anything about your data, there's just too much to assume. – Mathieu Guindon Mar 06 '19 at 21:53

1 Answers1

2

There are several reasons why Range.Insert might fail, depending on what's on that sheet... and it's not possible to tell why just by looking at that snippet.

There is no reason whatsoever to want to avoid declaring and using variables though. Assuming the sheet that's active when the macro starts (assuming Sub ... isn't too far above the beginning of that snippet) is the destination sheet...

Dim destSheet As Worksheet
Set destSheet = ActiveSheet
Debug.Assert destSheet.Index > 1 ' if code breaks here, assumption was wrong

Dim sourceSheet As Worksheet
Set sourceSheet = destSheet.Previous

Dim sourceStart As Range
Set sourceStart = sourceSheet.Range("A2:K2")

sourceSheet.Range(sourceStart, sourceStart.End(xlDown)).Copy
destSheet.Range("A2:K2").Insert Shift:=xlDown

If the above code fails similarly, then I'd suggest attempting to do the exact same thing manually and see if Excel gives you a more detailed error message, as it often does when all its API tells VBA is "1004, application-defined error".

Note that Range.End(xlDown) has a non-zero chance of not landing you where you think it does. Finding the last row is usually more reliable by going at it from the bottom of the sheet with Range.End(xlUp):

sourceSheet.Range(sourceStart, sourceSheet.Range("A" & Rows.Count & ":K" & Rows.Count).End(xlUp)).Copy

If the destination sheet contains a ListObject, make sure you're not inserting more columns than the table contains - if doing this manually, you get this message:

enter image description here

If doing this programmatically, this is all you get:

error 1004

Mathieu Guindon
  • 69,817
  • 8
  • 107
  • 235
  • 2
    Don't you think that this line `sourceSheet.Range(sourceStart, sourceStart.End(xlDown)).Copy` is why it's crashing? Depending on the values in the range, it can easily go to row 1048576. – Alex de Jong Mar 06 '19 at 21:44
  • @AlexdeJong could very well be - typically, finding the last row is much more reliable with e.g. `sourceSheet.Range("A" & Rows.Count).End(xlUp)`; given how much information is given in the OP, it's hard to tell if it makes a difference - hence the suggestion to try to reproduce the error manually. – Mathieu Guindon Mar 06 '19 at 21:47