Runtime error '1004' select method of worksheet class failed

patsfan1

New Member
Joined
Jul 30, 2019
Messages
11
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.


Code:
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
 
Last edited by a moderator:

Some videos you may like

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
41,089
Office Version
365
Platform
Windows
Hi & welcome to MrExcel.
Try
Code:
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

New Member
Joined
Jul 30, 2019
Messages
11
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

Well-known Member
Joined
Dec 3, 2018
Messages
11,868
Office Version
2007
Platform
Windows
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
41,089
Office Version
365
Platform
Windows
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

New Member
Joined
Jul 30, 2019
Messages
11
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
41,089
Office Version
365
Platform
Windows
You're welcome & thanks for the feedback
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
11,868
Office Version
2007
Platform
Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,099,803
Messages
5,470,872
Members
406,733
Latest member
darzu

This Week's Hot Topics

Top