View Full Version : Run time error 1004 Select methof of Worksheet class failed
anne.gomes
05-22-2014, 08:55 PM
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
05-23-2014, 02:37 AM
try fully qualifying the sheet, prefix with workbook, or better
workbooks("Book2.xlsm").Sheets("Sheet1").Range("A10").Insert Shift:=xlDown
mikerickson
05-25-2014, 01:05 PM
Is your sheet really named Sheet1? No extra spaces?
What error are you getting?
anne.gomes
05-25-2014, 03:11 PM
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
05-25-2014, 03:12 PM
sorry the [COLOR=#FF0000] is not actually in my code I used it to highlight that comment
Bob Phillips
05-26-2014, 01:58 AM
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
05-26-2014, 04:02 PM
Thank you!!!!
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.