Hi Gtton,

I think the command button is brilliant. But when I try to run a macro
that pastes cells, it runs into error even though on the forms button,
it runs fine. It highlights Rows("322:329").Select the line causing
the error in VB editor.

Your code fails on this line because the range is not sufficiently
qualified. If the problem with this line were resolved, your code would fail
on subsequent lines for the same reason.

As your code resides in the worksheet module, any unqualified range
reference will be interpreted as a range on the worksheet holding the code.

Therefore, after your code selects another worksheet [Sheets("Data")], it
sees the line:
Rows("322:329").Select
and, since the range is unqualified, it assumes that this range is on the
sheet holding the code. As your code cannot select a range on (what it
perceives to be) another sheet it throws the error you have experienced. If
you qualify the range thus:

Sheets("Data").Rows("32:39").Select

you will obviate the problem. You will need similarly to qualify other range
references in your code.

it runs into error even though on the forms button, it runs fine.

When you used the Forms button, your code resided in a normal module. Here,
unqualfied range references will be deemed to apply to the active sheet, so
your code worked. In general, it is good (wise?) practice fully to qualify
references as a matter of course. Doing so may appear tedious, but it will
avoid not only the problem you have experienced, but also problems of a much
more subtle nature which can be exceedingly difficult to identify and
resolve.

As a final comment, it is rarely necessary or desirable to make physical
selections. These tend to make your code run more slowly and render your
code more difficult to maintain.

Try therefo

Sub CommandButton1_Click()
Dim YesNo

YesNo = MsgBox("Are You Sure You Want To Insert " _
& "Multiple Location Drop Menus?", _
vbYesNo + 48, "Confirm Multiple Insert")

Select Case YesNo
Case vbYes
Sheets("Data").Rows("32:39").Copy
Sheets("3E Submittal Cover Sheet").Rows("46:46"). _
Insert Shift:=xlDown
ActiveWindow.ScrollRow = 32
Application.Goto Sheets("3E Submittal Cover Sheet"). _
Range("B54")
Application.ScreenUpdating = False
Case vbNo
'Insert your code here if No is clicked
End Select

Application.ScreenUpdating = True

Me.CommandButton1.Enabled = False
End Sub

---
Regards,
Norman



"gtton" wrote in
message ...

I think the command button is brilliant. But when I try to run a macro
that pastes cells, it runs into error even though on the forms button,
it runs fine. It highlights Rows("322:329").Select the line causing
the error in VB editor.

Here's the macro:

Sub CommandButton1_Click()
YesNo = MsgBox("Are You Sure You Want To Insert Multiple Location Drop
Menus?", vbYesNo + 48, "Confirm Multiple Insert")
Select Case YesNo
Case vbYes
Sheets("Data").Select
Rows("322:329").Select
Selection.Copy
Sheet1("3E Submittal Cover Sheet").Select
Rows("46:46").Select
Selection.Insert Shift:=xlDown
ActiveWindow.ScrollRow = 32
Range("B54").Select
Application.ScreenUpdating = False
Case vbNo
'Insert your code here if No is clicked
End Select

Application.ScreenUpdating = True
Sheet1.CommandButton1.Enabled = False 'Gray out the button

End Sub


--
gtton
------------------------------------------------------------------------
gtton's Profile:
http://www.excelforum.com/member.php...o&userid=24721
View this thread: http://www.excelforum.com/showthread...hreadid=383303