Results 1 to 4 of 4
  1. #1
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

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

    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?
    <pre>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
    </pre>


    TIA
    Regards
    Don

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

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

    A good reason not to use merged cells... <img src=/S/evilgrin.gif border=0 alt=evilgrin width=15 height=15>

    Seriously - they're nothing but trouble.

  3. #3
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

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

    Thank you Hans; I think. <img src=/S/disappointed.gif border=0 alt=disappointed width=15 height=15>
    Regards
    Don

  4. #4
    3 Star Lounger Jim Cone's Avatar
    Join Date
    Feb 2002
    Location
    Portland, Oregon, USA
    Posts
    238
    Thanks
    0
    Thanked 3 Times in 3 Posts

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

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •