Help With =SUM In A Cell

[gaspower]

Hello,

I am using =SUM(Sheet2:Sheet8!G33) to calculate the sum of all G33 cells throughout the sheets. I can not figure out how to make sheet8) variable. The amount of sheets is variable, Sheet2 is constant.

So when I import my csv file into Excel, when it is done with the setup creating the sheets, I do not know how many sheets will be created until done. It always will start with Sheet2.

Thanks JR

 

[Ron Coderre]

The typical approach is to create a SheetFirst and a SheetLast and have the formula(s) reference them. The formulas will sum every sheet between those two sheets (inclusive)
Example:

=SUM(SheetFirst:SheetLast!G33)

Is that something you can work with?

 

[gaspower]

Hello,

I guess what I do not understand is the end sheet. It is never the same. I included the VBA code I am using to create the sheets in the workbook.

Private Sub cmdB_Click()
With Application.FileDialog(msoFileDialogFilePicker)
.AllowMultiSelect = False
.InitialFileName = ThisWorkbook.Path
.Title = "Select CSV File."
If .Show = -1 Then
    txtCSV.Text = .SelectedItems(1)
End If
End With
End Sub

Private Sub cmdCreate_Click()
Dim Fs As New FileSystemObject
Dim WbT As Workbook
Dim WsM As Worksheet
Dim WsTmpl As Worksheet
Dim WbData As Workbook
Dim WsData As Worksheet
Dim Ws As Worksheet

Dim LrData As Long
Dim Ii As Long
Dim Jj As Long
Dim mSRN As String
Dim Rr As Integer
Dim Nn As Integer

Set WbT = ThisWorkbook
Set WsM = WbT.Worksheets("Main")
Set WsTmpl = WbT.Worksheets("Template")
For Each Ws In WbT.Worksheets
    If Left(Ws.Name, 5) = "Sheet" Then
        Application.DisplayAlerts = False
        Ws.Delete
        Application.DisplayAlerts = True
    End If
Next

If Not Fs.FileExists(txtCSV.Text) Then
    MsgBox "Input CSV file not found! Please select a valid CSV File.", vbInformation
    Exit Sub
End If
For Each WbData In Workbooks
    If InStr(LCase(txtCSV.Text), LCase(WbData.Name)) <> 0 Then
        WbData.Close False
        Exit For
    End If
Next
Set WbData = Workbooks.Open(txtCSV.Text)
Set WsData = WbData.Worksheets(1)


LrData = LastRowCol(WsData)(0)
mSRN = ""
Set Ws = Nothing
Nn = 0
WbT.Activate

Application.ScreenUpdating = False

With WsData
For Ii = 2 To LrData
    If Trim(.Range("A" & Ii).Text) <> "" Then
        
        If Trim(.Range("A" & Ii).Text) <> mSRN And _
            Trim(.Range("AD" & Ii).Text) <> "" Then
            mSRN = Trim(.Range("A" & Ii).Text)
            WsTmpl.Visible = xlSheetVisible
            WsTmpl.Copy after:=WbT.Worksheets(WbT.Worksheets.Count)
            Set Ws = ActiveSheet
            WsTmpl.Visible = xlSheetHidden
            Nn = Nn + 1
            Ws.Name = "Sheet" & Nn
            Ws.Range("B6") = .Range("C" & Ii)
            Ws.Range("B7") = .Range("F" & Ii)
            Ws.Range("B8") = .Range("G" & Ii)
            Ws.Range("B9") = .Range("H" & Ii).Text & ", " & _
                             .Range("I" & Ii).Text & " " & _
                             .Range("J" & Ii).Text
            Ws.Range("B10") = .Range("D" & Ii)
            Ws.Range("B11") = .Range("E" & Ii)
            Ws.Range("E6") = .Range("AD" & Ii)
            Ws.Range("B16") = .Range("AE" & Ii)
            Ws.Range("G31") = .Range("R" & Ii)
            Ws.Range("G16") = WsData.Range("V" & Ii)
            Ws.Range("G32") = .Range("Q" & Ii)
            Rr = 19
            If Trim(.Range("M" & Ii).Text) <> "" Then
                Ws.Range("A" & Rr) = .Range("O" & Ii)
                Ws.Range("B" & Rr) = .Range("M" & Ii)
                Ws.Range("E" & Rr) = .Range("P" & Ii)
                Ws.Range("F" & Rr) = .Range("L" & Ii)
                
                Rr = Rr + 1
            End If
        Else
            If Not Ws Is Nothing Then
                Ws.Range("A" & Rr) = .Range("O" & Ii)
                Ws.Range("B" & Rr) = .Range("M" & Ii)
                Ws.Range("E" & Rr) = .Range("P" & Ii)
                Ws.Range("F" & Rr) = .Range("L" & Ii)
                'Ws.Range("G32") = .Range("Q" & Ii)
                Rr = Rr + 1
            End If
        End If
    End If
Next
End With
Application.ScreenUpdating = True

Application.DisplayAlerts = False
WbT.Worksheets("Main").Delete
WbT.Worksheets("Sheet1").Delete
Application.DisplayAlerts = True
End Sub



Function LastRowCol(tmpSht As Worksheet) As Variant
Dim lrcArr As Variant

lrcArr = Array(0, "", 0)

If InStr(tmpSht.UsedRange.Address, ":") <> 0 Then
    lrcArr(0) = Split(Split(tmpSht.UsedRange.Address, ":")(1), "$")(2)
    lrcArr(1) = Split(Split(tmpSht.UsedRange.Address, ":")(1), "$")(1)
Else
    lrcArr(0) = Split(tmpSht.UsedRange.Address, "$")(2)
    lrcArr(1) = Split(tmpSht.UsedRange.Address, "$")(1)
End If
If Len(lrcArr(1)) = 1 Then
    lrcArr(2) = Asc(lrcArr(1)) - 64
ElseIf Len(lrcArr(1)) = 2 Then
    lrcArr(2) = (Asc(Left(lrcArr(1), 1)) - 64) * 26 + (Asc(Right(lrcArr(1), 1)) - 64)
ElseIf Len(lrcArr(1)) = 3 Then
    lrcArr(2) = ((Asc(Left(lrcArr(1), 1)) - 64) * 26 * 26) + ((Asc(Mid(lrcArr(1), 2, 1)) - 64) * 26) + (Asc(Right(lrcArr(1), 1)) - 64)
End If
LastRowCol = lrcArr

End Function

Private Sub cmdST_Click()
ThisWorkbook.Worksheets("Template").Visible = xlSheetVisible
ThisWorkbook.Worksheets("Template").Select
ThisWorkbook.Worksheets("Template").Activate


End Sub

 

[T. Valko]

Hello,

I am using =SUM(Sheet2:Sheet8!G33) to calculate the sum of all G33 cells throughout the sheets. I can not figure out how to make sheet8) variable. The amount of sheets is variable, Sheet2 is constant.

So when I import my csv file into Excel, when it is done with the setup creating the sheets, I do not know how many sheets will be created until done. It always will start with Sheet2.

Thanks JR

Create 2 empty "bookend" sheets named Start and End and then you place all newly created sheets between the "bookends" then:

=SUM(Start:End!G33)

Sort of like this:

\Start/\Sheet1/\Sheet2/\Sheet3/\Sheet4/\Sheet5/\End/

For each new sheet you create you simply place it between Start and End.