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

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
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.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
??? 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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,144
Members
448,552
Latest member
WORKINGWITHNOLEADER

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