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

 

[aadil_1408]

We always need more information than that. What happened?

ya sorry Rory, I was testing the code suggested by mark. The error which occurred is Runtime error '404' Select method of worksheet class failed.

 

[MARK858]

No the code suggested by me was for the code in the first post being run from the Personal workbook. I haven't suggested any code for the code that you posted being run from a command button (which should not be in the Personal workbook).

 

[aadil_1408]

No the code suggested by me was for the code in the first post being run from the Personal workbook. I haven't suggested any code for the code that you posted being run from a command button.

Okay, i am sorry about that, i guessed adding the part of my code containing the issue would be enough. Clearly i was wrong. I will try to post more accurate questions from the next time onward. would you want me to close this question and add another question appropriately?

 

[MARK858]

No, you don't need to start another thread but the command button code needs to be the same workbook as the command button, is it?

 

[aadil_1408]

No, you don't need to start another thread but the command button code needs to be the same workbook as the command button, is it?

yes it does. What i am trying to accomplish is that after clicking the command button, the current workbook is saved with a new name and then from this new file I need sheets to be transferred to another file

 

[MARK858]

Just ran your command button code with Rory's amendment just up to the sheets being selected and it works fine for me (Edit: as does the code in the first post if I place it in the code btw) and selects the sheets (please note the command button name is different in the code below to match my command button).

Private Sub CommandButton1_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]

Thank you for your patience Mark,

For me the same code till selecting the sheets throws the same error as before.

This error does not occur when i tried selecting only one sheet

 

[aadil_1408]

This code when I select only one sheet works perfectly, hence i guessed the error must be coming from that part of the 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"
    
Sheets(3).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

 

[MARK858]

Does it work when you comment out the same lines that I did, i.e. the path and the saveas lines?
Do you have any protection either on the Workbook or the Worksheets?

 

[MARK858]

Silly question, the sheets are all visible aren't they?