Worksheets(x).Select Error

aadil_1408

New Member
Joined
Jan 3, 2021
Messages
16
Office Version
  1. 2019
Platform
  1. Windows
Hi,

I am trying to select multiple sheets using the following code.

VBA Code:
    Dim x As Integer
    ThisWorkbook.Worksheets(3).Select
    For x = 3 To ThisWorkbook.Worksheets.count
        Worksheets(x).Select (False)
    Next x

Although it is a common code it does not work with the later versions of excel as other people online have encountered similar issues. Is there another way to selects all sheets except first two?

Thanks and Regards,
Aadil Khan
 

Some videos you may like

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

diddi

Well-known Member
Joined
May 20, 2004
Messages
3,263
Office Version
  1. 2010
Platform
  1. Windows
it is only possible to select 1 sheet at a time
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,415
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
You can create an array in a loop:

Code:
Dim v()
ReDim v(1 To Sheets.Count - 2)
Dim n As Long
For n = LBound(v) To UBound(v)
    v(n) = n + 2
Next
Sheets(v).Select

for example.
 
Solution

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,960
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile

ADVERTISEMENT

Although it is a common code it does not work with the later versions of excel as other people online have encountered similar issues. Is there another way to selects all sheets except first two?
Code works fine for me in 365. Where have you placed the code? in the Personal workbook?
 

aadil_1408

New Member
Joined
Jan 3, 2021
Messages
16
Office Version
  1. 2019
Platform
  1. Windows
it didn't work for me, this is my entire code
VBA Code:
Private Sub CommandButton6_Click()

    Dim path As String
    Dim nm1 As String
    Dim nm2 As String
    Dim nm3 As String
    Dim nm4 As String
    Dim nm5 As String
    
    Sheets("Sheet1").Select
    
    nm1 = Range("B10")
    nm2 = Range("B11")
    nm3 = Range("B12")
    path = "C:\Users\Khan_A.WLUCY\Desktop\"
    ActiveWorkbook.SaveAs Filename:=path & "PRS-" & nm1 & " Week-" & nm2 & " " & nm3 & ".xlsm"
    

    Dim v()
    ReDim v(1 To Sheets.count - 2)
Dim n As Long
For n = LBound(v) To UBound(v)
    v(n) = n + 2
Next
Sheets(v).Select
    
    Application.ScreenUpdating = False
    Set closedBook = Workbooks.Open("C:\Users\Khan_A.WLUCY\Desktop\Projects\Excel Automation\PRS BULD - Copy")
    ActiveWindow.SelectedSheets.Copy After:=closedBook.Sheets(2)
    closedBook.Close SaveChanges:=True
 

End Sub
 

aadil_1408

New Member
Joined
Jan 3, 2021
Messages
16
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

Code works fine for me in 365. Where have you placed the code? in the Personal workbook?
it's in personal workbook
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,960
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
Change ThisWorkbook to ActiveWorkbook (btw the code in the first post isn't in the last code that you posted) if it is the code in the first post that you on about.
 

aadil_1408

New Member
Joined
Jan 3, 2021
Messages
16
Office Version
  1. 2019
Platform
  1. Windows
Change ThisWorkbook to ActiveWorkbook (btw the code in the first post isn't in the last code that you posted) if it is the code in the first post that you on about.
I had changed the code according to RoryA's suggestion and posted the changed code.
Changing From ThisWorkbook to ActiveWorkbook didn't work. The following error has occurred Runtime error '404' Select method of worksheet class failed.

The following is the changed code as suggested by you

VBA Code:
Private Sub CommandButton6_Click()

    Dim path As String
    Dim nm1 As String
    Dim nm2 As String
    Dim nm3 As String
    Dim nm4 As String
    Dim nm5 As String
    
    Sheets("Sheet1").Select
    
    nm1 = Range("B10")
    nm2 = Range("B11")
    nm3 = Range("B12")
    path = "C:\Users\Khan_A.WLUCY\Desktop\"
    ActiveWorkbook.SaveAs Filename:=path & "PRS-" & nm1 & " Week-" & nm2 & " " & nm3 & ".xlsm"
    
    Dim x As Integer
    ActiveWorkbook.Worksheets(3).Select
    For x = 3 To ActiveWorkbook.Worksheets.count
        Worksheets(x).Select (False)
    Next x
    Application.ScreenUpdating = False
    Set closedBook = Workbooks.Open("C:\Users\Khan_A.WLUCY\Desktop\Projects\Excel Automation\PRS BULD - Copy")
    ActiveWindow.SelectedSheets.Copy After:=closedBook.Sheets(2)
    closedBook.Close SaveChanges:=True
 

End Sub

Thanks a lot for your help

1614850546956.png
 

Watch MrExcel Video

Forum statistics

Threads
1,127,009
Messages
5,622,149
Members
415,881
Latest member
tasic89

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