Selecting. Multiple Worksheets as per list.

venumkd

New Member
Joined
Feb 4, 2020
Messages
13
Office Version
2010
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

Well-known Member
Joined
Dec 3, 2018
Messages
9,707
Office Version
2007
Platform
Windows
Try this

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

New Member
Joined
Feb 4, 2020
Messages
13
Office Version
2010
Showing Run-time error “9”

Subscript out of range
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
9,707
Office Version
2007
Platform
Windows
you put sheet names in column A that don't exist in the book. perform a small test with 3 existing sheets
 

venumkd

New Member
Joined
Feb 4, 2020
Messages
13
Office Version
2010
Still not working
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
9,707
Office Version
2007
Platform
Windows
Do you have blank cells with blank spaces in column A?
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
9,707
Office Version
2007
Platform
Windows
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.

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

New Member
Joined
Feb 4, 2020
Messages
13
Office Version
2010
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

Well-known Member
Joined
Dec 3, 2018
Messages
9,707
Office Version
2007
Platform
Windows
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.

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

New Member
Joined
Feb 4, 2020
Messages
13
Office Version
2010
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
 

Forum statistics

Threads
1,085,786
Messages
5,385,871
Members
401,975
Latest member
OnPoint

Some videos you may like

This Week's Hot Topics

Top