Method or Data Member Not Found

chrfra

New Member
Joined
Nov 18, 2020
Messages
6
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
This code was working until the latest Windows update. Now it gives this error. (See attached pictures.) Do you have any thoughts as to why?
VBA Code:
Sub WorksheetLoop()

Dim WS_Count As Integer

Dim I As Integer

' Set WS_Count equal to the number of worksheets in the active workbook

WS_Count = ActiveWorkbook.Worksheets.Count



' Begin the loop

For I = 1 To WS_Count

Rows("1:1").Select

Selection.Delete Shift:=xlUp

Range("D:D,F:F").Select

Range("F1").Activate

Selection.Delete Shift:=xlToLeft

Range("A2:D251").Select

ActiveWorkbook.Worksheets().Sort.SortFields.Clear

ActiveWorkbook.Worksheets().Sort.SortFields.Add Key:=Range("A3:A12") _

, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal

ActiveWorkbook.Worksheets().Sort.SortFields.Add Key:=Range("C3:C12") _

, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal

With ActiveWorkbook.Worksheets().Sort

.SetRange Range("A2:D12")

.Header = xlYes

.MatchCase = False

.Orientation = xlTopToBottom

.SortMethod = xlPinYin

.Apply

End With

Range("A1").Select


' Go to next worksheet

Next I

End Sub
 

Attachments

  • Error Message Pic.jpg
    Error Message Pic.jpg
    39.6 KB · Views: 8
  • Error Message 2.jpg
    Error Message 2.jpg
    35.9 KB · Views: 8
Last edited by a moderator:

Some videos you may like

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

Zot

Well-known Member
Joined
Nov 26, 2020
Messages
1,026
Office Version
  1. 2016
Platform
  1. Windows
Instead of ActiveWorkbook.Worksheets(), try use ActiveSheet
 
Solution

Zot

Well-known Member
Joined
Nov 26, 2020
Messages
1,026
Office Version
  1. 2016
Platform
  1. Windows
That worked! Thank you!
Good to hear that

I think teh worksheet in ActiveWorkbook.Worksheets() is not referring to any specific worksheet. There is not name or index to the worksheet. Since you are not switching workbook (working on same workbook), you can just ignore it and refer straight to Active worksheet (the sheet you are working on)
 

Watch MrExcel Video

Forum statistics

Threads
1,127,803
Messages
5,626,997
Members
416,214
Latest member
boston814

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top