Solved Run Time Error '1004': Select Method of Worksheet Failed

July 2, 2014 at 08:58:02
Specs: Windows 7
Run Time Error '1004': Select Method of Worksheet Failed

This comes up after selecting an entire row and running a macro from ctrl+m which is suppose to print the work order that it is associated with.

-------------------------------------------------------------------------------------------

Debug Macro Code:

Sub Macro49()
'
' Macro49 Macro
'
' Keyboard Shortcut: Ctrl+m
'

ActiveCell.Rows("1:1").EntireRow.Select
Application.CutCopyMode = False
Selection.Copy
-->Error Line--> Sheets("Print Form").Select
ActiveCell.Rows("1:1").EntireRow.Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
ExecuteExcel4Macro "PRINT(1,,,1,,,,,,,,2,,,TRUE,,FALSE)"
Sheets("maintenance").Select
ActiveCell.FormulaR1C1 = "=NOW()"
ActiveCell.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
ActiveWorkbook.Save
ActiveWorkbook.Save

End Sub
-------------------------------------------------------------------------------------

Any Help?! I know very little VB so any and all help will be much appreciated. Thanks!


See More: Run Time Error 1004: Select Method of Worksheet Failed

Report •


#1
July 3, 2014 at 06:35:40
✔ Best Answer
I'll address your question in a moment, but first I'd like to offer a posting tip...

Before posting any more VBA code in this forum, please click on the blue line at the end of this post and read the instructions on how to post example data and VBA so that it is easier for us to read. Thanks!


As for your question, I am unable to duplicate your error, but maybe I can help anyway.

It appears to me that the code you posted was created via the macro recorder. When I I see code that uses .Select fairly often and extra instructions (e.g. the duplicate .Save instructions) I assume that the macro was created by the VBA Recorder.

Rarely do you have to Select an object via VBA to perform an action on it. For example, these instructions...

  ActiveCell.Rows("1:1").EntireRow.Select
    Application.CutCopyMode = False
     Selection.Copy

...can usually be reduced to a single instruction:

  ActiveCell.Rows("1:1").EntireRow.Copy

So since I can't duplicate your error, the best I can offer is to make your code a bit more efficient by eliminating the extra instructions, including the Selection of the "Print Form" sheet to see if that helps.

Note: I suggest that you test the following code in a backup copy of your workbook since macros can not easily be undone. The main concern I have is the fact that I am using the ActiveCell to Paste the copied data and the Now function. They may not end up where you actually want them, but that can be fixed if you'll give me some more detail as to what cells they belong in on the Print Form and Maintenance sheet.

Sub Print_Form()
'
' Print_Form Macro
'
'

'Copy ActiveCell Row
    ActiveCell.Rows("1:1").EntireRow.Copy
'PasteSpecial into Print Form shet
    Worksheets("Print Form").Cells(ActiveCell.Row, 1).PasteSpecial _
         Paste:=xlPasteValuesAndNumberFormats, Operation:= _
                xlNone, SkipBlanks:=False, Transpose:=False
'Print Print Form sheet
    Worksheets("Print Form").PrintOut
'Put Date & Time in maintenace sheet
    Sheets("maintenance").Range(ActiveCell.Address) = Now
'Save the Workbook
    ActiveWorkbook.Save
End Sub

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


Report •
Related Solutions


Ask Question