I am creating a simple envelope budgeting sheet. On my first sheet, I
would like to have a list of all the other sheets ('envelopes') and
their balance. Each 'envelope' sheet has its current balance in cell
A7 and I would like to access that as well.

So my first sheet would have something like:

8 | 9
D <name of second sheet| value of A7 on second sheet
E <name of third sheet | value of A7 on third sheet
....

If possible, I would like this to be automatic so when I add another
envelope sheet, its name and A7 value would show up on the first sheet.

Finally, would it be possible to detect a double click on the sheet
name and
then switch to that sheet?

Thanks in advance for any help.

This will add hyperlinks on sheet1 to each of the following sheets, 2, 3, 4,
etc. The hyperlink text will be the sheet name, the cell under it will be
the value from cell A7 on that sheet. The sheet names will begin on sheet1,
range D8 and continue for each column to the right for as many sheets you
have.

Sub test()
Dim i As Long
Dim DataClmn As Long
Dim ShtName As String

DataClmn = 4
For i = 2 To Worksheets.Count
ShtName = Worksheets(i).Name

With Worksheets(1)
.Hyperlinks.Add Anchor:=.Cells(8, DataClmn), _
Address:="", _
SubAddress:=ShtName & "!A7", _
ScreenTip:="", _
TextToDisplay:=ShtName & ""
.Cells(9, DataClmn).Value = Worksheets(i).Range("A7").Value
End With
DataClmn = DataClmn + 1

Next i
End Sub

Mike F
wrote in message
ups.com...
I am creating a simple envelope budgeting sheet. On my first sheet, I
would like to have a list of all the other sheets ('envelopes') and
their balance. Each 'envelope' sheet has its current balance in cell
A7 and I would like to access that as well.

So my first sheet would have something like:

8 | 9
D <name of second sheet| value of A7 on second sheet
E <name of third sheet | value of A7 on third sheet
...

If possible, I would like this to be automatic so when I add another
envelope sheet, its name and A7 value would show up on the first sheet.

Finally, would it be possible to detect a double click on the sheet
name and
then switch to that sheet?

Thanks in advance for any help.

Works great. The only problem I am haing is with sheet names with
spaces or '-' in the name.

Can I use sheet names with spaces?

Thanks.

Yes spaces and hyphens are OK in sheet names. Could you describe the exact
problem you are having?

Mike F
wrote in message
oups.com...
Works great. The only problem I am haing is with sheet names with
spaces or '-' in the name.

Can I use sheet names with spaces?

Thanks.

Also sheet names that are a date like Sep-05 were a problem when displayed
on sheet1. Excel used it's date recognition feature to screw it up. Here is
the modified code that should work with any sheet name.

Dim i As Long
Dim DataClmn As Long
Dim ShtName As String

DataClmn = 4
For i = 2 To Worksheets.Count
ShtName = Worksheets(i).Name

With Worksheets(1)
.Hyperlinks.Add Anchor:=.Cells(8, DataClmn), _
Address:="", _
SubAddress:="'" & ShtName & "'!A7", _
ScreenTip:="", _
TextToDisplay:="'" & ShtName
.Cells(9, DataClmn).Value = Worksheets(i).Range("A7").Value
End With
DataClmn = DataClmn + 1

Next i
End Sub

Mike F


"Mike Fogleman" wrote in message
...
Yes spaces and hyphens are OK in sheet names. Could you describe the exact
problem you are having?

Mike F
wrote in message
oups.com...
Works great. The only problem I am haing is with sheet names with
spaces or '-' in the name.

Can I use sheet names with spaces?

Thanks.

Thanks!

Cool - it is working.

I changed the code so that it operates from sheet 2 to the second from
last. The last sheet is now the base for copying new sheets.

Is it possible to create a macro which will take the name in the cell
and copy the last sheet to one with the cell name? That will allow me
to add envelope sheets as necessary.

Thanks again.

Yes it can be done. I will need some details on what you want done and where
things are located and where you want it to end up. It sounds like your last
sheet is a template for adding new sheets. You want all new sheets to be
placed right before the last sheet (Template) or right after the last
'Envelope' sheet. You want the new sheet name to be the value from a cell
where? On the Template sheet? A copy of the Template sheet would then
contain the same cell value on it as the new sheet's name. Do you want that
to stay or be removed? The basics for copying a new sheet in this manner
is:

Sub AddSheet()
Dim i As Long

i = Worksheets.Count

Sheets(i).Copy Befo=Sheets(i)
ActiveSheet.Name = Range("B3").Value

End Sub

This shows the New sheet name to be in Cell B3, change as needed.

Mike F
wrote in message
ups.com...
Cool - it is working.

I changed the code so that it operates from sheet 2 to the second from
last. The last sheet is now the base for copying new sheets.

Is it possible to create a macro which will take the name in the cell
and copy the last sheet to one with the cell name? That will allow me
to add envelope sheets as necessary.

Thanks again.

You are correct that I want to copy the last sheet (which is indeed the
template) after the last envelope sheet and before the template which
is what, I think your
Sheets(i).Copy Befo=Sheets(i) will do.

On my main sheet, I would like to have an entry area with a button
after it.

So H5 would have the entry area and i5 would have the button which
would invoke the macro. This macro would then add the sheet, and
reinvoke the earlier macro which will polulate the envelope list.

Thanks

