Macro to copy Data, delete old data that is used in new and paste new without lossing any values?

tonywatsonhelp

Well-known Member
Joined
Feb 24, 2014
Messages
3,194
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
Hi Everyone,

Im wondering if its possible to copy a range, then delete data that feeds that range and still paste the data I copied.

if it worked heres what I would like to do:

Code:
Sub copytest1()
Sheets("Orders1").Range("AI24:AR38").SpecialCells(xlCellTypeVisible).Copy
Sheets("Invoice").Range("A2:N33").ClearContents
Sheets("Sheet3").Range("A4").PasteSpecial xlPasteValues



End Sub
Orders1 is formulas linked to Invoice, so when I clear it I get an error trying to paste. please help
Thanks

Thanks
Tony
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
Why don't you pastevalues then clearcontents ... by reversing the sequence you should be ok
 
Upvote 0
Because The macro is just an example to show what it needs to do, In reality I will be using it in a much more complex situation where retaining the data after it has been copied is not possible.
However, if there's not a better option I suppose I could paste values into a temp. holding sheet and copy again once everything has been done, just seamed a bit longwinded a thought excel might be able to do it better.
Thanks
Tony
 
Upvote 0
Sorry .... I thought you might have been having a case of the Monday mornings :)

Would you be able to switch off calculations ? Application.Calculation=xlCalculationManual while you are doing the steps between the copy & paste then restore it with Application.Calculation = xlCalculationAutomatic
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,251
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top