Copy/Paste merged cell using command button

[sean614]

I'm very new to VBA. I recorded a simple macro to copy information from a merged cell on one sheet and special paste it to a cell on a different sheet. The macro works fine, but when I put the code into a command button it gives me this error message: 'Run-time error '1004': Select method of range class failed'.

Here is the code:

Private Sub CommandButton2_Click()

Sheets("Form").Select
Range("D91:E91").Select
Selection.Copy
Sheets("NMAD _Setup_Info").Select
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

End Sub

 

[PHV]

You may try this:
Private Sub CommandButton2_Click()
ActiveWorkbook.Sheets("Form").Range("D91:E91").Copy
ActiveWorkbook.Sheets("NMAD _Setup_Info").Range("A1").PasteSpecial _
Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
End Sub

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Bong]

You should avoid using select in favor of directly referencing the range you want to deal with.

dim r1,r2 as range
set r1=sheets("Form").range("d91:e91")
set r2=sheets("NMAD _Setup_Info").range("a1")
    [s]Sheets("Form").Select
    Range("D91:E91").Select[/s]
    [red]r1[/red].Copy
    [s]Sheets("NMAD _Setup_Info").Select
    Range("A1").Select[/s]
    [red]r2[/red].PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

_________________
Bob Rashkin

 

[PHV]

Bob, why using a variant (r1) and a range (r2) ?

 

[xlbo]

the (probable) reason for this is that the commandbutton has a property called "TakeFocusOnClick" which removes the focus from the worksheet. Hence, when you then "SELECT" the worksheet in code, it cannot as it does not have the focus

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[sean614]

Thanks all! Both methods worked (I had the time to try both). Thanks also for the information, Geoff. I like it when things work, but I also like to know why. I couldn't understand why the macro worked, but not when using with the commandbutton and your explanation makes sense.

 

[Bong]

PHV
You're right. It was an oversight.

_________________
Bob Rashkin

 

[Bong]

PHV,
Wait, I don't know if I agree. Doesn't dim r1,r2 as range declare both r1 and r2 as ranges?

_________________
Bob Rashkin

 

[PHV]

No.

 

[sean614]

This is the code I ended up with using the suggestions above and it does what I intended for it to do:

Private Sub CommandButton2_Click()

Dim r1, r2, r3, r4, r5, r6, r7, r8, r9, r10, r11, r12, r13 As Range

Dim rr1, rr2, rr3, rr4, rr5, rr6, rr7, rr8, rr9, rr10, rr11, rr12, rr13 As Range

Dim rrr1

Set r1 = Sheets("Form").Range("D91:E91")
Set r2 = Sheets("Form").Range("C45:G45")
Set r3 = Sheets("Form").Range("C46:G46")
Set r4 = Sheets("Form").Range("H46:I46")
Set r5 = Sheets("Form").Range("C47:G47")
Set r6 = Sheets("Form").Range("H47:I47")
Set r7 = Sheets("Form").Range("C48:G48")
Set r8 = Sheets("Form").Range("C49:G49")
Set r9 = Sheets("Form").Range("C50")
Set r10 = Sheets("Form").Range("F50:G50")
Set r11 = Sheets("Form").Range("N45:O45")
Set r12 = Sheets("Form").Range("N47:O47")

Set rr1 = Sheets("NMAD _Setup_Info").Range("A1")
Set rr2 = Sheets("NMAD _Setup_Info").Range("B1")
Set rr3 = Sheets("NMAD _Setup_Info").Range("C1")
Set rr4 = Sheets("NMAD _Setup_Info").Range("D1")
Set rr5 = Sheets("NMAD _Setup_Info").Range("E1")
Set rr6 = Sheets("NMAD _Setup_Info").Range("F1")
Set rr7 = Sheets("NMAD _Setup_Info").Range("G1")
Set rr8 = Sheets("NMAD _Setup_Info").Range("H1")
Set rr9 = Sheets("NMAD _Setup_Info").Range("I1")
Set rr10 = Sheets("NMAD _Setup_Info").Range("J1")
Set rr11 = Sheets("NMAD _Setup_Info").Range("K1")
Set rr12 = Sheets("NMAD _Setup_Info").Range("L1")
Set rr13 = Sheets("NMAD _Setup_Info").Range("M1")

