1

I'm trying to clean up my code and remove all the activates and selects that are in there from my novice use of the macro recorder. Just when I think I understand something, apparently I don't.

This bit of code in my macro works perfectly:

Worksheets(DEName).Activate
Range("A13").Select
Range(Selection, Selection.SpecialCells(xlLastCell)).Copy
Worksheets(CrunchName).Activate
Range("A1").PasteSpecial (xlPasteValues)

So, I thought I'd be clever and tighten it up. But, my attempt at doing so generates a 1004 error on the copy line:

Worksheets(DEName).Range("A13", Range("A13").SpecialCells(xlLastCell)).Copy
Worksheets(CrunchName).Range("A1").PasteSpecial (xlPasteValues)

Any help and/or explanation of what I'm doing wrong would be greatly appreciated.

braX
  • 11,506
  • 5
  • 20
  • 33
Dave
  • 63
  • 1
  • 5

3 Answers3

0

Include the Worksheet reference inside (1 here is DEName and 2 is CrunchName) as well as outside when using the range

Worksheets(1).Range("A13", Worksheets(1).Range("A13").SpecialCells(xlLastCell)).Copy
Worksheets(2).Range("A1").PasteSpecial (xlPasteValues)
QHarr
  • 83,427
  • 12
  • 54
  • 101
0

Or Set only one range("A13")

Worksheets(DEName).Range("A13").SpecialCells(xlLastCell)).Copy
Worksheets(CrunchName).Range("A1").PasteSpecial (xlPasteValues)

see more examples here https://stackoverflow.com/a/34886033/4539709

How to avoid using Select in Excel VBA

0m3r
  • 12,286
  • 15
  • 35
  • 71
0

Why have you got Range("A13") twice?

Worksheets(DEName).Range("A13").SpecialCells(xlLastCell)).Copy

See if that works.

Christian T
  • 128
  • 1
  • 12
  • Thanks for trying to help, Christian. This suggestion did not copy anything. To clarify (after the fact, since QHarr's suggestion did work), I was trying to copy everything in the range A13 to the last used cell, which would be something like J50000, but changes every time the macro is run. So, A13 was in there twice, once to define the beginning of the range to copy, and then again as a reference point for the lastcell statement to figure out where to go. – Dave Mar 23 '18 at 23:55