copy merged cells run time error

June 27, 2013 at 07:52:05
Specs: Windows 7
   With Sheets(shtName).Cells(1, colNum)
      .PasteSpecial Paste:=xlPasteValues
      .PasteSpecial Paste:=xlPasteFormats
      .PasteSpecial Paste:=xlPasteColumnWidths

I have this routine in a Sub.
I get a ' run-time error '1004'
This operation requires the merged cells to be identically sized ' on the first Pastespecial line

Sub ScoreCard_PASTE()
'
' PASTE scorecard to players sheet

' unprotect sheet
    Sheets("SCARD").Unprotect Password:="sillyboy"
        shtname = Sheets("SCARD").Range("D2")
     Sheets(shtname).Unprotect Password:="sillyboy"
    
    Sheets("SCARD").Range("A1:I31").Select
        Selection.Copy
    shtname = Sheets("SCARD").Range("D2")
    colNum = Sheets("SCARD").Range("A33")
  
   With Sheets(shtname).Cells(1, colNum)
      .PasteSpecial Paste:=xlPasteValues
      .PasteSpecial Paste:=xlPasteFormats
      .PasteSpecial Paste:=xlPasteColumnWidths

   End With
   Application.CutCopyMode = False
   
' protect sheet
    Sheets(shtname).Protect Password:="sillyboy", DrawingObjects:=True, Contents:=True, Scenarios:=True
    Sheets("SCARD").Protect Password:="sillyboy", DrawingObjects:=True, Contents:=True, Scenarios:=True

End Sub

I have two workbooks with the sub, one with the password operations, one without.
The one without works fine, so what would cause it not to work


See More: copy merged cells run time error

Report •

#1
June 27, 2013 at 10:32:28
I can't tell from your post which cells are merged and which aren't.

Sometimes you need to reference the MergeArea of the range, but since I can't see your workbook from where I'm sitting, I'm not sure where the problem is.

Try this: (untested)

 With Sheets(shtname).Cells(1, colNum).MergeArea

BTW you seem to be still Selecting objects instead of performing the operations directly.

    Sheets("SCARD").Range("A1:I31").Select
        Selection.Copy


should be:

    Sheets("SCARD").Range("A1:I31").Copy

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
June 27, 2013 at 12:53:10
BTW..this line is repeated in your code. I don't see any need for that:

shtname = Sheets("SCARD").Range("D2")

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#3
June 28, 2013 at 08:55:11
Sub ScoreCard_PASTE()
'
' PASTE scorecard to players sheet

' unprotect sheet
    Sheets("SCARD").Unprotect Password:="sillyboy"
    
    shtname = Sheets("SCARD").Range("D2")
    colNum = Sheets("SCARD").Range("A33")
    
    Sheets(shtname).Unprotect Password:="sillyboy"
    
    Sheets("SCARD").Range("D2:H2").UnMerge
        Sheets("SCARD").Range("D4:H4").UnMerge
            Sheets("SCARD").Range("D5:H5").UnMerge
    
    Sheets("SCARD").Range("A1:I31").Copy
    
    Sheets(shtname).Range("Cells(2, colNum+1):Cells(2, colNum+6)").UnMerge
        Sheets(shtname).Range("Cells(4, colNum+1):Cells(4, colNum+6)").UnMerge
            Sheets(shtname).Range("Cells(5, colNum+1):Cells(5, colNum+6)").UnMerge

  
   With Sheets(shtname).Cells(1, colNum)
      .PasteSpecial Paste:=xlPasteValues
      .PasteSpecial Paste:=xlPasteFormats
      .PasteSpecial Paste:=xlPasteColumnWidths

   End With
   Application.CutCopyMode = False
   
    Sheets(shtname).Range("Cells(2, colNum+1):Cells(2, colNum+6)").Merge
        Sheets(shtname).Range("Cells(4, colNum+1):Cells(4, colNum+6)").Merge
            Sheets(shtname).Range("Cells(5, colNum+1):Cells(5, colNum+6)").Merge
   
    Sheets("SCARD").Range("D2:H2").Merge
        Sheets("SCARD").Range("D4:H4").Merge
            Sheets("SCARD").Range("D5:H5").Merge
   
' protect sheet
    Sheets(shtname).Protect Password:="sillyboy", DrawingObjects:=True, Contents:=True, Scenarios:=True
    Sheets("SCARD").Protect Password:="sillyboy", DrawingObjects:=True, Contents:=True, Scenarios:=True

End Sub

I have sort of come up with this but no for sure that the following taken from above doesn't work and doesn't look right

Sheets(shtname).Range("Cells(2, colNum+1):Cells(2, colNum+6)").UnMerge
Sheets(shtname).Range("Cells(4, colNum+1):Cells(4, colNum+6)").UnMerge
Sheets(shtname).Range("Cells(5, colNum+1):Cells(5, colNum+6)").UnMerge

What am I missing?


Report •

Related Solutions

#4
June 28, 2013 at 11:40:22
Since I don't have a workbook set up like yours, it's kind of hard for me to actually test your code. The best I can do is point out syntax errors and code construction that I can visually determine won't work in any situation. Whether fixing the errors makes the code do what you want it to do or not is up to you to determine.

That said...

Anything that you put inside quotes is going to be considered as a text string by VBA.

Therefore "Cells(2, colNum+1):Cells(2, colNum+6)" is not going to refer to any cells, it is going to be considered as a text string. Since it's a text string VBA is actually going to be looking for a Named Range with that name.

To use the Range(Cells...) syntax, drop the quotes and replace the : with a comma:

Sheets(shtname).Range(Cells(5, colNum + 1), Cells(5, colNum + 6)).UnMerge

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#5
June 28, 2013 at 13:14:02
Thanks for coming back. I have tried your suggestion and still end up with
Run time Error '1004'
Application-defined or Object-defined error

I will have to think of another way round this, as I have one copy of the workbook that I have been using for a while, and the paste works fine
It seems the initial start up of each 'players' sheet is causing the problem when I restart from column K

If you can see why this is not working, please let me know, but again thanks for your help. Its late here so I'm gonna have a beer and chill


Report •

Ask Question