About Us

[email protected]

Steve,

Thank you very much! It works.
Great!!

Maurits

wrote:
Hi Maurits,

Yes, you paste it into a module in the VBA editor. The problem is that
the formatting gets screwed up somewhat when you copy it from your
browser window into the code module. The red lines probably appear when
there is a line break where there shouldn't be one. E.g., the second
Dim statement should be changed from

...wsMaster As Excel.Worksheet <line break here
, ws As Excel.Worksheet

to

...wsMaster As Excel.Worksheet, ws As Excel.Worksheet

Alternatively, you can go to
http://www.swiss-ins.com/stk/listSheets_macro.txt where I uploaded a
text file that has the correct formatting. You may paste this into your
code module, and all red lines should disappear. The macro can be
started by hitting F5 in the VBA editor or Alt-F8 in Excel (which lists
the available macros).

Regards,
Steve
wrote:

Okay thank you very much.

I am going to sound ike a complete idiot. But where do I start to input
this data. Is that in VBA? If I past your command in VBA I get a number
of red lettering and some in green.

How do I get this program to run?

Thank you,

Maurits

wrote:
You certainly can solve this programmatically:

Sub listSheets()
Dim colSheets As String, colRem As String, clRem As String,
masterSheet As String
Dim i As Integer, wb As Excel.Workbook, wsMaster As Excel.Worksheet,
ws As Excel.Worksheet

masterSheet = "MasterSheet" 'replace MasterSheet with whatever name
your master sheet has
colSheets = "A" 'the column that contains the sheet names
colRem = "B" 'the column that contains the remarks
clRem = "A1" 'the cell that contains the remarks in each sheet

Set wb = ThisWorkbook 'Your Workbook
Set wsMaster = wb.Sheets(masterSheet)

For i = 2 To wb.Sheets.Count
Set ws = wb.Sheets(i)
With wsMaster
.Range(colSheets & CStr(i - 1)).Value = ws.Name
.Range(colRem & CStr(i - 1)).Value = ws.Range(clRem).Value
End With
Set ws = Nothing
Next i

Set ws = Nothing
Set wb = Nothing
Set wsMaster = Nothing
End Sub

schrieb:

Hi Guys,

I made a database for personal information. I gave every person one
sheet with a fixed format. I have about 200 sheets now all named
according to the individual. Now I would like to have one master sheet,
the first sheet, in the excel workbook that list all the names of the
other sheets in one column and in the next column a cell that refers to
one cell in each sheet with a remark in there. This way I have one
overview from which sheets I need to change something and which ones
are ok.

I just don't have a clue how to do this. Is there an excel function or
macro function?

Would really appreciate the help.

Regards,

Maurits

Thread Tools	Search this Thread
Show Printable Version	Search this Thread: Advanced Search
Display Modes
Switch to Linear Mode Switch to Hybrid Mode Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Updating Individual Sheets from a master	[email protected]	Excel Worksheet Functions	1	June 8th 07 09:39 PM
Help: reference info from master spreadsheet to individual forms	Tia	Excel Worksheet Functions	1	March 6th 07 09:41 PM
Moving primary data from master sheet to individual sheets w/in wo	willie091028	Excel Discussion (Misc queries)	1	January 13th 07 04:33 AM
Getting info from individual sheets into master sheet	[email protected]	Excel Worksheet Functions	4	November 21st 06 10:05 AM
Getting info from individual sheets into master sheet	[email protected]	Excel Discussion (Misc queries)	1	November 17th 06 05:24 PM

Menu

About Us