Listing Sheets

[Jeff Klein]

I have approx 150 sheets that have renamed for each client name. I want to
create another sheet that lists the client names with cell M44. M44 is a
cell that shows "balance due". I need a list of each client and the balance
due. Any help is appreciated, thank you.

 

[Tom Ogilvy]

Dim sh as worksheet, sh1 as Worksheet
Dim i as Long
worksheets.Add
set sh = Activesheet
i = 0
for each sh1 in Worksheets
if sh.Name <> sh1.name then
i = i + 1
cells(i,1).Value = sh.Name
cells(i,2).Value = sh1.Range("M44").Value
end if
Next

 

[Jeff Klein]

Tom, Thanks for the reply. This works great....although I have placed this
code in the workbook on open event so will get a new sheete every time I
open this woork book. Is there a better way to process this code as to
creat the sheet only when I neet it? Again, thanks for the reply. Jeff

 

[Tom Ogilvy]

Don't add a sheet

Dim sh as worksheet, sh1 as Worksheet
Dim i as Long
'worksheets.Add
set sh = Worksheets("Summary")
i = 0
.. . .