Search secondary workbooks for specific tabs in an ".xlsx" file

atf32

Board Regular
Joined
Apr 13, 2011
Messages
157
I normally use a basic code that looks into a secondary workbook and scrolls through all that tabs for me (example scroll below). However, what I just realized was that I'm always running this against other macro enabled files. And earlier when I tried it against an ".xlsx" file, it would not go past the start for the for loop, even though it got in the file fine.

Can someone suggest another method (code) to use to list out the tabs of a secondary
".xlsx"
file?:confused:

Example scroll I am only successfull with marco enabled files:

For Each sh In Worksheet
sh.name
if sh.name="Sheet1" then
[do this]
else
[do that]
endif
Next sh​
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
It shouldn't matter whether the workbook is an .xlsm or .xlsx file. As it stands, the code loops through each worksheet within the active workbook (although, it should be Worksheets with an "s", not Worksheet). So you'll need to make sure that the target workbook is active when you run this code. Alternatively, you can avoid having to make sure that your target workbook is the active workbook by assigning it to a variable, and do it this way...

Code:
    Dim wb As Workbook
    Dim ws As Worksheet
    
    Set wb = Workbooks("Book2.xlsx")
    
    For Each ws In wb.Worksheets
        'etc
        '
        '
    Next ws
 
Last edited:
Upvote 0
This worked out great. Thank you for the help and the quick reply. Really appreciate it.
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,113,998
Members
448,539
Latest member
alex78

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
Back
Top