Runtime error '1004' select method of worksheet class failed

[patsfan1]

Hello,
Getting above error when running the below Macro, and cannot figure out why for life of me. I have searched endlessly, but am new to VBA so hoping someone can assist.

I understand the code I've used is problematic due to the use of .select and copying/paste back and forth b/n sheets, however, the code was working fine yesterday and now I am getting the runtime error one the second line of copy/past code I've highlighted and bolded in red. I get the error no matter if "sheet1" is visible or hidden to start the code.

My goal is to copy multiple ranges from one sheet ("sheet1") and past those ranges to another sheet("Rent Roll"), just in different columns/rows. I know there is a much easier and cleaner way to do this I just haven't been able to figure it out. Any help in either fixing this issue to get this code working or simplying is very much appreciated.

Sub UpdateRR()

' Copy from hidden RR to main RR
    
    Worksheets("Sheet1").Visible = True
    Application.ScreenUpdating = False
    


    Sheets("Sheet1").Select
    Range("A2:A31").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Rent Roll").Select
    Range("E6").Select
    ActiveSheet.Paste
    
[B][COLOR=#ff0000]    Sheets("Sheet1").Select[/COLOR][/B]
    Range("B2:B31").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Rent Roll").Select
    Range("D6").Select
    ActiveSheet.Paste
    
    Sheets("Sheet1").Select
    Range("C2:C31").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Rent Roll").Select
    Range("G6").Select
    ActiveSheet.Paste
    
    Sheets("Sheet1").Select
    Range("D2:D31").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Rent Roll").Select
    Range("h6").Select
    ActiveSheet.Paste
    
    Sheets("Sheet1").Select
    Range("e2:e31").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Rent Roll").Select
    Range("f6").Select
    ActiveSheet.Paste
    
    Sheets("Sheet1").Select
    Range("f2:f31").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Rent Roll").Select
    Range("j6").Select
    ActiveSheet.Paste
    
    Sheets("Sheet1").Select
    Range("g2:g31").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Rent Roll").Select
    Range("m6").Select
    ActiveSheet.Paste
    
    Sheets("Sheet1").Select
    Range("h2:h31").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Rent Roll").Select
    Range("r6").Select
    ActiveSheet.Paste
    
    Sheets("Sheet1").Select
    Range("i2:i31").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Rent Roll").Select
    Range("t6").Select
    ActiveSheet.Paste
    
    Sheets("Sheet1").Select
    Range("j2:j31").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Rent Roll").Select
    Range("u6").Select
    ActiveSheet.Paste
    
    Sheets("Sheet1").Select
    Range("k2:k31").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Rent Roll").Select
    Range("x6").Select
    ActiveSheet.Paste
    
    Sheets("Sheet1").Select
    Range("l2:l31").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Rent Roll").Select
    Range("y6").Select
    ActiveSheet.Paste
    
    Sheets("Sheet1").Select
    Range("m2:m31").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Rent Roll").Select
    Range("aa6").Select
    ActiveSheet.Paste
    
    Sheets("Sheet1").Select
    Range("n2:n31").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Rent Roll").Select
    Range("ab6").Select
    ActiveSheet.Paste
    
    Sheets("Sheet1").Select
    Range("o2:o31").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Rent Roll").Select
    Range("ae6").Select
    ActiveSheet.Paste
    
    Sheets("Sheet1").Select
    Range("p2:p31").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Rent Roll").Select
    Range("ag6").Select
    ActiveSheet.Paste
    
    Sheets("Sheet1").Select
    Range("q2:q31").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Rent Roll").Select
    Range("af6").Select
    ActiveSheet.Paste
    
    Application.CutCopyMode = False
    
    Worksheets("Sheet1").Visible = False
    
    Application.ScreenUpdating = True
    
End Sub

 

[Fluff]

Hi & welcome to MrExcel.
Try

Sub patsfan1()
   Dim Ws As Worksheet
   
   Worksheets("Sheet1").Visible = True
   Set Ws = Sheets("Sheet1")
   With Sheets("Rent Roll")
      Ws.Range("A2:A31").Copy .Range("E6")
      Ws.Range("B2:B31").Copy .Range("D6")
      Ws.Range("C2:C31").Copy .Range("G6")
      Ws.Range("D2:D31").Copy .Range("h6")
      Ws.Range("e2:e31").Copy .Range("f6")
      Ws.Range("f2:f31").Copy .Range("j6")
      Ws.Range("g2:g31").Copy .Range("m6")
      Ws.Range("h2:h31").Copy .Range("r6")
      Ws.Range("i2:i31").Copy .Range("t6")
   End With
   Ws.Visible = xlSheetHidden
End Sub

You will need to add the rest of the ranges if this works.

 

[patsfan1]

Thank you for the response Fluff. I used your code but only the first range was copied over from "Sheet1" to "Rent Roll", none of the subsequent ranges. Any ideas on how to correct that?

 

[DanteAmor]

Thank you for the response Fluff. I used your code but only the first range was copied over from "Sheet1" to "Rent Roll", none of the subsequent ranges. Any ideas on how to correct that?

Obvious question. Do you have data on these ranges on Sheet1?
Do you have formulas in those ranges?
Do you have any other macro running on your sheets?

 

[Fluff]

Along with DanteAmor's questions, did you copy paste the code from post#2, or did you type out yourself?
If the latter do you have the period (.) before the 2nd range on each line?

 

[patsfan1]

Obvious question. Do you have data on these ranges on Sheet1?
Do you have formulas in those ranges?
Do you have any other macro running on your sheets?

Ok, that was it. There was a macro running in the sheet code after a change in cell value. Removed that and everything is working perfectly. Thank you everyone for your assistance!

 

[Fluff]

You're welcome & thanks for the feedback

 

[DanteAmor]

Ok, that was it. There was a macro running in the sheet code after a change in cell value. Removed that and everything is working perfectly. Thank you everyone for your assistance!

I'm glad to help you. Thanks for the feedback.