Copy Destination VBA Error

[akjohno]

I am copying the formulas from a range of cells and pasting them in the cells below for a range that is calculated from a CountA value as per the below code. The trouble is that the paste stops in random places and doesn't complete to the correct number of rows. This has had me scratching my head for over a week now but I still can't see where my mistake is. Can someone possibly enlighten me? The copy range is 22 cells that I need to paste down for example 30000 rows below, so the same formula copies down from row. My macro might cut out at row 7251 in one instance, then I run the macro again and it cuts out at 7885 or just in random spots like that, even though my CountA number is 30000. Please help? . Numshots is my CountA value....

pastearea = "G5:AF" & NumShots + 3              'Assigns the variable pastearea the value of the cells we need to paste to
    Range("G4:AF4").Copy Destination:=ActiveSheet.Range(pastearea)  'Copies the formulas from cells G4 to AF4 to the entire range needed
    Do While Workbooks(wbname).Worksheets("Sheet1").Range("AF" & NumShots + 3) = ""

 

[Michael M]

Ok, but at least we are getting the same Last orw
Have you tried clearing the clipboard after each run

Application.CutCopyMode=False

 

[akjohno]

Ok, but at least we are getting the same Last orw
Have you tried clearing the clipboard after each run

Application.CutCopyMode=False

tried that and it worked the first time doing the F8 step into, but when I run it as a macro I get the Run-time error '1004': Copy Method of Range Class Failed at the line

Range("G4:AF4").Copy Destination:=ActiveSheet.Range(pastearea)  'Copies the formulas from cells G4 to AF4

 

[MARK858]

What happens if you just define pastearea as "G5" rather than "G5:AF" & NumShots + 3

 

[akjohno]

What happens if you just define pastearea as "G5" rather than "G5:AF" & NumShots + 3

Just tried this and it stopped at Q8039 instead of G29452. As in it pasted everything correctly up to row 8038 but stopped at column Q on the 8039th row

 

[MARK858]

Just for reference if you are using Destination then you only need to know the top left cell of the destination the same as you do in a paste or pastespecial.

Just tried this and it stopped at Q8039 instead of G29452. As in it paster everything correctly up to row 8038 but stopped at column Q on the 8039th row

Then test the value of numshots as you are using Counta to get the count of the rows which only counts cells with something in them and you will probably find that the value of numshots is not what you expect/need.

 

[akjohno]

Just for reference if you are using Destination then you only need to know the top left cell of the destination the same as you do in a paste or pastespecial.


Then test the value of numshots as you are using Counta to get the count of the rows which only counts cells with something in them and you will probably find that the value of numshots is not what you expect/need.

Nope, NumShots is definitely 29449. That’s what’s got me stumped?