Selecting. Multiple Worksheets as per list.

[venumkd]

Sirs

Kindly provide me a suitable method or VBA code for selecting multiple worksheets / tabs in a workbook. This is for a data entry work. Details are below.

The workbook is containing around 12000 random sheets out of which only 1500 worksheets are to be selected and tab coloured and they are neither adjacent nor in a particular order (Also they are to be renamed and that I shall do manually) . But the name / list of sheets/tabs to be selected shall be mentioned in the first sheet in Column A. Those sheets are to be selected. Tab coloring also I shall do according to the required color. Only selection is required now.

Thanking you, yours faithfully

VENU

 

[DanteAmor]

Try this

Sub SelectSheet()
  Dim a
  a = Sheets(1).Range("A1", Sheets(1).Range("A" & Rows.Count).End(xlUp)).Value2
  Sheets(Application.Transpose(a)).Select
End Sub

 

[venumkd]

Showing Run-time error “9”

Subscript out of range

 

[DanteAmor]

you put sheet names in column A that don't exist in the book. perform a small test with 3 existing sheets

 

[venumkd]

Still not working

 

[DanteAmor]

Do you have blank cells with blank spaces in column A?

 

[DanteAmor]

I can't see what sheet names you have in column A or the names of your sheets.
Then run the following macro to check if the data in column A exists in the sheets of your book.

Note: The names of the sheets should be placed on the first sheet of your book in column A, beginning in row 1.

Sub SelectSheet()
  Dim a, i As Long, status As Boolean
  a = Sheets(1).Range("A1", Sheets(1).Range("A" & Rows.Count).End(xlUp)).Value2
  For i = 1 To UBound(a)
    If Evaluate("ISREF('" & a(i, 1) & "'!A1)") = False Then
      status = True
      MsgBox "The sheet name " & a(i, 1) & " of the row: " & i & " does not exists"
    End If
  Next
  If status = True Then
    MsgBox "You have sheets that don't exist. No sheets will be selected."
    
  Else
    Sheets(Application.Transpose(a)).Select
  End If
End Sub

 

[venumkd]

Sirs

Thanks a lot.

But it is working only in a new workbook. Not working in an existing workbook.

Is it possible to make it work in an existing workbook?

Pl. try

Thanks & Regards

VENU

 

[DanteAmor]

Of course it works in any book.
you should only put the names of the sheets on the first sheet of your book.
Check that in the book, you don't have hidden or very hidden sheets, maybe some of those hidden sheets is the first sheet.

And something important, if the sheet is hidden, it cannot be selected.

If you know the name of the sheet where you are going to put the name of the sheets, then try the following, change "Sheet1" to the name of that sheet.

Sub SelectSheet()
  Dim a, i As Long, status As Boolean
  a = Sheets("Sheet1").Range("A1", Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp)).Value2
  For i = 1 To UBound(a)
    If Evaluate("ISREF('" & a(i, 1) & "'!A1)") = False Then
      status = True
      MsgBox "The sheet name " & a(i, 1) & " of the row: " & i & " does not exists"
    End If
  Next
  If status = True Then
    MsgBox "You have sheets that don't exist. No sheets will be selected."
   
  Else
    Sheets(Application.Transpose(a)).Select
  End If
End Sub

 

[venumkd]

Sirs

With this code it is working. Thanks a lot.

Is it possible to rename these selected sheets as per the appropriate cell values which shall be mentioned in column B?

Thanks & Regards

VENU