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

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

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

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

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