Set rrr1 = Sheets("NMAD _Setup_Info").Range("A1:M1")


Sheets("NMAD _Setup_Info").Visible = True
r1.Copy
rr1.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
r2.Copy
rr2.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
rr2.Replace What:=" ", Replacement:="", LookAt:=xlPart, SearchOrder:= _
xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
r2.Copy
rr3.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
r3.Copy
rr4.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
r4.Copy
rr5.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
rr5.Replace What:="Choose One", Replacement:="", LookAt:=xlPart, SearchOrder:= _
xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
r5.Copy
rr6.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
r6.Copy
rr7.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
rr7.Replace What:="Choose One", Replacement:="", LookAt:=xlPart, SearchOrder:= _
xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
r7.Copy
rr8.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
r8.Copy
rr9.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
r9.Copy
rr10.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
r10.Copy
rr11.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
rr11.NumberFormat = "00000"
r11.Copy
rr12.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
r12.Copy
rr13.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Sheets("NMAD _Setup_Info").Visible = False


End Sub

 

[Zathras]

it does what I intended for it to do:

Not sure if you really mean that. Data are getting over-laid. For example, the first copy copies E91 into B1 but the second copy then copies C45 into B1 replacing what was there.

Also, you are setting the format to range rr11, but the data being copied in (from r10) has two cells. Only one cell is formatted (K1)

Also, you start out copying r1 into rr1 and r2 into rr2 (replacing blanks afterwords) but then you copy r2 again into rr3 (without replacing blanks) and proceed with the same offset, r3 --> rr4, r4 --> rr5, etc down to r12 --> r13. Seems like that would make future maintenance harder than it should be.

Here is some code that does exactly what your code does, but it should be a bit easier to maintain:

Sub CommandButton2_Click()
    DoOneCopy "D91:E91", "A1"
    DoOneCopy "C45:G45", "B1", " "
    DoOneCopy "C45:G45", "C1"
    DoOneCopy "C46:G46", "D1"
    DoOneCopy "H46:I46", "E1", "Choose One"
    DoOneCopy "C47:G47", "F1"
    DoOneCopy "H47:I47", "G1", "Choose One"
    DoOneCopy "C48:G48", "H1"
    DoOneCopy "C49:G49", "I1"
    DoOneCopy "C50", "J1"
    DoOneCopy "F50:G50", "K1", "", "00000"
    DoOneCopy "N45:O45", "L1"
    DoOneCopy "N47:O47", "M1"
    Application.CutCopyMode = False
End Sub

Private Sub DoOneCopy(FromAddress As String, ToAddress As String, _
               Optional DoReplace As String = "", _
               Optional NumberFormat As String = "")
Dim rFrom As Range
Dim rTo As Range

  Set rFrom = Sheets("Form").Range(FromAddress)
  Set rTo = Sheets("NMAD _Setup_Info").Range(ToAddress)
  
  rFrom.Copy
  rTo.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
  
  If DoReplace <> "" Then
      rTo.Replace What:=DoReplace, Replacement:="", LookAt:=xlPart, SearchOrder:= _
      xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
  End If
  
  If NumberFormat <> "" Then
    rTo.NumberFormat = NumberFormat 
  End If
End Sub

The second and third lines show clearly that the same range is being copied twice.

Hope this helps.

 

[sean614]

Thank you for the suggestion. I'll have to check into that since I definitely don't want data to be over-laid. As you can surely tell from my code, I have very little experience with vba and I really appreciate the input. Thanks!