Specifying a Worksheet with VBA

Matt_O

Board Regular
Joined
May 23, 2013
Messages
62
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).

Code:
[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
 

Some videos you may like

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
12,390
Office Version
365, 2010
Platform
Windows, Mobile
Try...

Code:
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

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
Try

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

 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
12,390
Office Version
365, 2010
Platform
Windows, Mobile
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) :banghead:
 

Matt_O

Board Regular
Joined
May 23, 2013
Messages
62
Thanks everyone for the really quick replies. Once the trick or treaters are done I'll try out these suggestions.

Much appreicated!

Cheers,
Matt
 

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
Mark May be right about needing to specify the book as well as the sheet

Code:
With wb.Worksheets("IIP")
 

Matt_O

Board Regular
Joined
May 23, 2013
Messages
62
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.

Code:
[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
 

Watch MrExcel Video

Forum statistics

Threads
1,095,479
Messages
5,444,728
Members
405,298
Latest member
fxtrtr17

This Week's Hot Topics

Top