Macro works on one sheet, not another? + Help with minimizing code.

[MattHamby]

Hello,

I have created a report the has 2 summaries, one is a total job summary and the other is a weekly summary. When you open the excel the page for total job summary is the only sheet available - there are templates and reference sheets that are hidden (all are protected). There is a button on this sheet that generates a weekly summary sheet and renames it to "Week###" (you simply enter which week number you want it to read). On this sheet there is a button that generates the 7 daily reports and renames them "FOR###" (this info is gathered from cells with the formatting based on the week number you enter). the summary sheets have links to each sheet for important information respectfully the total job summary has links to each weekly summary and the weekly summary has links to each daily report. I have everything working correctly. However, I thought it would be nice to have everything generated from the first button click on the total job summary sheet. Now here is where I can't find what is wrong. I copied all of the coding from the weekly summary into the macro on the total job summary, it works and fills out all of the information and creates all of the sheets like it should, however, when you look at the weekly summary sheet, the linked information from each daily report has not been modified - they are still linked to the template. I've looked through the code and can't find what is wrong. I would appreciate any help and also if you see a means of making this code better - I am learning to code and I get things to work, but there may be better ways of doing so - especially where doing the sums for the newly added referenced cells that are every other line (I had to enter each cell for 104 weeks, so the max this report would be good for is 2 years, I would like to make it good for an infinite amount of entries, I don't know how to write that code though, so I have immensely long lines selecting each cell individually - you'll know it when you see it). I have to make 2 posts here as my code is too long.

Thanks,

Matt

[MattHamby]

Private Sub AddNewWeek_Click()

Sheets("WeeklyCompletionReportTemplate").Visible = True
Sheets("WeeklyCompletionReportTemplate").Copy After:=Sheets("CompletionReportSummary")
Sheets("WeeklyCompletionReportTemplate").Visible = False

Sheets("SummaryTemplate").Visible = True
Sheets("SummaryTemplate").Unprotect
Worksheets("CompletionReportSummary").Unprotect

Dim WeekName As Variant
WeekName = InputBox("What is the new week number?")
Worksheets("SummaryTemplate").Range("A7").Value = WeekName

NewPageName = Worksheets("SummaryTemplate").Range("A7").Text
Worksheets(2).Name = NewPageName

Worksheets(2).Range("X5").Value = Now + (7 - Weekday(Now))

Worksheets("SummaryTemplate").Activate
ActiveSheet.Rows("1:2").EntireRow.Select
Selection.Copy
  With Sheets("CompletionReportSummary")
   .Activate
   .Cells(32, 1).Activate
  End With
Selection.Insert
Worksheets("CompletionReportSummary").Activate
Sheets("CompletionReportSummary").Select
Cells.Replace What:="WeeklyCompletionReportTemplate", Replacement:=NewPageName, LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
ReplaceFormat:=False

Range("F14").Formula = "=SUM($A$33,$A$35,$A$37,$A$39,$A$41,$A$43,$A$45,$A$47,$A$49,$A$51,$A$53,$A$55,$A$57,$A$59,$A$61,$A$63,$A$65,$A$67,$A$69,$A$71,$A$73,$A$75,$A$77,$A$79,$A$81,$A$83,$A$85,$A$87,$A$89,$A$91,$A$93,$A$95,$A$97,$A$99,$A$101,$A$103,$A$105,$A$107,$A$109,$A$111,$A$113,$A$115,$A$117,$A$119,$A$121,$A$123,$A$125,$A$127,$A$129,$A$131,$A$133,$A$135,$A$137,$A$139,$A$141,$A$143,$A$145,$A$147,$A$149,$A$151,$A$153,$A$155,$A$157,$A$159,$A$161,$A$163,$A$165,$A$167,$A$169,$A$171,$A$173,$A$175,$A$177,$A$179,$A$181,$A$183,$A$185,$A$187,$A$189,$A$191,$A$193,$A$195,$A$197,$A$199,$A$201,$A$203,$A$205,$A$207,$A$209,$A$211,$A$213,$A$215,$A$217,$A$219,$A$221,$A$223,$A$225,$A$227,$A$229,$A$231,$A$233,$A$235,$A$237,$A$239)"

Sheets("FORTemplate").Visible = True
Worksheets(2).Unprotect

Sheets("FORTemplate").Copy After:=Worksheets(2)
NewPageName = Worksheets("SummaryTemplate").Range("A8").Text
ActiveWindow.ActiveSheet.Name = NewPageName
Worksheets(NewPageName).Range("X5").Value = Now + (1 - Weekday(Now))
Worksheets("SummaryTemplate").Activate
ActiveSheet.Rows("4:5").EntireRow.Select
Selection.Copy
  With Worksheets(2)
   .Activate
   .Cells(30, 1).Activate
  End With
