Create a list of sheets in a file
I receive an excel file each month.
It has multiple sheets.
Each month when i receive the file it may have additional sheets I am creating a summary sheet.
Is there a way, in the summary sheet to list the sheets.
Then if i have the list, when creating
formulas in the summary file, can i refernce cells in the sheets using ther listed sheet names?
Solutions to the Problem Create a list of sheets in a file
Here you go...
If you create a Personal.xlsb file to contain this macro, it will always be available to
use on any workbook you have open. If you already have a Personal.xlsb workbook,
just paste the below VBA code into a general module in that workbook.
These instructions detail how to create a Personal.xlsb:
• Create a new workbook
• ALT+F11....to open the VBA editor
• Select your new workbook from the "Project-VBA Project" window.
• Copy the below VBA code and paste it into that module
Dim wks As Worksheet
Dim rngStart As Range
Dim intCtr As Integer
If MsgBox(Title:="PLEASE CONFIRM", _
Prompt:="Ok to create the worksheet list beginning with the current cell?", _
Buttons:=vbQuestion + vbYesNo) = vbNo Then
Set rngStart = ActiveCell
intCtr = 0
For Each wks In ActiveWorkbook.Worksheets
rngStart.Offset(RowOffset:=intCtr).Value = wks.Name
intCtr = intCtr + 1
• ALT+F11...to return to the workbook
...Save in: C:\Program Files\Microsoft Office\Office12\XLSTART
...Save as type: Excel Binary Workbook (*.xlsb)
...File name: PERSONAL.xlsb
Now, whenever you open Excel 2007, the macros in the Personal.xlsb file will be available.
Note: You'll want to hide that workbook so it won't clutter up the list of open workbooks:
• Select the Personal.xlsb workbook
To use that macro:
• ALT+F8...to see the list of available macros
• Select: ListWkshtsOfWkbk
• Click: Run
You'll be prompted for permission to start the list in the active cell.
After clicking Yes...the list will be created.
Is that something you can work with?
Microsoft MVP - Excel (2006 - 2010)
If any post answers your question, please mark it as the Answer
(That way it won't keep showing as an open item.)
Open Action Center by clicking the Start button Picture of the Start button, clicking Control Panel, and then, under System and Security, clicking Review your computer's status.
Make sure your computer has at least the minimum amount of random access memory (RAM) required to run the program that is displaying the error. Look for the program requirements online or in the packaging information.
Another Safe way to Repair the Problem: Create a list of sheets in a file:
How to Fix Create a list of sheets in a file with SmartPCFixer?
1. You can Download SmartPCFixer here. Install it on your computer. When you open it, it will perform a scan.
2. After the scan is finished, you can see the errors and problems which need to be repaired.
3. The Repair part is finished, the speed of your computer will be much higher than before and the errors have been fixed. You can also use other functions in SmartPCFixer. Like dll downloading, windows updating and print spooler error repair.
Related: How Can You Update & Download NVidia 6100, 6800 GS/XT Display Driver v.260.89 WHQL,Where to Download NVidia GeForce 8800 GT WHQL-certified driver v.196.21,How to Update & Download NVidia GeForce Go 7400 Display Driver v.260.19.12,[Solved] Download NVidia GeForce GTX 460M Driver v.331.82,Download NVidia Quadro Plex Model IV VGA Driver v.304.43 Certified,Way to Download RealTek RTL8100C(L) Driver v.5.01,Way to Download RealTek RTL8100E Drivers v.694,Way to Update & Download RealTek RTL8101L Auto Installation Program v.6.110 driver,Method to Update & Download RealTek RTL8111G PXE and RPL ROM code v.2.58 driver,How to Update & Download RealTek RTL8411B(N) Driver v.10.003,Best Way to Update & Download ASUS A53SV nVidia Graphics Driver v.22.214.171.12486,Method to Herunterladen ASUS K75VJ Intel Rapid Storage Technology Treiber v.126.96.36.1990,How Can You Update & Download ASUS CG8580 Intel Chipset Driver v.188.8.131.529,Method to Update & Download ASUS K41VD Intel INF Update Driver v.184.108.40.2065,Way to Update & Download ASUS Pro70T NB Probe v.3.0.0026 driver
Read More: Fast Solution to Error: Could not Connect To the Internet (wired connection),Troubleshoot:Copying from external hard drive to c drive,Tech Support: copy formulae in cells with condition,Troubleshooting:coupon printer doesnt work for red plum Error,Troubleshooting:Cubase 6 Elements App Crash Error,Closing Internet Explorer causes error,Compare 2 Tables and Calculate the PROFIT Faster,Computer goes to Windows Boot Manager after Start,Connect to one of my network computer remotely when don't allow exception box Marked,computer locking up randomly