Specifying a Worksheet with VBA

[Matt_O]

Hi Excel Experts,

I am struggling with how to reference a Worksheet in VBA and hope someone can help me.

I have a macro enable book where the code opens a different Workbook and copies the first worksheet to the macro book.

Once the code has done its thing I need to sort the results.

I had a 'sort' code working in an earlier version of the macro workbook but it was inefficient code and I'm trying to eliminate all instances of 'select', 'copy', paste' and 'activate'.​

My sort code works so long as the worksheet containing the data to sort is active. Here's the old code which worked fine although I don't know if the worksheet with the data was active or not. The old code is messy and difficult for me to completely trace.

To sort the data I created 2 Ranges that use a variable (count).

[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]
    'rgSort is the 7 columns containing the data
    'rgCol is the column that gets sorted from smallest to largest


[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif][LEFT][COLOR=#222222][FONT=Verdana]    Dim rgSort As Range, rgCol As Range [/FONT][/COLOR][/LEFT][/FONT]

[/FONT][FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]    Set rgSort = Worksheets("IIP").Range(Cells(2, 2), Cells(count, 7))
 
    Set rgCol = Worksheets("IIP").Range(Cells(2, 7), Cells(count, 7))

[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]    rgSort.Sort Key1:=rgCol, order1:=xlAscending

[/FONT]  'When I run this piece of code and the sheet 'IIP' is not active I get a "Runtime error '1004' Application-defined or object defined error."  

'I've tried the following code where I set 'wb' as the macro enabled workbook and shIIP is the code name for the worksheet 'IIP'.

[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]    Set rgSort = wb.shIIP.Range(shIIP.Cells(2, 2), shIIP.Cells(count, 7))
 
    Set rgCol = wb.shIIP.Range(shIIP.Cells(2, 7), shIIP.Cells(count, 7))

'And I get a Run-time error '438' Object doesn't support this property or method.

I've also tried not referencing the workbook (wb) to no avail.

[/FONT][FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]    Set rgSort = shIIP.Range(shIIP.Cells(2, 2), shIIP.Cells(count, 7))
 
    Set rgCol = shIIP.Range(shIIP.Cells(2, 7), shIIP.Cells(count, 7))[/FONT]
[/FONT]

I can get the code to function as it should so long as I activate the sheet 'IIP' at the top of my code. But my goal is to do away with using 'active' in the code.

Any ideas?

Thanks in advance to anyone who answers.

Matt

 

[MARK858]

Try...

Set rgSort = wb.shIIP.Range(wb.shIIP.Cells(2, 2), wb.shIIP.Cells(count, 7))
Set rgCol = wb.shIIP.Range(wb.shIIP.Cells(2, 7), wb.shIIP.Cells(count, 7))

 

[Jonmo1]

Try

With Worksheets("IIP")
    Set rgSort = .Range(.Cells(2, 2), .Cells(count, 7))
    Set rgCol = .Range(.Cells(2, 7), .Cells(count, 7))
End With

 

[MARK858]

I'm glad that Jonmo1 in post number 3 picked up on that you stated "and shIIP is the code name" which I missed and wrongly assumed that shIIP was a variable (although I think you might need to still reference the wb in the With statement but test it and see)

 

[Matt_O]

Thanks everyone for the really quick replies. Once the trick or treaters are done I'll try out these suggestions.

Much appreicated!

Cheers,
Matt

 

[MARK858]

I'll try out these suggestions.

My suggestion won't work if shIIP is the code name, try the code by Jonmo1.

 

[Jonmo1]

Mark May be right about needing to specify the book as well as the sheet

With wb.Worksheets("IIP")

 

[Matt_O]

Thanks again everyone for chiming in.

I either kept getting '438' errors or else had to go back and make the worksheet "IIP" active in order for this code to run correctly and this confounded me. Then I realized that I forgot to update other parts of the code to work inside the 'With' part. I forgot to add "." before Cells.

Without adding the "." the code ran without errors but wasn't providing the expected results because the range was getting sorted by cells with no data.

[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]        If .Cells(srt, 6).Value = "none" Then
            .Cells(srt, 7).Value = "1"
        End If[/FONT]

I was so focused on ensuring that the code was directed to the IIP worksheet I forgot to look at the bigger picture.

Thanks again!

Matt