PasteSpecial with merged cells (VBA/Excel/2000/SP3)

Topic

New Reply

Hello All
I need to provide a workbook to a third party without any of the formulae, and as little embedded code as possible. My planned approach is to open a workbook containing the necessary VBA code and an identical complement of worksheeets to the workbook containing the data of interest. After this it will only be necessary to copy each sheet and paste it into the target sheet (PasteSpecial xlPasteValues). However when I run the following code, I get an error at the paste special line. I suspect this is caused by merged cells, though the same pattern of merged and un-merged cells exists in both workbooks.
I resist the temptation to map out the cells and paste the data on a cell by cell basis, as I believe this will be a slow process.
Does anyone have an elegant approach to resolving this problem?

Public Sub Replace_Formulae()
Dim mySheet As Worksheet
Dim oWkbk As Workbook
    Set oWkbk = ThisWorkbook
    With Workbooks(strReport)
        For Each mySheet In .Sheets
            If mySheet.Name  "Transaction Sheet" Then
                With .Worksheets(mySheet.Name)
                    .Activate
                    .Unprotect pwd
                    Cells.ClearContents
                    oWkbk.Sheets(mySheet.Name).Cells.Copy
                    Cells.PasteSpecial xlPasteValues
                    .Protect pwd
                End With
            End If
        Next
    End With
End Sub

TIA

Reply | Quote

Replies

A good reason not to use merged cells…

Seriously – they’re nothing but trouble.

Reply | Quote

Thank you Hans; I think.

Reply | Quote

Don,

Hans gave very good advice about merged cells.
They are nothing but trouble.
However, I did revise your code and it may be worth a try…
(note: strReport was nowhere to be found?)
‘——————————-
Public Sub Replace_Formulae()
Dim mySheet As Excel.Worksheet
Dim oWkbk As Excel.Workbook
Dim objCell As Excel.Range

Set oWkbk = ThisWorkbook
For Each mySheet In Workbooks(strReport).Worksheets
If mySheet.Name “Transaction Sheet” Then
With mySheet
.Unprotect “pwd”
.UsedRange.ClearContents
‘a used range does not have to start in Range(“A1”)
Set objCell = oWkbk.Sheets(mySheet.Name).UsedRange.Cells(1, 1)
oWkbk.Sheets(mySheet.Name).UsedRange.Copy
.Range(objCell.Address).PasteSpecial xlPasteValues
.Protect “pwd”
End With
End If
Next
Set objCell = Nothing
Set mySheet = Nothing
Set oWkbk = Nothing
End Sub
‘———————-

Regards,
Jim Cone
San Francisco, USA

Reply | Quote