Macro to create new sheet and rename +1

[sassriverrat]

I currently have a macro that creates and formats a sheet called "Noon" I'd like a macro that can create a new sheet each time (and I will add my formatting code to it) the cmdbutton is pushed and will rename each sheet +1 to the last, i.e. Noon2, Noon3, Noon4, etc.

Thanks!

 

[My Aswer Is This]

Why not just make a entire new copy of the sheet and give it a new name.
Would that not work?

If that would work try this:

Sub Copy_Noon()
'Modified  11/1/2018  3:30:37 PM  EDT
Sheets("Noon").Copy After:=Sheets(Sheets.Count)
ActiveSheet.Name = "Noon" & ActiveSheet.Index
End Sub

 

[sassriverrat]

Why not just make a entire new copy of the sheet and give it a new name.
Would that not work?

If that would work try this:

Sub Copy_Noon()
'Modified  11/1/2018  3:30:37 PM  EDT
Sheets("Noon").Copy After:=Sheets(Sheets.Count)
ActiveSheet.Name = "Noon" & ActiveSheet.Index
End Sub

It would work after the first sheet. The first sheet it wouldn't work because some of the formulas are different but for Noon2 onward it would work. Now another question based on the same topic- If I can essentially make an infinite number of sheets now- is there a macro to make a formula to add all the values from a selected cell in each sheet? Like if I made 3 "Noons" (Noon, Noon2, Noon3) and wanted the total from "A1" in each sheet?

Thank you very very much!

 

[My Aswer Is This]

Try this:
=SUM(Noon1:Noon3!A1)

 

[sassriverrat]

Try this:
=SUM(Noon1:Noon3!A1)

Which is what I thought too but basically this is designed to be a dynamic workbook- it's going to be a "template" and people will continuously saveas copies- so one copy might have 10 "Noons" and one copy might have 3...that's where I got stuck-

I have a primitive version of this workbook that uses very few macros and mostly formulas and has 10 "Noons" ready with a lot of IF formulas in the totals sheet but it doesn't work when I go over 10....

 

[My Aswer Is This]

Show me what your formula with 10 if's might look like.

 

[sassriverrat]

So I will include five examples below. A note- The totals sheet has about 25 formulas on it. Every formula has an "Override Cell" that allows the user to input a number into a specified cell if the formula somehow had an error. In the first example, this is the "W9" piece.

The "Hours" example computes the hours from the last "used" noon till the time input on the total form (so less than 24 hours). There is a separate column that does minutes.
=IF(W9="",(HOUR(ABS(((((($D$4+(TIME($C$5,0,0)))+$F$4)-(IF('Noon 10'!D8<>0,(('Noon 10'!$D$4+(TIME('Noon 10'!$C$5,0,0))+'Noon 10'!$F$4)),IF('Noon 9'!D8<>0,(('Noon 9'!$D$4+(TIME('Noon 9'!$C$5,0,0))+'Noon 9'!$F$4)),IF('Noon 8'!D8<>0,(('Noon 8'!$D$4+(TIME('Noon 8'!$C$5,0,0))+'Noon 8'!$F$4)),IF('Noon 7'!D8<>0,(('Noon 7'!$D$4+(TIME('Noon 7'!$C$5,0,0))+'Noon 7'!$F$4)),IF('Noon 6'!D8<>0,(('Noon 6'!$D$4+(TIME('Noon 6'!$C$5,0,0))+'Noon 6'!$F$4)),IF('Noon 5'!D8<>0,(('Noon 5'!$D$4+(TIME('Noon 5'!$C$5,0,0))+'Noon 5'!$F$4)),IF('Noon 4'!D8<>0,(('Noon 4'!$D$4+(TIME('Noon 4'!$C$5,0,0))+'Noon 4'!$F$4)),IF('Noon 3'!D8<>0,(('Noon 3'!$D$4+(TIME('Noon 3'!$C$5,0,0))+'Noon 3'!$F$4)),IF('Noon 2'!$D$8<>0,(('Noon 2'!$D$4+(TIME('Noon 2'!$C$5,0,0))+'Noon 2'!$F$4)),IF(Noon!D8<>0,((Noon!$D$4+(TIME(Noon!$C$5,0,0))+Noon!$F$4)),IF(Noon!F4="No Data Input",((Noon!R28+(TIME(Noon!Z8,0,0)))+Noon!S28),)))))))))))))))))),W9)



The "Total Hours" example computes total hours of the entire workbook, meaning all "used" noons and the "Hours" from above. There is a separate column that does minutes.
=D8+'Noon 10'!D9+IF((F8+'Noon 10'!F9)>=60,(INT((F8+'Noon 10'!F9)/60)),)



This piece adds all values from the "used" noons and adds it to whatever value was input into a cell on the Totals sheet.
=Noon!N12+'Noon 2'!N12+'Noon 5'!N12+'Noon 3'!N12+'Noon 4'!N12+'Noon 6'!N12+'Noon 7'!N12+'Noon 8'!N12+'Noon 9'!N12+'Noon 10'!N12+(IF(R17<=(D8+F8),R17,"Error"))


This piece finds the distance (assuming the Y10 override isn't used) from the last used Noon "total distance" to today's total distance, giving the difference as the value.
=IF(Y10<>"",Y10, N8-(IF('Noon 10'!N8>0,'Noon 10'!N8,IF('Noon 9'!N8>0,'Noon 9'!N8,IF('Noon 8'!N8>0,'Noon 8'!N8,IF('Noon 7'!N8>0,'Noon 7'!N8,IF('Noon 6'!N8>0,'Noon 6'!N8,IF('Noon 5'!N8>0,'Noon 5'!N8,IF('Noon 4'!N8>0,'Noon 4'!N8,IF('Noon 3'!N8>0,'Noon 3'!N8,IF('Noon 2'!N8>0,'Noon 2'!N8,Noon!N8)))))))))))

Finally, this example is the total distance and like the other formulas, reaches back to the last "used" noon.
=(IF(Y10<>"",(IF('Noon 10'!N8>0,'Noon 10'!N8,IF('Noon 9'!N8>0,'Noon 9'!N8,IF('Noon 8'!N8>0,'Noon 8'!N8,IF('Noon 7'!N8>0,'Noon 7'!N8,IF('Noon 6'!N8>0,'Noon 6'!N8,IF('Noon 5'!N8>0,'Noon 5'!N8,IF('Noon 4'!N8>0,'Noon 4'!N8,IF('Noon 3'!N8>0,'Noon 3'!N8,IF('Noon 2'!N8>0,'Noon 2'!N8,Noon!N8))))))))))+N7+Noon!R26,R6+Noon!R26))



Now if you're still reading this, the main purpose of this is to get rid of having "extra noons or sheets" when they aren't used while having the ability to quickly (i.e. click of a button) generate new ones or more when I need them.

Thank you very much again. I am learning a HECK of a lot more than I think I could every convey. The previous workbook "worked" but I like always trying to improve it and I really enjoy learning. The original workbook and "if"'s were a big deal to me when I first learned how to use them....now VBA is the "next big thing" to me!

 

[My Aswer Is This]

This is beyond my knowledgebase.
I will continue to monitor this thread to see what I can learn.