Error if I'm not on the right worksheet

P

Pat_The_Bat

Board Regular
Joined
Jul 12, 2018
Messages
83
When I am on the Sheet "Master", and I step through this code there is no problem. But if I'm on one of the other sheets in the wkbk and I start stepping through the code, I get Run-Time error '1004':
Application-defined or object-defined error

I'm guessing it has to do with the way I've set the range = to the value of the cell B3.
Any help is appreciated!


HTML: 
Dim Bor1 As StringDim Bor2 As StringDim Bor3 As StringDim Bor4 As String
Dim Wage1 As RangeDim Wage2 As RangeDim Wage3 As RangeDim Wage4 As RangeDim Self  As RangeDim Fixed1 As RangeDim Fixed2 As RangeDim Fixed3 As Range

Dim LstRow1 As IntegerDim LstRow2 As IntegerDim LstRow3 As IntegerDim LstRow4 As Integer
Worksheets("Master").Range("D2:D100").ClearContents        With Worksheets("Master")                Set Wage1 = Sheets("Master").Range(Range("B3").Value)        Wage1.Select        Set Self = Sheets("Master").Range(Range("B7").Value)        Wage1.Select        Set Fixed1 = Sheets("Master").Range(Range("B8").Value)        Wage1.Select        Set Fixed2 = Sheets("Master").Range(Range("B9").Value)        Wage1.Select        Set Fixed3 = Sheets("Master").Range(Range("B10").Value)        Wage1.Select        End With
 
Sort by date Sort by votes

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
That's quite amazing if it is the code I posted as nowhere does it select anything on the sheet, it does it all in memory bar the clearcontents where it doesn't select the range.

Post the full code you are currently using.
 
Last edited:
Upvote 0
Code: 
Sub Graphic2_Click()


'This section checks the income templates to see which income docs to load from the Master to the Doc Checklist:
Dim Bor1 As String
Dim Bor2 As String
Dim Bor3 As String
Dim Bor4 As String


Dim Wage1 As Range
Dim Wage2 As Range
Dim Wage3 As Range
Dim Wage4 As Range
Dim Self  As Range
Dim Fixed1 As Range
Dim Fixed2 As Range
Dim Fixed3 As Range




Dim LstRow1 As Integer
Dim LstRow2 As Integer
Dim LstRow3 As Integer
Dim LstRow4 As Integer


    With Worksheets("Master")
        .Range("D2:D100").ClearContents
        Set Wage1 = Sheets("Master").Range(Sheets("Master").Range("B3").Value)
        Application.Goto Wage1
        Wage1.Select
        Set Self = Sheets("Master").Range(Sheets("Master").Range("B7").Value)
        Wage1.Select
        Set Fixed1 = Sheets("Master").Range(Sheets("Master").Range("B8").Value)
        Wage1.Select
        Set Fixed2 = Sheets("Master").Range(Sheets("Master").Range("B9").Value)
        Wage1.Select
        Set Fixed3 = Sheets("Master").Range(Sheets("Master").Range("B10").Value)
        Wage1.Select
        End With
        
                
        With Worksheets("Doc Request")
         Bor1 = .Range("B3").Value
         Bor2 = .Range("C3").Value
         Bor3 = .Range("D3").Value
         Bor4 = .Range("E3").Value
            Debug.Print Bor1
              Debug.Print Bor2
                Debug.Print Bor3
                  Debug.Print Bor4
  
        End With
        
       
                
        
'*******************************************************************************************
'********  ADDING BORROWER INCOME DOC TEMPLATES
'*******************************************************************************************


        
  
'BORROWER 1
'Activate Bor 1 for all Template Docs


            Sheets("Master").Range("E2:E100") = Bor1
            Sheets("Master").Range("D2:D100").ClearContents
            With Worksheets("Doc Checklist")
                LstRow1 = .Range("D" & .Rows.Count).End(xlUp).Row + 1
            End With
                                          
            Debug.Print LstRow1
'Add Checkmarks for Bor 1 Template
        With Sheets("Doc Request")
            If .Range("B4").Value2 = "x" Then Wage1 = "x"
            If .Range("B5").Value2 = "x" Then Wage1 = "x"
            If .Range("B6").Value2 = "x" Then Wage1 = "x"
            If .Range("B7").Value2 = "x" Then Wage1 = "x"
            If .Range("B8").Value2 = "x" Then Self = "x"
            If .Range("B9").Value2 = "x" Then Fixed1 = "x"
            If .Range("B10").Value2 = "x" Then Fixed2 = "x"
            If .Range("B11").Value2 = "x" Then Fixed3 = "x"
            
        End With




'Copy the concatenated Income Doc to the Doc Checklist




            With Sheets("Master").Range("D2:D100").SpecialCells(xlConstants)
              .Offset(, 7).Copy
             
            Sheets("Doc Checklist").Range("D" & LstRow1).PasteSpecial Paste:=xlPasteValues
            End With
            
            Debug.Print LstRow1
            
            
            
            
            
'BORROWER 2
'Activate Bor 2 for all Template Docs


            Sheets("Master").Range("E2:E100") = Bor2
            Sheets("Master").Range("D2:D100").ClearContents
            
            With Worksheets("Doc Checklist")
            LstRow2 = .Range("D" & .Rows.Count).End(xlUp).Row + 1
            End With
            
            Debug.Print LstRow2
            
