Solved: 2000 compatiblity: Pasting to merged cell using macro

[Orange_Wed]

Hello Folks,

I am having an issue with pasting data from another workbook into a workbook with merged cells. The code I've thrown together works fine in Excel 2003 & 2007, but two of the systems in our office are still running 2000 builds.

The merged cells are necessary for esthetic reasons, as well as the simple fact that they keep the folks who don't quite know a mouse from an optical drive from asking me why they can't enter data directly into my protected headings and labels.

Is there any way of pasting data into merged cells using a macro in 2000 that anyone could fill me in on? It would be nice if it were forward-compatable with 2003 and 2007, as this sheet is to be on a shared drive and accessable by all the office staff.

Here is a sample of the code in the sheet containing the data to be copied:

[VBA]Sub SwitchToReport() 'pasted into ThisWorkbook in file Employee Records.xls
ActiveCell.Copy
Workbooks("copy of combined.xls").Activate
ActiveCell.PasteSpecial xlPasteValues 'Gives a generic "400" error here
Workbooks("Employee Record.xls").Close SaveChanges:=False
End Sub[/VBA]

For testing purposes, I'll include a copy of the right-click menu modification I took directly from the Excel help files and modified for my purposes:

[VBA]Private Sub Worksheet_BeforeRightClick(ByVal target As Range, _
Cancel As Boolean)
For Each icbc In Application.CommandBars("cell").Controls
If icbc.Tag = "brccm" Then icbc.Delete
Next icbc
If Not Application.Intersect(target, Me.Range("B:B")) Is Nothing Then
With Application.CommandBars("cell").Controls _
.Add(Type:=msoControlButton, before:=6, _
temporary:=True)
.Caption = "<---Insert Name--->"
.OnAction = "Thisworkbook.SwitchToReport"
.Tag = "brccm"
End With
End If
End Sub[/VBA]

I've stripped this aspect of the project down to the bare minimum of code and even combined the sheets of two workbooks to see if it was corruption in the original file. Narrowed it down to the fact that one sheet has merged cells, and this is where the code fails. I have tried the ActiveSheet.Paste method without sucess, as well.

I've attached a stripped down version of the main sheets for your perusal.

If anyone could give me some pointers, it would be most appreciated. These sheets have been adapted from a paper-only format, and I want to stick as close to the original style as is possible to reduce the learning curve.

Thanks in advance,

Wednesday

[rbrhodes]

Hi OW,

This seems to work on the merged cells...

[VBA]
Sub SwitchToReport() 'pasted into ThisWorkbook in file Employee Records.xls
ActiveCell.Copy
Workbooks("copy_of_combined.xls").Activate
'ActiveCell.PasteSpecial xlPasteValues 'Gives a generic "400" error here

'Seems to work
ActiveSheet.Paste

Workbooks("Record.xls").Close SaveChanges:=False
End Sub
[/VBA]

[Orange_Wed]

Well, upon further brainstorming (and another cup of coffee) I discovered that I have been suffering from a rare strain of cerebral flatulance for the last three days.

[VBA]
Dim temp as string
[/VBA]

Brain, why dost thou abandon me?

I am still wondering if it's possible to use a paste command to move data into a merged cell in Excel 2000, but the urgency of the request has been downgraded to a nice, round nil.

Have a good day all,

Wednesday