Which sheet is H5 & the button on? I am assuming now that it will be on the
First sheet where the sheet list and values were put with the first macro I
wrote. If it is on the Template then Each New sheet will also have a button
and it's sheet name in H5. Secondly, if I tie the 2 macros together, the New
sheet added will not have a value in A7 yet, as far as I know. The macro
will add the new sheet name to Sheet1 but the cell under it will be blank.
It will not be updated until the next new sheet is added. Is this what you
intend?

Mike F
wrote in message
oups.com...
You are correct that I want to copy the last sheet (which is indeed the
template) after the last envelope sheet and before the template which
is what, I think your
Sheets(i).Copy Befo=Sheets(i) will do.

On my main sheet, I would like to have an entry area with a button
after it.

So H5 would have the entry area and i5 would have the button which
would invoke the macro. This macro would then add the sheet, and
reinvoke the earlier macro which will polulate the envelope list.

Thanks

Mike,

Here is the code I am using:

Sub FillBalanceSheets()
Dim i As Long
Dim DataClmn As Long
Dim ShtName As String

DataRow = 8
For i = 2 To Worksheets.Count - 1
ShtName = Worksheets(i).Name

With Worksheets(1)
.Hyperlinks.Add Anchor:=.Cells(DataRow, 8), _
Address:="", _
SubAddress:="'" & ShtName & "'!i1", _
ScreenTip:="", _
TextToDisplay:="'" & ShtName
.Cells(DataRow, 9).Value = Worksheets(i).Range("i1").Value
End With
DataRow = DataRow + 1

Next i
End Sub


Sub AddSheet()
Dim i As Long

i = Worksheets.Count

Sheets(i).Copy Befo=Sheets(i)
ActiveSheet.Name = Range("h6").Value
FillBalanceSheets

End Sub

It is the code you sent with a few changes. Everything works and I
have a button on the first page linked to addsheet.

When I run this, or press the button, it jumps to the newly created
page. Can I make it stay on the original page?
Also, I would like to clear the columns h8:i65384 (what ever the max
is) before I invoke the fillBalancesheet macro. How can I clear cells?

Thanks again for all your help.

Sub AddSheet()
Dim i As Long
Dim LastRow as Long

i = Worksheets.Count
Application.ScreenUpdating = False

Sheets(i).Copy Befo=Sheets(i)
ActiveSheet.Name = Range("h6").Value
LastRow = Cells(Rows.Count, "I").End(xlUp).Row
Range("H8:I" & LastRow).Clear
WorkSheets(1).Activate

FillBalanceSheets

Application.ScreenUpdating = True
End Sub

Mike F

wrote in message
oups.com...
Mike,

Here is the code I am using:

Sub FillBalanceSheets()
Dim i As Long
Dim DataClmn As Long
Dim ShtName As String

DataRow = 8
For i = 2 To Worksheets.Count - 1
ShtName = Worksheets(i).Name

With Worksheets(1)
.Hyperlinks.Add Anchor:=.Cells(DataRow, 8), _
Address:="", _
SubAddress:="'" & ShtName & "'!i1", _
ScreenTip:="", _
TextToDisplay:="'" & ShtName
.Cells(DataRow, 9).Value = Worksheets(i).Range("i1").Value
End With
DataRow = DataRow + 1

Next i
End Sub


Sub AddSheet()
Dim i As Long

i = Worksheets.Count

Sheets(i).Copy Befo=Sheets(i)
ActiveSheet.Name = Range("h6").Value
FillBalanceSheets

End Sub

It is the code you sent with a few changes. Everything works and I
have a button on the first page linked to addsheet.

When I run this, or press the button, it jumps to the newly created
page. Can I make it stay on the original page?
Also, I would like to clear the columns h8:i65384 (what ever the max
is) before I invoke the fillBalancesheet macro. How can I clear cells?

Thanks again for all your help.

Perfect!

How can I remove the contents of the H6 cell but not the formating?

I currently have a button assigned to call addsheet. However, I am not
able to type text in the cell and then press the button. I first have
to choose something outside of the cell. How can I make it so I type
in the cell and press enter which will invoke the macro?

Thanks.

I was able to find part of the solution:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Range("h6"), Target) Is Nothing Then
AddSheet
End If
End Sub

I still need to be able to remove the text but not the formating from
h6.

Sub AddSheet()
Dim i As Long
Dim LastRow as Long

i = Worksheets.Count
Application.ScreenUpdating = False

Sheets(i).Copy Befo=Sheets(i)
ActiveSheet.Name = Range("h6").Value
LastRow = Cells(Rows.Count, "I").End(xlUp).Row
Range("H8:I" & LastRow).Clear
Range("H6").ClearContents
WorkSheets(1).Activate

FillBalanceSheets

Application.ScreenUpdating = True
End Sub

However, I am not
able to type text in the cell and then press the button.
You are still in cell edit mode. Code cannot run until you have completed
the cell data entry by pressing Enter or clicking another cell. You really
don't want to invoke a macro on this sheet by pressing Enter. The code would
be in a WorkSheet change Event, and would run every time you changed cells
anywhere on the sheet. A big drain on memory and performance. Stick with the
button.

wrote in message
ups.com...
Perfect!

How can I remove the contents of the H6 cell but not the formating?

I currently have a button assigned to call addsheet. However, I am not
able to type text in the cell and then press the button. I first have
to choose something outside of the cell. How can I make it so I type
in the cell and press enter which will invoke the macro?

Thanks.