'Add Checkmarks for Bor 1 Template
        With Sheets("Doc Request")
            If .Range("C4").Value2 = "x" Then Wage1 = "x"
            If .Range("C5").Value2 = "x" Then Wage1 = "x"
            If .Range("C6").Value2 = "x" Then Wage1 = "x"
            If .Range("C7").Value2 = "x" Then Wage1 = "x"
            If .Range("C8").Value2 = "x" Then Self = "x"
            If .Range("C9").Value2 = "x" Then Fixed1 = "x"
            If .Range("C10").Value2 = "x" Then Fixed2 = "x"
            If .Range("C11").Value2 = "x" Then Fixed3 = "x"
        End With




'Copy the concatenated Income Doc to the Doc Checklist
                  
            With Sheets("Master").Range("D2:D100").SpecialCells(xlConstants)
              .Offset(, 7).Copy
            Sheets("Doc Checklist").Range("D" & LstRow2).PasteSpecial Paste:=xlPasteValues
            End With
            
            
            
            
       
'BORROWER 3
'Activate Bor 3 for all Template Docs


            Sheets("Master").Range("E2:E100") = Bor3
            Sheets("Master").Range("D2:D100").ClearContents
            
            With Worksheets("Doc Checklist")
            LstRow3 = .Range("D" & .Rows.Count).End(xlUp).Row + 1
            End With
            
            Debug.Print LstRow3
            
'Add Checkmarks for Bor 3 Template
        With Sheets("Doc Request")
            If .Range("D4").Value2 = "x" Then Wage1 = "x"
            If .Range("D5").Value2 = "x" Then Wage1 = "x"
            If .Range("D6").Value2 = "x" Then Wage1 = "x"
            If .Range("D7").Value2 = "x" Then Wage1 = "x"
            If .Range("D8").Value2 = "x" Then Self = "x"
            If .Range("D9").Value2 = "x" Then Fixed1 = "x"
            If .Range("D10").Value2 = "x" Then Fixed2 = "x"
            If .Range("D11").Value2 = "x" Then Fixed3 = "x"
        End With




'Copy the concatenated Income Doc to the Doc Checklist
                  
            With Sheets("Master").Range("D2:D100").SpecialCells(xlConstants)
              .Offset(, 7).Copy
            Sheets("Doc Checklist").Range("D" & LstRow3).PasteSpecial Paste:=xlPasteValues
            End With
           
           
           
'BORROWER 4
'Activate Bor 4 for all Template Docs


            Sheets("Master").Range("E2:E100") = Bor4
            Sheets("Master").Range("D2:D100").ClearContents
            
            With Worksheets("Doc Checklist")
            LstRow4 = .Range("D" & .Rows.Count).End(xlUp).Row + 1
            End With
                        
            Debug.Print LstRow4
            
'Add Checkmarks for Bor 4 Template
        With Sheets("Doc Request")
            If .Range("E4").Value2 = "x" Then Wage1 = "x"
            If .Range("E5").Value2 = "x" Then Wage1 = "x"
            If .Range("E6").Value2 = "x" Then Wage1 = "x"
            If .Range("E7").Value2 = "x" Then Wage1 = "x"
            If .Range("E8").Value2 = "x" Then Self = "x"
            If .Range("E9").Value2 = "x" Then Fixed1 = "x"
            If .Range("E10").Value2 = "x" Then Fixed2 = "x"
            If .Range("E11").Value2 = "x" Then Fixed3 = "x"
        End With




'Copy the concatenated Income Doc to the Doc Checklist
                  
            With Sheets("Master").Range("D2:D100").SpecialCells(xlConstants)
              .Offset(, 7).Copy
            Sheets("Doc Checklist").Range("D" & LstRow4).PasteSpecial Paste:=xlPasteValues
            End With
 
 '*****************************************************************************************************************
 '********* THAT IS THE END OF ADDING THE INCOME TEMPLATES
 '*****************************************************************************************************************
 
    


  With Sheets("Doc Checklist")
    .Range("C2:C500").Sort Key1:=.Range("C2"), Order1:=xlAscending, Header:=xlNo
    End With
    
  Worksheets("Doc Checklist").Range("D2:D100").Copy Worksheets("Doc Request").Range("I4:I100")
  Worksheets("Doc Request").Activate
  Worksheets("Doc Request").Range("A1").Select
  
  MsgBox ("Income Templates Have Been Loaded")
  
    
End Sub
 
Upvote 0
That's fine as it doesn't use the code I posted. Again the select or goto Wage1 serves no purpose in setting your ranges (and even if it did you wouldn't use them both together).
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question Question
Split master into seperate worksheets: getting overlapping error
Replies
0
Views
413
Melimob
M
P
  • Question Question
Set Range Variable to the value of a cell so user can input range in Excel Cell
Replies
1
Views
522
Fluff
Fluff
P
  • Question Question
The cell or chart you're trying to change is on a protected sheet. Error upon placing worksheet Change Selection Code
Replies
1
Views
5K
pingme89
P
A
  • Question Question
inserting exclusion list to merge sheets macro
Replies
2
Views
670
ajm
A
R
  • Question Question
VBA Automation Error - possibly born from my complete lack of understanding with Error Handling
2 3
Replies
23
Views
9K
RockandGrohl
R

Forum statistics

Threads
1,223,924
Messages
6,175,415
Members
452,640
Latest member
steveridge

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top