Any Ideas?

[zendog1960]

Good Morning Gurus!

The following is the Summary page. What I would like is to automatically popullate the columns A thru F. If a new sheet is added, by whatever means, The code would look at this list on the summary page and add the necessary sheet name to column A and add the references from that new sheet in the appropriate columns.

How would you code that?

Coin Shooter.xls
	A	B	C	D	E	F
2	Park	Time	Total Coins	Total Value	Total PCV	Total VPH
3	Example Park	2.50	84	$3.56	$0.04	$1.42
4
5
6
7
8
9
Location Summary

Thanks in advanced for any and all help!

 

[zendog1960]

You know goldfish, you hit the nail on the head. It was adding the correct lines to the Location Summary page. However, I have the sheet setup so the first line is on row 3. I have a columns row way down on row 54. The range I have of empty rows is A3:A53. Is there a way to modify code that checks for the first empty line to only look within that range so it doesn't go down to line 55 and start adding them?

Now THAT would be S W E E T!

 

[zendog1960]

never mind. I just re-arranged the sheet such that the totals for the columns are elsewhere.

Now, thinking as a user would, If I delete a sheet that is on the list, the references all become invalid for that sheet. Since I certainly don't want a list full of invalid cells, how can I refresh the list of only the sheets currently in the workbook, with all the same referenced cells, and remove the sheet from the list after it has been deleted?

Awww the plot thickens!

 

[goldfish]

My first attempt at writing the code instead of adding a line to the bottom of the list simply rewrites the whole section (it shouldn't be too much slower). If you want you could go back to that, so every time the code is run it generates a completely new list (since you want to do more than just hide the rows especially if you're going to sum them). Was just running it for the one sheet your work around for the Sheet15 and Sheet16 issue, where sheets that didn't exist were popping up on the list?


Additional: If you substitute 55 for ".rows.count" in your code that will make it check for the first blank row above 55.

Additional Additional: You should probably add a

Sheets("Summary").Range("A3:F55").clearcontents

or something like that to my original code if you intend on it to rewrite the summary page every time. Plus, I'm not sure why you have you totals in row 55, but you could surely code that to be 2 lines below the last Summary entry or something like that.

~Gold Fish

 

[theozz]

???
if cell A3 is 'Example Park' then
need formula ='Example Park'!A1

if cell A3 is 'Sample' then
need formula ='Sample'!A1

as upper result?

of course cell address should change???

 

[zendog1960]

My first attempt at writing the code instead of adding a line to the bottom of the list simply rewrites the whole section (it shouldn't be too much slower). If you want you could go back to that, so every time the code is run it generates a completely new list (since you want to do more than just hide the rows especially if you're going to sum them).

Additional: You should probably add a

Sheets("Summary").Range("A3:F55").clearcontents

~Gold Fish

ok this is working really good. I am really getting excited because not only is this workbook looking really good but I am learning this stuff as we go.

I have two other things I am curious about.

1. When I create the list on the Location Summary page, it also includes the example worksheet and the template worksheet. Is there a way to Exclude these two from the list? I have then hidden, thinking that might work however it did not.

2. If a user decides he wants to delete a sheet (Location), once done the list on the Location Summary page shows the deleted sheet references as broken with the #REF!. How can this be resolved?

Thanks again for all your help.

 

[goldfish]

1. If you want to just do visible sheets you could do

If wSht.Name <> "Location Summary" and wkSht.visible = true Then

or you could do

If wSht.Name <> "Location Summary" and wSht.Name <> "template" and wSht.Name <> "example" Then

2. The next time the macro runs it will be fixed if ya do it right. I take it you would like to have the fix happen faster than that? If you need it to happen right away, you are going to need an event macro. (Unless of course you are willing to coop with 0's instead of #REF in which case you could change all your formulas to

IF(ISERROR('Example Park'!A1),0,'Example Park'!A1)

Otherwise you will have to create a macro that acts whenever a sheet is deleted. Too bad this is one Event that Microsoft forgot to include in excel so Ivan F Moala wrote this code to get around that

Dim shName As String

Dim Avail



Private Sub Workbook_SheetActivate(ByVal Sh As Object)

On Error Resume Next

Avail = Sheets(shName).Range("A1")

If Err Then

MsgBox shName & " has been Deleted ...Put your routine here to run?"

End If

On Error GoTo 0

End Sub



Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)

'Application.DisplayAlerts = False

shName = Sh.Name

'Application.DisplayAlerts = True

End Sub



'This works on the fact that the Workbook_SheetDeactivate event is triggered before

'the SheetActivate event and stores the Sheet name in variable shName.

'The sheetActivate event runs a little routine that

'tries to get a value from the lastsheet, which is

'shName (from the DeactivateEvent), the on Error Resume next is put there so that if the sheet no longer

'exists it generates an error object which is evaluated.

'THIS IS WHERE you can put your routine you want

'to run when a sheet is deleted.

This can be place in your the "ThisWorkbook" Module in the VBA editor.

you should change

MsgBox shName & " has been Deleted ...Put your routine here to run?"

to something more useful, either to rerun the summary macro or to hide any #REF errors on the summary page.

HTH,
~Gold Fish

 

[zendog1960]

viable solution

Excellent. I am not sure why I didn't think of the worksheet activate function... Here is the code I used to simply re-generate the list everytime the user goes to the page"

Private Sub Workbook_SheetActivate(ByVal Sh As Object)

GenSummary

End Sub

I tested it and it works great. I tried the code you posted and it worked too but everytime I changed worksheets the message box appeared which was annoying so I tried this and it worked most excellent.

For those wanting to see the end result, let me know and I will post the code that has been developed with the fantastic help of goldfish.

 

[goldfish]

Well your code does work, but if you get a lot of sheets, that is really going to start to slow down how quickly you can change sheets. I wasn't suggesting using the code in my last post specifically, more something like this:

Dim shName As String 
Dim Avail 

Private Sub Workbook_SheetActivate(ByVal Sh As Object) 
On Error Resume Next 
Avail = Sheets(shName).Range("A1") 
If Err Then 
Call GenSummary
End If 
On Error GoTo 0 
End Sub 

Private Sub Workbook_SheetDeactivate(ByVal Sh As Object) 
shName = Sh.Name 
End Sub

The message box wasn't designed to be left in the code, just a marker to show off what the code can do. And it should not have popped up every time only when a sheet was deleted.

~Gold Fish

 

[zendog1960]

Sweet. That does work better. Thank you so much for your help!