Run-Time Error 1004 - Select method of range class failed

[2000km12]

Hello, I am extremely new to VBA. I am trying to do something very simple and I am running into this error. In my Excel Workbook, there are several sheets. The ones of interest are named "CAR Daily Report Macro" and "CAR Daily Report." I am setting up a macro to copy and paste data from another workbook (this part of the code is working) and I want it to delete the top 5 rows on the new sheet, which is where I am getting the error code. I (think) I changed the active sheet, so I do not know what is happening. here is my code:





Private Sub CAR_Daily_Report_Click()



'Settings to make Macro load faster
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Application.DisplayStatusBar = False
Application.EnableEvents = False
ActiveSheet.DisplayPageBreaks = False


'Create new sheet and copy information from exported sheet
Sheets.Add.Name = "CAR Daily Report"

Workbooks("02. CARs_5. All.xlsx").Worksheets("02. CARs_5. All").Range("A1:G3000").Copy _
Workbooks("Main Macro Workbook.xlsm").Worksheets("CAR Daily Report").Range("A1")

'Change the active sheet
Worksheets("CAR Daily Report").Activate


'Attempt at deleting top rows
Rows("1:5").Select
Selection.Delete Shift:=xlUp

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
Application.DisplayStatusBar = True
Application.EnableEvents = True
ActiveSheet.DisplayPageBreaks = True



End Sub

 

[DanteAmor]

Hi @2000km12. Welcome to the MrExcel forum. Please accept my warmest greetings and sincere hope that all is well.

If you are going to delete the first 5 rows, then only copy from row 6 onwards:

Private Sub CAR_Daily_Report_Click()
  'Create new sheet and copy information from exported sheet
  Sheets.Add.Name = "CAR Daily Report"
 
  Workbooks("02. CARs_5. All.xlsx").Worksheets("02. CARs_5. All").Range("A6:G3000").Copy _
    ThisWorkbook.Worksheets("CAR Daily Report").Range("A1")
End Sub

--------------
Let me know the result and I'll get back to you as soon as I can.
Sincerely
Dante Amor
--------------

 

[2000km12]

Thank you for your response! I am stupid and didnt think of that! From a learning standpoint however, why cannot I select rows 1-5 on the new worksheet after I have already transfered it over? I changed my active sheet, so I would think it would allow me to do that.

I want to know why it isn't working as this is not the end of my code. A lot more selections and deletions happen after that and I need to know how to do those since I am getting the same error code for any selecting I am doing. Thanks

 

[DanteAmor]

I want to know why it isn't working a

Your code as it is, works for me, no errors.

Can you tell exactly what the error message says?
At which line of the macro does it stop?

There are other factors that you have not told us:
- You have hidden sheets.
- You have the sheet protected.
- You have the book protected.
- You have hidden rows.
- Do you have formulas in the source sheet?

There are several factors that I cannot guess.

But with two books in normal conditions and the sheets in normal conditions, your macro works without problem.


You can delete the rows without selecting them, it's a best practice, for example:

Private Sub CAR_Daily_Report_Click_v0()
  'Settings to make Macro load faster
  Application.ScreenUpdating = False
  Application.Calculation = xlCalculationManual
  Application.DisplayStatusBar = False
  Application.EnableEvents = False
  ActiveSheet.DisplayPageBreaks = False
 
  'Create new sheet and copy information from exported sheet
  Sheets.Add.Name = "CAR Daily Report"
 
  Workbooks("02. CARs_5. All.xlsx").Worksheets("02. CARs_5. All").Range("A1:G3000").Copy _
    ThisWorkbook.Worksheets("CAR Daily Report").Range("A1")
 
  'Change the active sheet
  Worksheets("CAR Daily Report").Rows("1:5").Delete Shift:=xlUp
 
  Application.ScreenUpdating = True
  Application.Calculation = xlCalculationAutomatic
  Application.DisplayStatusBar = True
  Application.EnableEvents = True
  ActiveSheet.DisplayPageBreaks = True
End Sub

 

[2000km12]

Deleting the rows without selecting them is working now, although I would love to find out why it wasnt working. It was freezing at the "select" line of code with the error code in the screenshot below. I do not have any hidden rows, volumns, sheets, or workbooks to my knowledge. Although I do work for a defense company and the sheet might be protected without my knowledge?

 

[DanteAmor]

I already solved your problem in 2 ways.
I don't know why you have an error when selecting the rows, as I told you, there may be factors that I can't see, because I don't have your books.
But selecting the rows to delete them, it's a bad practice and it's done in several steps, and as I already showed you, you can solve it in a single line to delete.

I want to know why it isn't working as this is not the end of my code. A lot more selections and deletions happen after that and I need to know how to do those since I am getting the same error code for any selecting I am doing. Thanks

So if you are going to delete rows, you already saw how you should do it.
No need to select.

Try everything in other books.

That is, run the test like I do, on 2 new workbooks, and check the result of your macro.
That way you can see that the macro works in new books and the problem is in the original books.