Pasting into Split Cells

Bench

Board Regular
Joined
Aug 27, 2009
Messages
134
I'll try to describe this as best i can, i'm sure there's a simple solution.

I'm copying say Range("A121:D121,M121:O121")

I then want to paste it into another sheet on a variable row number but i need A121:D121 to paste into A(x):D(x) which it does but then M121:O121 must paste into M(x):O(x) whereas currently it simply carries on the paste range eg. E(x):G(x)

Sure it will be some kind of PasteSpecial but struggling to find anything.

Any help would be appreciated.

NOTE: There will be around 40 ranges in the copy and paste, the destination has formulas which extract the YEAR and MONTH, just to give some context.
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

Snakehips

Well-known Member
Joined
May 17, 2009
Messages
5,301
Office Version
  1. 2013
Platform
  1. Windows
Bench,

Try something like this.....

Code:
Sub Split_Paste()
dr = 7  'destination row??????
For Each ar In Range("A121:D121,M121:O121").Areas
col = ar(1).Column
ar.Copy Sheets("Dest Sheet").Cells(dr, col)
Next ar
End Sub

Hope that helps.
 

Bench

Board Regular
Joined
Aug 27, 2009
Messages
134
Bench,

Try something like this.....

Code:
Sub Split_Paste()
dr = 7  'destination row??????
For Each ar In Range("A121:D121,M121:O121").Areas
col = ar(1).Column
ar.Copy Sheets("Dest Sheet").Cells(dr, col)
Next ar
End Sub

Hope that helps.

Thanks Snakehips ....works fine, just need it to paste values if possible?

Any ideas?

Thanks again
 

Snakehips

Well-known Member
Joined
May 17, 2009
Messages
5,301
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

Thanks Snakehips ....works fine, just need it to paste values if possible?

Any ideas?

Thanks again

??? The code will paste values.
I have assumed that, as per your example, the cells are all in the same row?
It will copy the split range from that row within the active sheet and paste into another sheet, which I have named "Dest Sheet" in my example.
dr is a variable to hold what you referred to as the 'variable row number' which determines the row to paste to in the destination sheet. I set as 7 by way of example.
You will need to include similar in your code but pick up the correct ranges and destination rows.
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
37,197
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
Thanks Snakehips ....works fine, just need it to paste values if possible?
Give this modification I did to Snakehips code a try...

Code:
Sub Split_Paste()
  dr = 7  'destination row??????
  For Each Ar In Range("A121:D121,M121:O121").Areas
    col = Ar(1).Column
    Sheets("Dest Sheet").Cells(dr, col).Resize(Ar.Rows.Count, Ar.Columns.Count) = Ar
  Next Ar
End Sub
 

Bench

Board Regular
Joined
Aug 27, 2009
Messages
134

ADVERTISEMENT

??? The code will paste values.
I have assumed that, as per your example, the cells are all in the same row?
It will copy the split range from that row within the active sheet and paste into another sheet, which I have named "Dest Sheet" in my example.
dr is a variable to hold what you referred to as the 'variable row number' which determines the row to paste to in the destination sheet. I set as 7 by way of example.
You will need to include similar in your code but pick up the correct ranges and destination rows.

Hey, yeah the DR part was straight forward, however when it pasted it retained the formulas on the new sheet rather than values as in if you did PasteSpecial > Values
 

Snakehips

Well-known Member
Joined
May 17, 2009
Messages
5,301
Office Version
  1. 2013
Platform
  1. Windows
Bench,

Sorry but your Note in post #1 refers to formulas in the destination sheet. I have been proceeding under the misaprehension that the source data was values rather than the product of formulas.

So, modify Rick's modification slightly and all should be good.


Rich (BB code):
Sub Split_Paste()
dr = 7  'destination row??????
For Each Ar In Range("A121:D121,M121:O121").Areas
col = Ar(1).Column
Sheets("Dest").Cells(dr, col).Resize(Ar.Rows.Count, Ar.Columns.Count) = Ar.Value
Next Ar
End Sub
 

Bench

Board Regular
Joined
Aug 27, 2009
Messages
134
Yeah sorry for the confusion, there are formulas in both, on the destination sheet the formulas are in Cols E:L and P:X etc etc hence why i only wanted to chop out the A:D,M:O etc. the formulas will then pull out the month and year from whats been pasted.

Thanks for your help, i'll give it a try at work in the morning, looks logical so should work,

Appreciate your assistance :)

Bench,

Sorry but your Note in post #1 refers to formulas in the destination sheet. I have been proceeding under the misaprehension that the source data was values rather than the product of formulas.

So, modify Rick's modification slightly and all should be good.


Rich (BB code):
Sub Split_Paste()
dr = 7  'destination row??????
For Each Ar In Range("A121:D121,M121:O121").Areas
col = Ar(1).Column
Sheets("Dest").Cells(dr, col).Resize(Ar.Rows.Count, Ar.Columns.Count) = Ar.Value
Next Ar
End Sub
 

Forum statistics

Threads
1,137,298
Messages
5,680,689
Members
419,927
Latest member
Axtros

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