Error if I'm not on the right worksheet

[Pat_The_Bat]

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!

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

 

[MARK858]

Assuming that range B3 etc. is on the master sheet maybe

    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 = .Range(.Range("B3").Value)
        Set Self = .Range(.Range("B7").Value)
        Set Fixed1 = .Range(.Range("B8").Value)
        Set Fixed2 = .Range(.Range("B9").Value)
        Set Fixed3 = .Range(.Range("B10").Value)
    End With

Btw, can you please use normal code tags and not Html tags so your code formats correctly in future.

 

[PCL]

Perhaps


Set Fixed1 = Sheets("Master").Range(Sheets("Master").Range("B8").Value)

 

[MARK858]

Actually as you are probably using a version later than 2003 the below is more correct than what I posted...

    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 Long
    Dim LstRow2 As Long
    Dim LstRow3 As Long
    Dim LstRow4 As Long

    With Worksheets("Master")
        .Range("D2:D100").ClearContents
        Set Wage1 = .Range(.Range("B3").Value)
        Set Self = .Range(.Range("B7").Value)
        Set Fixed1 = .Range(.Range("B8").Value)
        Set Fixed2 = .Range(.Range("B9").Value)
        Set Fixed3 = .Range(.Range("B10").Value)
    End With

 

[Pat_The_Bat]

Perhaps


Set Fixed1 = Sheets("Master").Range(Sheets("Master").Range("B8").Value)

That appears to be the issue! Thank you! However, while that did alleviate the issue, it now says

Select method of Range class failed on the Wage1.Select line

[/COLOR]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)
        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
[COLOR=#574123]

 

[Pat_The_Bat]

Thanks for the heads up on that. I didn't realize I was doing that wrong.
thx!

 

[PCL]

[COLOR=#333333]Wage1.Select line

It connot run if the active sheet is not the one where you want to make the selection[/COLOR]

 

[MARK858]

[COLOR=#333333]Wage1.Select line

It connot run if the active sheet is not the one where you want to make the selection[/COLOR]

Good point by PCL, thus try changing it to

Application.Goto Wage1

If you want it at the end but please note the code I posted doesn't use it as it serves no purpose in setting the ranges.

 

[Pat_The_Bat]

That does the trick. Is there a way to avoid the application flickering from one sheet to the other when that operation is performed? (I guess maybe I could store that range on the sheet that I'm currently on and then the code doesn't need to switch sheets to get that reference.)
The sheet where that code lies will be hidden to the user, so when I run the code right now with the sheet hidden, it creates quite a spazz out flicker.
Not the end of the world, but was curious to know your thoughts.
Thank you!

 

[MARK858]

Do you get flicker with the code I posted?