Multiple Cells Cut and Paste

[helpexcel]

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.

 

[Rick Rothstein]

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.

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]

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]

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.

 

[helpexcel]

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]

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...

[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]

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]

replacing all those lines of code with this simple one makes a hug difference in the speed its calculated. THANKS!!!

 

[Rick Rothstein]

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]

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