[VBA] Thisworkbook.sheets Problem

[Safety]

Hey everyone,

I'm trying to define two ranges composed of non-contiguous columns located on 2 separate sheets (Sheet1 and Sheet2). The script does work with one sheet but it ignores the ThisWorkbook.Worksheets() located in the second With statement and simply selects the range on the same Sheet defined in the first With statement.

I used ".Select" to get a quick visual confirmation of what the script was doing. They will be removed in the final version.

Any advice would be greatly appreciated!

Thanks!

Sub SelectionTest()
    Dim SelectOne As Range, SelectTwo As Range
        
    With ThisWorkbook.Worksheets("Sheet1")
        StartRange = "A2"
        MidRange = "C2"
            
            Set a = Range(StartRange, Range(StartRange).End(xlDown))
            Set b = Range(MidRange, Range(MidRange).End(xlDown))
        
        Set SelectOne = Union(a, b)
        SelectOne.Select
    End With
        
    With ThisWorkbook.Worksheets("Sheet2")
        TratsRange = "A2"
        DimRange = "C2"
        DneRange = "E2"
            
            Set d = Range(TratsRange, Range(TratsRange).End(xlDown))
            Set e = Range(DimRange, Range(DimRange).End(xlDown))
            Set f = Range(DneRange, Range(DneRange).End(xlDown))
        
        Set SelectTwo = Union(d, e, f)
        SelectTwo.Select
    End With

 

[andrewkard]

Hello. I think that you need before "With ThisWorkbook.Worksheets("Sheet2")" select Worksheets("Sheet2")" like this: "Worksheets("Sheet2").select"

 

[Jonmo1]

When using a With structure, you have to qualilfy the sheet that was designated in the with..
Notice the Red periods below..

    With ThisWorkbook.Worksheets("Sheet1")
        StartRange = "A2"
        MidRange = "C2"
 
            Set a = .Range(StartRange, .Range(StartRange).End(xlDown))
            Set b = .Range(MidRange, .Range(MidRange).End(xlDown))
 
        Set SelectOne = Union(a, b)
        SelectOne.Select
    End With

Otherwise, VBA defaults to use whatever sheet happens to be active at the time.

 

[Safety]

Thanks to both of you for the help!

@Jon: The edit you suggested did solve the problem but it also introduced a Run-Time error '1004' in the second to last line "ListTwo.Select". Would you happen to know why this is happening? Thanks again!

Sub SelectionTest()
    Dim ListOne As Range, ListTwo As Range
    
    With ThisWorkbook.Worksheets("Sheet1")
        StartRange = "A2"
        MidRange = "C2"
            
            Set a = .Range(StartRange, .Range(StartRange).End(xlDown))
            Set b = .Range(MidRange, .Range(MidRange).End(xlDown))
        
        Set ListOne = Union(a, b)
        ListOne.Select
    End With
        
    With ThisWorkbook.Worksheets("Sheet2")
        TratsRange = "A2"
        DimRange = "C2"
        DneRange = "E2"
            
            Set d = .Range(TratsRange, .Range(TratsRange).End(xlDown))
            Set e = .Range(DimRange, .Range(DimRange).End(xlDown))
            Set f = .Range(DneRange, .Range(DneRange).End(xlDown))
        
        Set ListTwo = Union(d, e, f)
        ListTwo.Select
    End With
End Sub

 

[Jonmo1]

You can't select a range on a sheet, if that sheet is not currently Active.

You said you had select in there just for testing purposes, you intended to remove it later anyway, so I wouldn't worry about it.

If the first section set ListOne correctly, then the 2nd one will Set ListTwo correctly as well..

 

[Safety]

You can't select a range on a sheet, if that sheet is not currently Active.

Got it! Thanks again Jon!