CODEWITHSUNDEEP
vbaexcel
James
James

Reputation: 449

Sumif formula that Inserts variable worksheet name into named range of other workbook

As you can see in the code below, I matching worksheet names in different workbooks. Once the macro finds matching worksheet names it then is performing a SUMIF formula. The named range inside of the SUMIF formula is unique to each sheet but is consistent. (i.e. - Name of sheet is "Sheet1"...named range 1 is "Sheet1_WEEKENDING" and named range 2 is "Sheet1_FORECAST"); this is consistent through all sheets.

I want the SUMIF formula to have the worksheet variable in the named range. Example ws = sheet1
(Named range 1 = "ws_WEEKENDING" and named range 2 = "ws_FORECAST")

Code so far:

Public Sub Baseline()
    Dim ws, sh As Worksheet
    Dim wbMaster, wbVariance As Workbook
    Dim fileOpen As Workbook
    Dim folderPath As String
    Const VPPName As String = "Master_Vpp.xlsm"
    Const VarName As String = "Program Variance Report_Test.xlsm"
    '*******************************************************************
    'MUST place Master_VPP and Variance Report files in the same folder

    Application.ScreenUpdating = False

    folderPath = Application.ActiveWorkbook.Path & Application.PathSeparator 'assigning path to get   to both workbooks folder

    On Error Resume Next
    fileOpen = Workbooks("Master_VPP.xlsm")

    If fileOpen Is Nothing Then 'is not open
        Set wbMaster = Application.Workbooks.Open(folderPath & VPPName)
    End If

    Set wbVariance = ActiveWorkbook    'setting variable quarter variance report

    For Each ws In wbVariance.Sheets
    Application.ScreenUpdating = False
    ws.Activate
        If (ws.Name <> "SUMMARY") And (ws.Name <> "Template") Then
            For Each sh In wbMaster.Sheets
                sh.Activate
                If ws.Name = sh.Name Then
                    ws.Range("C20").Activate
                    ActiveCell.FormulaR1C1 = _
                        "=SUMIF(Master_VPP.xlsm!HNB_WEEKENDING,RC2,Master_VPP.xlsm!HNB_FORECAST)"
                        '"=SUMIF('[" & wbMaster & "]'!" & sh.Name & "_WEEKENDING,RC2,'[" & wbMaster & "]'!" & sh.Name & "_FORECAST)"
                        
                    Selection.AutoFill Destination:=Range("C20:C33")
                    
                    'Range("C20").Select
                    'ActiveCell.FormulaR1C1 = _
                        "=SUMIF('[" & wbMaster & "]'!" & ws.Name & "_WEEKENDING',RC2,'[" & wbMaster & "]'!" & ws.Name & "_FORECAST)"
                    'Selection.AutoFill Destination:=Range("C20:C33")
                Else
                GoTo Cont:
                End If
            Next sh
       Else
         GoTo Cont
    
Cont:
    
       End If
    Next ws

End Sub

Upvotes: 0

Views: 828

Answers (1)

Rory
Rory

Reputation: 34045

Reviewing your code, it appears it never worked - I had assumed that it was only the formula that required adjusting. Perhaps this will do it:

Public Sub Baseline()
Dim ws As Worksheet, sh As Worksheet
Dim wbMaster As Workbook, wbVariance As Workbook
Dim fileOpen As Workbook
Dim folderPath As String

Const VPPName As String = "Master_Vpp.xlsm"
Const VarName As String = "Program Variance Report_Test.xlsm"
'*******************************************************************
'MUST place Master_VPP and Variance Report files in the same folder

Application.ScreenUpdating = False

folderPath = Application.ActiveWorkbook.Path & Application.PathSeparator 'assigning path to get   to both workbooks folder

Application.ScreenUpdating = False

Set wbVariance = ActiveWorkbook    'setting variable quarter variance report

On Error Resume Next
Set fileOpen = Workbooks(VPPName)
On Error GoTo 0

If fileOpen Is Nothing Then 'is not open
    Set fileOpen = Application.Workbooks.Open(folderPath & VPPName)
End If

For Each ws In wbVariance.Sheets
    If (ws.Name <> "SUMMARY") And (ws.Name <> "Template") Then
        On Error Resume Next
        Set sh = fileOpen.Sheets(ws.Name)
        On Error GoTo 0
        If Not sh Is Nothing Then
            With ws.Range("C20")
                .FormulaR1C1 = _
                "=SUMIF(" & VPPName & "!" & sh.Name & "_WEEKENDING,RC2," & VPPName & "!" & sh.Name & "_FORECAST)"

                .AutoFill Destination:=ws.Range("C20:C33")
            End With
            Set sh = Nothing
        End If
   End If
Next ws

End Sub

Upvotes: 0

Related Questions