CODEWITHSUNDEEP
excelvbacopy-pastepastecut
Joel A. Seely
Joel A. Seely

Reputation: 101

Cutting a region on one worksheet and pasting to another doesn't work. But copy does. Why?

I have been struggling with this problem for awhile now.

I've written a VBA routine that is supposed to find a region on one worksheet, then cut and paste it to another worksheet. If I run the following code it doesn't work:

   ' This does not work
   DSheet.Range(SummaryDataAddr, DSheet.Cells.SpecialCells(xlLastCell)).Cut
   PSheet.Range(SummaryDataLocation).PasteSpecial Paste:=xlPasteValues

But if I change the code to copy the region from the first worksheet, paste it to the second worksheet, then go back to the first worksheet and explicitly delete the region, it works. 

   ' But this does work.  Why?
   DSheet.Range(SummaryDataAddr, DSheet.Cells.SpecialCells(xlLastCell)).Copy   
   PSheet.Range(SummaryDataLocation).PasteSpecial Paste:=xlPasteValues
   DSheet.Range(SummaryDataAddr, DSheet.Cells.SpecialCells(xlLastCell)).Delete

Has anyone seen this kind of problem before? Is there a way to use the cut function and get it all to work?

To access a the spreadsheet with the VBA code in it go here: https://www.dropbox.com/s/satv6z95tlqw7lr/CutBug.xlsm?dl=0

The routine is called "CreateDLDataPivot" (it's a pared down version of a larger program I'm working on).

Thanks for any help!

Upvotes: 0

Views: 36

Answers (1)

user11060139
user11060139

Reputation:

PasteSpecial Paste:=xlPasteValues does not work with Range.Cut; it only works with Range.Copy.

Range.Cut only clears the cells, it does not delete the cells.

Try a direct value transfer and clear.

with DSheet.Range(SummaryDataAddr, DSheet.Cells.SpecialCells(xlLastCell))
  PSheet.Range(SummaryDataLocation).resize(.rows.count, .columns.count) = .value
  .clear   'use .clearcontents to retain formatting
end with

Upvotes: 2

Related Questions