Multiple Cells Cut and Paste

helpexcel

Well-known Member
Joined
Oct 21, 2009
Messages
616
Hi,

Is there a way to code for cutting and pasting multiple cells that are not joined. So cutting E8, F3, G13 in sheet 1 and D1, H5 in sheet 2 and pasting to A3, B4, C7, E1, J10 in sheet 3?
I have coded to do it individually, but i'm getting inconsistent results. I don't know if going back and forth between the sheets is the reason for that, so i'm looking for an alternative.

Thanks.
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
37,553
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
Is there a way to code for cutting and pasting multiple cells that are not joined. So cutting E8, F3, G13 in sheet 1 and D1, H5 in sheet 2 and pasting to A3, B4, C7, E1, J10 in sheet 3?
I have coded to do it individually, but i'm getting inconsistent results. I don't know if going back and forth between the sheets is the reason for that, so i'm looking for an alternative.
The above statement makes it sound like you are doing a lot of selecting of sheets and ranges on those sheets in order to move data from sheet to sheet. You do not have to select anything to do this... all you have to do is reference the sheets directly. Cutting the above ranges to their new location can be done with 5 lines of code.
Code:
Sheets("Sheet1").Range("E8").Cut Sheets("Sheet3").Range("A3")
Sheets("Sheet1").Range("F3").Cut Sheets("Sheet3").Range("B4")
Sheets("Sheet1").Range("G13").Cut Sheets("Sheet3").Range("C7")
Sheets("Sheet2").Range("D1").Cut Sheets("Sheet3").Range("E1")
Sheets("Sheet2").Range("H5").Cut Sheets("Sheet3").Range("J10")
 

helpexcel

Well-known Member
Joined
Oct 21, 2009
Messages
616
Wow, so i currently have 5 lines of code for each, selecting, copying, pasting...what would i add to the above if i wanted to paste values?
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
37,553
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
Wow, so i currently have 5 lines of code for each, selecting, copying, pasting...what would i add to the above if i wanted to paste values?
You mean you would want to leave the original values in place and just copy them instead of cutting them (as your original post said)? If so, simply change the word Cut in each line of code to the word Copy.
 
Last edited:

helpexcel

Well-known Member
Joined
Oct 21, 2009
Messages
616

ADVERTISEMENT

There is currently a formula in the cells I'm Cutting/Copying. I only want the value copied. I added the code below, but imagine there is a simpler version.



Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
37,553
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
There is currently a formula in the cells I'm Cutting/Copying. I only want the value copied.
In that case, all you need to do is assign the values directly. Use these 5 lines of code instead of the ones I gave you earlier...
Code:
[table="width: 500"]
[tr]
	[td]Sheets("Sheet3").Range("A3").Value = Sheets("Sheet1").Range("E8").Value
Sheets("Sheet3").Range("B4").Value = Sheets("Sheet1").Range("F3").Value
Sheets("Sheet3").Range("C7").Value = Sheets("Sheet1").Range("G13").Value
Sheets("Sheet3").Range("E1").Value = Sheets("Sheet2").Range("D1").Value
Sheets("Sheet3").Range("J10").Value = Sheets("Sheet2").Range("H5").Value[/td]
[/tr]
[/table]
 

helpexcel

Well-known Member
Joined
Oct 21, 2009
Messages
616

ADVERTISEMENT

thanks!! one more quirk, if the sheets are in different workbooks I'm assuming i would change to:

Workbooks("One1").Sheets("Sheet3").Range("A3").Value = ThisWorkbook.Sheets("Sheet1").Range("E8").Value
 

helpexcel

Well-known Member
Joined
Oct 21, 2009
Messages
616
replacing all those lines of code with this simple one makes a hug difference in the speed its calculated. THANKS!!!
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
37,553
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
thanks!! one more quirk, if the sheets are in different workbooks I'm assuming i would change to:

Workbooks("One1").Sheets("Sheet3").Range("A3").Value = ThisWorkbook.Sheets("Sheet1").Range("E8").Value
Yes, but you do not need the ThisWorkbook reference as that is the default (doesn't hurt to have it in there if you want, but it is not required).
 

helpexcel

Well-known Member
Joined
Oct 21, 2009
Messages
616
Is there a way to sum then copy? I tried this but it didn't work

Workbooks("One1").Sheets("Sheet3").Range("A3").Value = ThisWorkbook.Sheets("Sheet1").Sum(Range("E8"),Range("E6")).Value
 

Forum statistics

Threads
1,148,143
Messages
5,745,043
Members
423,917
Latest member
Frank1931

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
Top