DIM AS problem

[Damo71]

Hi at the top of my code I have the line:

Dim nws As Worksheet

Then there are several lines of code as the macro works on the current sheet.
The macro creates (and selects) a new sheet, then i have the following line:

Set nws = ActiveSheet

when I run the macro, it breaks at this line:

ActiveWorkbook.Worksheets(nws).AutoFilter.Sort.SortFields.Clear

What am I doing wrong?

Thanks

 

[kpark91]

ActiveWorkbook.nws.AutoFilter.Sort.SortFields.clear

nws is already a worksheet object.
Worksheets(nws) would work if nws was a string.

 

[VoG]

Try

ActiveWorkbook.nws.AutoFilter.Sort.SortFields.Clear

 

[Richard Schollar]

Just:

nws.Autofilter.Sort.SortFields.Clear

would also work perfectly well.

 

[Damo71]

Thanks everyone!

 

[Jonmo1]

All you need is

nws.AutoFilter.Sort.SortFields.Clear


When using worksheet objects, there is no need to specify the workbook
Because the active workbook is implied in this line

Set nws = ActiveSheet

Basically, the Active Workbook is assumed unless you specify otherwise...
So the nws object variable is already tied to whatever book was active at the time that code ran.


to specify a specific workbook for a worksheet object, do it in the SET line.

Set wsn = Workbooks("bookname.xls").Sheets("Sheetname")
If the blue part is omitted, then the current active book is assumed.



Hope that helps.

 

[CWatts]

While the other methods mentioned are a lot cleaner and I'd suggest going with those, below is an alternative that i believe represents what you were trying to do when you were setting up your code.

Dim nws As String
nws = ActiveSheet.Name

Then the below syntax would work:

ActiveWorkbook.Worksheets(nws).AutoFilter.Sort.SortFields.Clear