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: 13
  • Error Message 2.jpg
    Error Message 2.jpg
    35.9 KB · Views: 12
Last edited by a moderator:

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
Instead of ActiveWorkbook.Worksheets(), try use ActiveSheet
 
Upvote 0
Solution
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)
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,034
Members
448,543
Latest member
MartinLarkin

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
Back
Top