Range.Select
 

I have a very uncomplicated code that is giving me troubles. i hav
been trying to figure this out for much longer than i want to admit.
here is the code.

Private Sub CommandButton2_Click()

msg = "Did you Export the CMS Data to your Clipboard?"
Style = vbYesNo + vbDefaultButton2
Title = "QUESTION"
Ctxt = 1000
Response = MsgBox(msg, Style, Title, Help, Ctxt)

If Response = vbYes Then

Worksheets("RECAP").Select
Columns("aa:iv").ClearContents
Sheet2.Paste Destination:=Sheet2.Range("aA1")
Range("ah7").Select
Sheets("armdore").Select
Range("c9").Select

Do

If IsEmpty(ActiveCell) = False Then

ActiveCell.Offset(0, 1).Select

End If

Loop Until IsEmpty(ActiveCell) = True
'inbound
Sheets("RECAP").Select
Selection.Copy
Sheets("armdore").Select
Selection.PasteSpecial Paste:=xlPasteValues, operation:=xlNone
skipblanks _
:=False, Transpose:=False
'customer service
Sheets("RECAP").Select
ActiveCell.Offset(2, 0).Select
Selection.Copy
Sheets("armdore").Select
ActiveCell.Offset(3, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues, operation:=xlNone
skipblanks _
:=False, Transpose:=False
'TPF sales
Sheets("RECAP").Select
ActiveCell.Offset(4, -1).Select
Selection.Copy
Sheets("armdore").Select
ActiveCell.Offset(3, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues, operation:=xlNone
skipblanks _
:=False, Transpose:=False
'TPF corp Sales
Sheets("RECAP").Select
ActiveCell.Offset(2, 0).Select
Selection.Copy
Sheets("armdore").Select
ActiveCell.Offset(3, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues, operation:=xlNone
skipblanks _
:=False, Transpose:=False
'P&H sales
Sheets("RECAP").Select
ActiveCell.Offset(4, 1).Select
Selection.Copy
Sheets("armdore").Select
ActiveCell.Offset(3, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues, operation:=xlNone
skipblanks _
:=False, Transpose:=False
'MC sales
Sheets("RECAP").Select
ActiveCell.Offset(-1, 0).Select
Selection.Copy
Sheets("armdore").Select
ActiveCell.Offset(6, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues, operation:=xlNone
skipblanks _
:=False, Transpose:=False
'HS Sales
Sheets("RECAP").Select
ActiveCell.Offset(0, 0).Select
Selection.Copy
Range("ah7").Select
Sheets("armdore").Select
ActiveCell.Offset(6, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues, operation:=xlNone
skipblanks _
:=False, Transpose:=False


End If

End Sub

it is giving me the following error: Run-time error '1004': Selec
method of range class failed
when i go to debug it highlights line 14 - range("ah7").select.
i have tried moving this line, and changing it. any suggestions

--
Message posted from http://www.ExcelForum.com

Range.Select
 

"scottnshelly " wrote in
message ...
I have a very uncomplicated code that is giving me troubles. i have
been trying to figure this out for much longer than i want to admit.
here is the code.

Private Sub CommandButton2_Click()

it is giving me the following error: Run-time error '1004': Select
method of range class failed
when i go to debug it highlights line 14 - range("ah7").select.
i have tried moving this line, and changing it. any suggestions?



It sounds like the button that your code is attached to still has
focus. Try changing the TakeFocusOnClick property of the button to
False. This should prevent the error from occurring.

FWIW, it looks like there are several lines of code that could be
removed safely. Most of the stuff that we need to do with code can be
done without changing the selection and referring to
ActiveSheet/ActiveCell. Doing so will improve performance and make
the code easier to maintain.


Hope this helps,

-Mike