Create a new worksheet named Summary that has the same layou

Create a new worksheet named Summary that has the same layout as the monthly worksheets

Position it to the left of the January worksheet.

In each of the numeric cells of the Summary worksheet enter a formula that uses cross sheet references to sum up the values from the monthly worksheets.

You must write a formula in the upper left cell (C7) that you can copy down and across to all the cells of the table.

Your formula should use the SUM function.

DO NOT write a function that looks like this: SUM(MonthX!C7,MonthY!C7,... or like this SUM(MonthX!C7+MonthY!C7,...

What does this mean and how do you write the SUM function different?

Solution

Sub Summary_All_Worksheets_With_Formulas() Dim Sh As Worksheet Dim Newsh As Worksheet Dim myCell As Range Dim ColNum As Integer Dim RwNum As Long Dim Basebook As Workbook With Application .Calculation = xlCalculationManual .ScreenUpdating = False End With \'Delete the sheet \"Summary-Sheet\" if it exist Application.DisplayAlerts = False On Error Resume Next ThisWorkbook.Worksheets(\"Summary-Sheet\").Delete On Error GoTo 0 Application.DisplayAlerts = True \'Add a worksheet with the name \"Summary-Sheet\" Set Basebook = ThisWorkbook Set Newsh = Basebook.Worksheets.Add Newsh.Name = \"Summary-Sheet\" \'The links to the first sheet will start in row 2 RwNum = 1 For Each Sh In Basebook.Worksheets If Sh.Name <> Newsh.Name And Sh.Visible Then ColNum = 1 RwNum = RwNum + 1 \'Copy the sheet name in the A column Newsh.Cells(RwNum, 1).Value = Sh.Name For Each myCell In Sh.Range(\"A1,D5:E5,Z10\") \'<--Change the range ColNum = ColNum + 1 Newsh.Cells(RwNum, ColNum).Formula = _ \"=\'\" & Sh.Name & \"\'!\" & myCell.Address(False, False) Next myCell End If Next Sh Newsh.UsedRange.Columns.AutoFit With Application .Calculation = xlCalculationAutomatic .ScreenUpdating = True End With End Sub