Selection.Insert
Worksheets(2).Activate
Worksheets(2).Select
Cells.Replace What:="FORTemplate", Replacement:=NewPageName, LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
ReplaceFormat:=False

Sheets("FORTemplate").Copy After:=Worksheets(2)
NewPageName = Worksheets("SummaryTemplate").Range("A9").Text
ActiveWindow.ActiveSheet.Name = NewPageName
Worksheets(NewPageName).Range("X5").Value = Now + (2 - Weekday(Now))
Worksheets("SummaryTemplate").Activate
ActiveSheet.Rows("4:5").EntireRow.Select
Selection.Copy
  With Worksheets(2)
   .Activate
   .Cells(32, 1).Activate
  End With
Selection.Insert
Worksheets(2).Activate
Worksheets(2).Select
Cells.Replace What:="FORTemplate", Replacement:=NewPageName, LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
ReplaceFormat:=False

Sheets("FORTemplate").Copy After:=Worksheets(2)
NewPageName = Worksheets("SummaryTemplate").Range("A10").Text
ActiveWindow.ActiveSheet.Name = NewPageName
Worksheets(NewPageName).Range("X5").Value = Now + (3 - Weekday(Now))
Worksheets("SummaryTemplate").Activate
ActiveSheet.Rows("4:5").EntireRow.Select
Selection.Copy
  With Worksheets(2)
   .Activate
   .Cells(34, 1).Activate
  End With
Selection.Insert
Worksheets(2).Activate
Worksheets(2).Select
Cells.Replace What:="FORTemplate", Replacement:=NewPageName, LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
ReplaceFormat:=False

Sheets("FORTemplate").Copy After:=Worksheets(2)
NewPageName = Worksheets("SummaryTemplate").Range("A11").Text
ActiveWindow.ActiveSheet.Name = NewPageName
Worksheets(NewPageName).Range("X5").Value = Now + (4 - Weekday(Now))
Worksheets("SummaryTemplate").Activate
ActiveSheet.Rows("4:5").EntireRow.Select
Selection.Copy
  With Worksheets(2)
   .Activate
   .Cells(36, 1).Activate
  End With
Selection.Insert
Worksheets(2).Activate
Worksheets(2).Select
Cells.Replace What:="FORTemplate", Replacement:=NewPageName, LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
ReplaceFormat:=False

Sheets("FORTemplate").Copy After:=Worksheets(2)
NewPageName = Worksheets("SummaryTemplate").Range("A12").Text
ActiveWindow.ActiveSheet.Name = NewPageName
Worksheets(NewPageName).Range("X5").Value = Now + (5 - Weekday(Now))
Worksheets("SummaryTemplate").Activate
ActiveSheet.Rows("4:5").EntireRow.Select
Selection.Copy
  With Worksheets(2)
   .Activate
   .Cells(38, 1).Activate
  End With
Selection.Insert
Worksheets(2).Activate
Worksheets(2).Select
Cells.Replace What:="FORTemplate", Replacement:=NewPageName, LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
ReplaceFormat:=False

Sheets("FORTemplate").Copy After:=Worksheets(2)
NewPageName = Worksheets("SummaryTemplate").Range("A13").Text
ActiveWindow.ActiveSheet.Name = NewPageName
Worksheets(NewPageName).Range("X5").Value = Now + (6 - Weekday(Now))
Worksheets("SummaryTemplate").Activate
ActiveSheet.Rows("4:5").EntireRow.Select
Selection.Copy
  With Worksheets(2)
   .Activate
   .Cells(40, 1).Activate
  End With
Selection.Insert
Worksheets(2).Activate
Worksheets(2).Select
Cells.Replace What:="FORTemplate", Replacement:=NewPageName, LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
ReplaceFormat:=False

Sheets("FORTemplate").Copy After:=Worksheets(2)
NewPageName = Worksheets("SummaryTemplate").Range("A14").Text
ActiveWindow.ActiveSheet.Name = NewPageName
Worksheets(NewPageName).Range("X5").Value = Now + (7 - Weekday(Now))
Worksheets("SummaryTemplate").Activate
ActiveSheet.Rows("4:5").EntireRow.Select
Selection.Copy
  With Worksheets(2)
   .Activate
   .Cells(42, 1).Activate
  End With
Selection.Insert
Worksheets(2).Activate
Worksheets(2).Select
Cells.Replace What:="FORTemplate", Replacement:=NewPageName, LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
ReplaceFormat:=False

