Copying Merged & Unmerged Cells

[ads_3131]

Hello

Need help!

Below i have some code which copys a range of cells from on sheet to another..... My problem is some of the cells are merged and some aint...

Is there any way i can get the code below to work how i want it to?

-------------------------------------------------------------------------------


sub_example()

For Each cell In Selection

Range("F194: L197 , M195: N196 , P194: S197 , T194: T196 ,").Copy Worksheets("Sheet2").Range("D" & Rows.Count).End(xlUp).Offset(1)

' Next cell

end sub
---------------------------------------------------------------------

Any help would be great ..........

[Bob Phillips]

I understand your problem, but I am confused at what you are trying to do. Why the loop, and where does the data end up when you have multiple areas?

[ads_3131]

Basically... im wanting to copy some cells from one sheet to another, some cells are merged while others arnt .... not all cells which i want to copy go in order....

The example of code is a macro which is run on a button... and copys the range F194 to L197 then M195 to N196 then P194 to S197 then T194 to T196 , and places it into sheet two....

my problem is this isnt working due to multiple random ranges :/

but if i did the code below it works:

-------------------------------------------------------------------------------


sub_example()

For Each cell In Selection

Range("F194:T194").Copy Worksheets("Sheet2").Range("D" & Rows.Count).End(xlUp).Offset(1)

' Next cell

end sub
---------------------------------------------------------------------

How can i get it to work with different ranges in place?

tar

[Bob Phillips]

I think it is the areas, not the merged cells

[vba]

Sub example()
Dim rngArea As Range
Dim cell As Range

For Each rngArea In Range("F194:L197, M195:N196, P194:S197, T194:T196").Areas

For Each cell In rngArea

cell.Copy Worksheets("Sheet2").Range("D" & Rows.Count).End(xlUp).Offset(1)
Next cell
Next rngArea
End Sub[/vba]

[ads_3131]

Thanks XLD , that works just how i want it apart from.... its putting it into sheet two down column D...?

How can i select The cell and row i want the data to be pasted along.....E.G pasting in D1 right along E1,F1,G1 and so on

tar again

[Bob Phillips]

Do you mean like this?

[vba]

Sub example()
Dim rngArea As Range
Dim cell As Range
Dim NextRow As Long

NextRow = 1
For Each rngArea In Range("F194:L197, M195:N196, P194:S197, T194:T196").Areas

rngArea.Copy Worksheets("Sheet2").Cells(NextRow, "D")
NextRow = NextRow + rngArea.Rows.Count
Next rngArea
End Sub[/vba]

[ads_3131]

Yes and no.... that bit of code starts a new line per each set of ranges... I just want the copy from sheet one to be placed at a place of my choice in sheet two going along a line and not down a column.... if thats possible :S

[Bob Phillips]

Okay, is this it?

[vba]

Sub example()
Dim rngArea As Range
Dim cell As Range
Dim Nextcol As Long

Nextcol = 4
For Each rngArea In Range("F194:L197, M195:N196, P194:S197, T194:T196").Areas

For Each cell In rngArea

cell.Copy Worksheets("Sheet2").Cells(1, Nextcol)
Nextcol = Nextcol + 1
Next cell
Next rngArea
End Sub
[/vba]