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
 

aadil_1408

New Member
Joined
Jan 3, 2021
Messages
16
Office Version
  1. 2019
Platform
  1. Windows
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.
1614850764383.png
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
14,049
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
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

New Member
Joined
Jan 3, 2021
Messages
16
Office Version
  1. 2019
Platform
  1. Windows
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

MrExcel MVP
Joined
Nov 12, 2010
Messages
14,049
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
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

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

ADVERTISEMENT

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

MrExcel MVP
Joined
Nov 12, 2010
Messages
14,049
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
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).

VBA Code:
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
 
Last edited:

aadil_1408

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

ADVERTISEMENT

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
1614852989994.png
 

aadil_1408

New Member
Joined
Jan 3, 2021
Messages
16
Office Version
  1. 2019
Platform
  1. Windows
This code when I select only one sheet works perfectly, hence i guessed the error must be coming from that part of the 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"
    
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

MrExcel MVP
Joined
Nov 12, 2010
Messages
14,049
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
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

MrExcel MVP
Joined
Nov 12, 2010
Messages
14,049
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
Silly question, the sheets are all visible aren't they?
 

Watch MrExcel Video

Forum statistics

Threads
1,129,566
Messages
5,637,085
Members
416,956
Latest member
mitzhaki

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