Range("F14").Formula = "=SUM($A$31,$A$33,$A$35,$A$37,$A$39,$A$41,$A$43)"
Range("P14").Formula = "=SUM($D$31,$D$33,$D$35,$D$37,$D$39,$D$41,$D$43)"
Range("Z14").Formula = "=SUM($G$31,$G$33,$G$35,$G$37,$G$39,$G$41,$G$43)"
Range("J20").Formula = "=SUM($J$31,$J$33,$J$35,$J$37,$J$39,$J$41,$J$43)"
Range("O20").Formula = "=SUM($M$31,$M$33,$M$35,$M$37,$M$39,$M$41,$M$43)"
Range("T20").Formula = "=SUM($P$31,$P$33,$P$35,$P$37,$P$39,$P$41,$P$43)"
Range("Y20").Formula = "=SUM($S$31,$S$33,$S$35,$S$37,$S$39,$S$41,$S$43)"
Range("J24").Formula = "=SUM($V$31,$V$33,$V$35,$V$37,$V$39,$V$41,$V$43)"
Range("O24").Formula = "=SUM($Y$31,$Y$33,$Y$35,$Y$37,$Y$39,$Y$41,$Y$43)"
Range("T24").Formula = "=SUM($AB$31,$AB$33,$AB$35,$AB$37,$AB$39,$AB$41,$AB$43)"
Range("Y24").Formula = "=SUM($AE$31,$AE$33,$AE$35,$AE$37,$AE$39,$AE$41,$AE$43)"
Range("AD24").Formula = "=SUM($AI$31,$AI$33,$AI$35,$AI$37,$AI$39,$AI$41,$AI$43)"
Range("AI24").Formula = "=SUM($AL$31,$AL$33,$AL$35,$AL$37,$AL$39,$AL$41,$AL$43)"

Sheets("FORTemplate").Visible = False
Worksheets(2).Protect
Worksheets("CompletionReportSummary").Protect
Sheets("SummaryTemplate").Protect
Sheets("SummaryTemplate").Visible = False

Worksheets(9).Activate

End Sub

[davesexcel]

Attaching a sample wb might help.

Could it be a simple activesheet instead of a named sheet?

[MattHamby]

Attaching a sample wb might help.

Could it be a simple activesheet instead of a named sheet?

I'll Attach the wb shortly - however, it is currently in the form where the code is on 2 separate sheets.

[MattHamby]

I had to remove several lines - basically repeats for line 26 (Range("##").Formula = "SUM($A$33,$A35,...)). The only difference in each line following is the cells change for Range and SUM.
Any help for either portion of these problems would be greatly appreciated!

Thanks,

Matt

[MattHamby]

Here you go!

Thanks,

Matt

[davesexcel]

Can you make you original question more readable, it is hard to identify what the problem is, it looks like you just wrote it in one continuous line.

[MattHamby]

I have created a report the has 2 summaries, one is a total job summary and the other is a weekly summary.

When you open excel the page for total job summary is the only sheet available - there are templates and reference sheets that are hidden (all are protected). There is a button on this sheet that generates a weekly summary sheet and renames it to "Week###" (you simply enter which week number you want it to read).

On this sheet there is a button that generates the 7 daily reports and renames them "FOR###" (this info is gathered from cells with the formatting based on the week number you enter). the summary sheets have links to each sheet for important information respectfully the total job summary has links to each weekly summary and the weekly summary has links to each daily report.

I have everything working correctly. However, I thought it would be nice to have everything generated from the first button click on the total job summary sheet. Now here is where I can't find what is wrong. I copied all of the coding from the weekly summary into the macro on the total job summary, it works and fills out all of the information and creates all of the sheets like it should, however, when you look at the weekly summary sheet, the linked information from each daily report has not been modified - they are still linked to the template. I've looked through the code and can't find what is wrong.

I would appreciate any help and also if you see a means of making this code better - I am learning to code and I get things to work, but there may be better ways of doing so - especially where doing the sums for the newly added referenced cells that are every other line (I had to enter each cell for 104 weeks, so the max this report would be good for is 2 years, I would like to make it good for an infinite amount of entries, I don't know how to write that code though, so I have immensely long lines selecting each cell individually - you'll know it when you see it). I have to make 2 posts here as my code is too long.

I'm not sure if that helps.

So to maybe simplify there are 2 separate issues;

1. I have the 2 separate buttons that reside on 2 separate worksheets. I tried combining the macro from the 2nd sheet into the button on the first sheet, but for some reason it does not work completely - changing the links on the weekly summary sheet does not work.

2. On the total job summary sheet, totals are calculated for newly added rows. When a row is inserted the formulas change to omit the inserted row. To fix this I have a line of code that reinserts the formula into the cell. However the problem with this is it will only work for up to 104 new rows. I don't know how to write a formula that would total the new rows indefinitely.

I hope that is clear.