Problem with pasting special merged cells to merged cells

[ritpg]

I see several posts on this topic but none recently and I have yet to
find a solution. I am pasting special from a cell that consists to
two merged cells to another cell in the same row that also consists of
two merged cells. The columns are labeled Previous Month's Total and
Current Month's Total so you can see what I'm trying to do.
Interestingly, the problem seems to occur only when I'm doing a paste
special Values. I have to do Values because the cell in the Current
Month's Total contains an equation which I do not want to move to the
cell in the Previous Month's Total column - I just want the value.
Dr. Excel, are you still out there? Anbody else have a clue? I'm
running MS Excel 2003 under XP.
Thank you very much!

 

[Project Mangler]

ritpg,

using excel 2003:

Sub Toggle()
Dim ma As Object
Dim mb As Object
'B4 & B6 are first cells in respective merge areas
Set ma = Range("B4").MergeArea
Set mb = Range("B6").MergeArea
mb.Cells(1, 1).Value = ma.Cells(1, 1).Value
End Sub

HTH

 

[ritpg]

To give you more complete information, I am trying to copy E7:E46 to C7:C46.
And cells C7 & C8 are merged as are C9 & C10, etc. The column E cells are
identically merged. I hope this helps.

 

[Project Mangler]

ritpg,

This is rather clumsy code, but then I'm just a beginner.

BTW I don't seem to be getting all your posts.

Click on Tools/Macro/Visual Basic Editor
You should see comething like VBAProject(yourSpreadsheet.xls)
Highlight that line
From the menu select "Insert" then click on "module"
If you have no other code in that project you should see "Module1" inserted
The cursor should be flashing in a window to the right

Copy the text below the ------- (from Option Explicit to End Sub) and paste
it in that window
Select Tools/Macro/Macros
"procPaste" should be highlighted (if its the only macro in your project)
Click the options button at the bottom right of the form
In the box beside "Ctrl+" enter a letter (e.g. "i")
Click OK, close the form
Save the spreadsheet
Typing Ctrl i will run the macro and should paste E7:E46 to C7:C46 (it does
here)
Good luck & I hope this helps
Note that the macro assumes that Ctrl i is being clicked on the activesheet
containing the merged ranges; it won't work if this is not the case.


-----------------------------------------------------------
Option Explicit
'Copy merged cells

Sub procPaste()
Dim ma As Object
Dim mb As Object
Dim R As Range
Dim rngE As Range
Dim i As Integer

Set rngE = Range("$E$7")
For i = 9 To 45 Step 2
Set rngE = Application.Union(rngE, ActiveSheet.Cells(i, 5))
Next i

For Each R In rngE
Set ma = Range(R.Address).MergeArea
Set mb = Range(R.Address).Offset(0, -2).MergeArea
mb.Cells(1, 1).Value = ma.Cells(1, 1).Value
Next

End Sub