ThisWorkbook.Sheets(1).Select (False) Not Working

Question

I have had a piece of code in operation for over 3 years. Suddenly on July 28th, 2016, it stopped working.

It is very simple and I hope it is an easy solve (or maybe a Microsoft update broke it)

ThisWorkbook.Sheets(1).Select
ThisWorkbook.Sheets(2).Select (False) ' like holding ctrl

This would always selects Sheet #1 AND Sheet #2. Now it seems that the "(False)" doesn't work and it will only select Sheet #1. I have tried this on 5 different computers (all Excel 2013) Please let me know what is going on.

Thanks! -Mike

Edit: This also doesn't work anymore. Like Jordan said in the comments, it just does not execute.

y = 9
ThisWorkbook.Sheets(1).Select

For y = 2 To x

       ThisWorkbook.Sheets(y).Select (False) ' like holding ctrl

Next y

edit2: Since there doesn't seem to be a definitive answer I will ask if somebody can help me with a workaround:

ThisWorkbook.Sheets(Array(1 to x)).ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
    FolderName & "\" & QuoteFilename, Quality:=xlQualityStandard, IncludeDocProperties:=True, _
     IgnorePrintAreas:=False, OpenAfterPublish:=False

Obviously this does not work, but it should get my point across.

SOLUTION:

Thanks to Ralph, I took some excerpts and created this:

Private Sub Tester()
x = 5
ReDim SheetstoSelect(1 To x) As String

For y = 1 To x
    SheetstoSelect(y) = ThisWorkbook.Sheets(y).Name
Next y
ThisWorkbook.Sheets(SheetstoSelect).Select

End Sub

This selects the actual Sheet# from 1-5 and allows defining sheets to select by their actual sheet order.

Still don't know the root of the initial issue, but workarounds are just as good.

Does it work without the brackets around `False`? Something must have changed? you had an Office update, OS update, do you no longer have two sheets? — Gary Evans, Aug 02 '16 at 15:12
Are you getting an error message? have you tried using the Array("Sheet1","Sheet2") to test? — Nathan_Sav, Aug 02 '16 at 15:14
I'd guess they broke something in an update as @GaryEvans mentioned. — Rory, Aug 02 '16 at 15:15
Just tried the code myself, it basically just selects `Sheet(1)` and does nothing with the second statement. — Jordan, Aug 02 '16 at 15:17
@Jordan What is your Excel version? It worked for me on 14 (Excel 2010). — Gary Evans, Aug 02 '16 at 15:20
Just FYI - It's ***highly*** recommended to [avoid using `.Select`](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros) — BruceWayne, Aug 02 '16 at 15:23
Thanks @Jordan. This is a version issue I just tried on Version 15 (Excel 2013) and it failed too. — Gary Evans, Aug 02 '16 at 15:37
I have 18 sheets, no brackets gives the same issue. As I stated in the post, maybe Microsoft release an update... does anybody know of a recent Office update? No error messages...all Excel 2013 — mxc1090, Aug 02 '16 at 15:44

Ralph · Accepted Answer · 2016-08-02T17:51:58.440

The following lines of code will select all sheets in the workbook the macro is called from:

Option Explicit

Public Sub SelectAllSheetsInThisFile()

Dim x As Long
Dim SheetstoSelect() As String

ReDim SheetstoSelect(1 To ThisWorkbook.Worksheets.Count)

For x = 1 To ThisWorkbook.Worksheets.Count
    SheetstoSelect(x) = ThisWorkbook.Worksheets(x).Name
Next x
ThisWorkbook.Worksheets(SheetstoSelect).Select

End Sub

The following sub will just select the two sheets you asked for in your original post:

Option Explicit

Public Sub SelectYourSheets()

Dim SheetstoSelect(1 To 2) As String
SheetstoSelect(1) = ThisWorkbook.Worksheets(1).Name
SheetstoSelect(2) = ThisWorkbook.Worksheets(2).Name
ThisWorkbook.Worksheets(SheetstoSelect).Select

End Sub

If you prefer to have it all in one line then you can also use split to create an array on the fly like this:

ThisWorkbook.Worksheets(Split("Sheet1/Sheet3", "/")).Select

This line of code will select two sheets with the names Sheet1 and Sheet3. I chose the delimiter / because this character cannot be used in a sheet's name.

_{Just on a side note: I agree with @BruceWayne. You should try to avoid using select altogether (if possible).}

Thanks! Check the last edit on my original post for the solution. — mxc1090, Aug 02 '16 at 18:27
Not sure why you edited your post to include the answer when the answer is already here. It seems redundant and weird (when someone new reads your post wondering): why would someone with an answer post a question?! So, I'd suggest that (next time) you don't edit your initial post to include the posted answers. At least 99.9% of all other posts on this site follow this format. — Ralph, Aug 02 '16 at 18:32
I guess because it was a variation of what you had, not the exact thing. My code uses "Sheets" not "Worksheets" which works differently. I'm also used to other sites where the answer gets buried so the OP always puts the answer in the main post. I will keep that in mind for the future though...thanks Ralph! — mxc1090, Aug 03 '16 at 12:16

score 1 · Answer 2 · edited Oct 10 '17 at 07:41

I had VBA that was working perfectly until the first week of August, then my PDFs only had the first page. I was using a similar method as you - where I'd select many worksheets. I did a work around using an array. My code was within a form, but I'll post here for reference.

Private Sub CommandButton2_Click()

Dim PrintArray() As Variant

'I used a form to select with checkboxes which worksheets to print, so this code would go inside the form linked to a command button

ReDim Preserve PrintArray(1 To 1)
PrintArray(1) = "Sheet 1 Name"
   j = 1

If Sheet2.Value = True Then  'I used a checkbox to select which worksheets to print, but you could use any conditional statement here
    j = j + 1
    ReDim Preserve PrintArray(1 To j)
    PrintArray(j) = "Sheet 2 Name"
End If

If Sheet3.Value = True Then 'I used a checkbox to select which worksheets to print, but you could use any conditional statement here
    j = j + 1
    ReDim Preserve PrintArray(1 To j)
    PrintArray(j) = "Sheet 3 Name"
End If

'You could add as many pages and conditions as you need....

Unload Me 'because I was using a form

Sheets(PrintArray).Select


'Creates the PDF file name
FileNameforSave = "Name of New File" & ".pdf"

'Save file as a PDF
ActiveSheet.ExportAsFixedFormat xlTypePDF, Filename:= _
        FileNameforSave, _
        Quality:=xlQualityStandard, IncludeDocProperties:= _
        True, IgnorePrintAreas:=False, OpenAfterPublish:=True
End Sub

score 0 · Answer 3 · answered Mar 22 '17 at 15:50

I had the same issue today. Probably delayed because of the update-schedule of my company; likely still the same update. I found your thread and then just before implementing your workaround I found a much simpler one:

ThisWorkbook.Sheets(1).Select
ThisWorkbook.Sheets(2).Select (False) ' like holding ctrl
ThisWorkbook.Sheets(3).Select (False)

does not work anymore, but

ThisWorkbook.Sheets(1).Select
ThisWorkbook.Sheets(2).Select (False) ' like holding ctrl
ThisWorkbook.Sheets(3).Select (False) 
ThisWorkbook.Sheets(2).Select (False) ' line 2 again; essential sacrifice for the vba-gods.

does.

ThisWorkbook.Sheets(1).Select (False) Not Working

3 Answers3

Linked