Run time error 1004 Select methof of Worksheet class failed

[anne.gomes]

Hi everyone,

Im getting this error on this line: Sheets("Sheet1").Select, I'm not exactly sure why.
Any ideas???



Sub Copy()
On Error GoTo ErrHandler
Sheets("Sheet1").Select

Workbooks.Open Filename:="C:\Users\anneg\Desktop\Book1.xlsx"
Range("A1", ActiveCell.SpecialCells(xlLastCell)).Select
Selection.Copy
Windows("Book2.xlsm").Activate
Sheets("Sheet1").Range("A10").Select
Selection.Insert Shift:=xlDown
ActiveSheet.Paste

Exit Sub

ErrHandler:
Select Case Err.Number
Case 1004
MsgBox "You must unhide the sheet first."
Case 9
MsgBox "No such sheet name exists."
Case Else
MsgBox "Error, see workbook author."
End Select

End Sub


Thank you

[westconn1]

try fully qualifying the sheet, prefix with workbook, or better

workbooks("Book2.xlsm").Sheets("Sheet1").Range("A10").Insert Shift:=xlDown

[mikerickson]

Is your sheet really named Sheet1? No extra spaces?
What error are you getting?

[anne.gomes]

Thanks for all your replies. I started again and now have another problem. I want to copy a row from one workbook to another into the next available blank row. This is what I have so far:




Sub Copy()
Application.DisplayAlerts = wdAlertsNone
dt = Format(CStr(Now), "mmmm d, yyyy")
ChDir "C:\Users\anneg\Desktop"
Workbooks.Open Filename:="C:\Users\anneg\Desktop\Book1.xlsx"
Rows("1:1").Select
Selection.Copy
ActiveWindow.Close

Range("A1").Select 'how to select next empty row on the worksheet and paste it onto that[/COLOR]
ActiveSheet.Paste
Application.CutCopyMode = False

ActiveWorkbook.SaveAs Filename:="C:\Users\anneg\Documents\Annedate" & dt & ".xlsm", FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
End Sub




Thank you for all your help, really appreciate it! :)

[anne.gomes]

sorry the [COLOR=#FF0000] is not actually in my code I used it to highlight that comment

[Bob Phillips]

Sub Copy()
Dim lastrow As Long

Application.DisplayAlerts = wdAlertsNone
dt = Format(CStr(Now), "mmmm d, yyyy")
ChDir "C:\Users\anneg\Desktop"
Workbooks.Open Filename:="C:\Users\anneg\Desktop\Book1.xlsx"
Rows(1).Copy
ActiveWindow.Close

Range("A1").End(xlDown).Offset(1, 0).Select 'how to select next empty row on the worksheet and paste it onto that[/COLOR]
ActiveSheet.Paste
Application.CutCopyMode = False

ActiveWorkbook.SaveAs Filename:="C:\Users\anneg\Documents\Annedate" & dt & ".xlsm", _
FileFormat:=xlOpenXMLWorkbookMacroEnabled, _
CreateBackup:=False
End Sub

[anne.gomes]

Thank you!!!!