Worksheets(x).Select Error

[aadil_1408]

Hi,

I am trying to select multiple sheets using the following 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

 

[diddi]

it is only possible to select 1 sheet at a time

 

[RoryA]

You can create an array in a loop:

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.

 

[RoryA]

it is only possible to select 1 sheet at a time

Um, no.

 

[MARK858]

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]

it didn't work for me, this is my entire 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]

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

it's in personal workbook

 

[MARK858]

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.

 

[RoryA]

it didn't work for me

We always need more information than that. What happened?

 

[aadil_1408]

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

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