Macro to match sheet reference in formula to workbook sheet

[Kim B]

This post may look familiar, but it will be used by a different user for a different purpose. The red sheet reference needs to match the by store data pull data. So the 1-2 should say 1-15. There is a tab for each day of the month. For Jan there are 31 tabs...next one will be 1-16 then 1-17 etc. Currently the user is having to go into each store data pull tab and update the paste csv dates manually.

=SUMIFS('Paste CSV File Here 1-2'!$D:$D,'Paste CSV File Here 1-2'!$A:$A,'by Store Data Pull 1-15'!O$4,'Paste CSV File Here 1-2'!$B:$B,'by Store Data Pull 1-15'!$A6)

Thanks you

 

[Rijnsent]

What is your question?

 

[Kim B]

What is your question?

The above paste csv file here x-xx (red) formulas need to be able to match the x-xx on the by store data pull tab. So if by store data pull tab says 1-23, then all the csv formulas in that tab (by store data pull tab 1-23) need to say 1-23. Likewise if you were on by store data pull tab 1-10, the all the paste csv file references need to say 1-10. And so on. Formulas start in column O

 

[Rijnsent]

Okay, what you could do:
This formula should give you the name of the file:

=TEXTAFTER(CELL("filename",A1),"]")

Next, you can use INDIRECT to use that value, e.g. like:

=SUM(INDIRECT("'" & A3 &"'!A10:A12"))

(note: A3 holds the sheet name, you need a lot of single & double quotes.)
So you can build up your SUMIFS with quite some INDIRECTs inside.
Hope that helps,
Koen

 

[Kim B]

Okay, what you could do:
This formula should give you the name of the file:

=TEXTAFTER(CELL("filename",A1),"]")

Next, you can use INDIRECT to use that value, e.g. like:

=SUM(INDIRECT("'" & A3 &"'!A10:A12"))

(note: A3 holds the sheet name, you need a lot of single & double quotes.)
So you can build up your SUMIFS with quite some INDIRECTs inside.
Hope that helps,
Koen

There are 31 (january) by store pull data tabs. Each month has one store data tabs for every day of the month. It would be easier if I could get a macro that looked at each by store data pull tab x-xx and made the paste csv file x-xx formulas match it.

 

[Kim B]

Here is the code for anyone following this.

Private Sub EnterFormula2()
Dim ws As Worksheet
Dim i As Long, j As Long
Dim fX1 As String, fX2 As String
Dim shName1 As String, shName2 As String

On Error Resume Next

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
For Each ws In Worksheets
    If InStr(ws.Name, "by Store Data Pull") Then
        shName1 = "'Paste CSV File Here " & WorksheetFunction.Substitute(ws.Name, "by Store Data Pull ", "") & "'!"
        shName2 = "'" & ws.Name & "'!"
        For i = 15 To 1256 Step 3
            j = i + 1
            fX1 = "=SUMIFS(" & shName1 & "C4," & shName1 & "C1," & shName2 & "R4C," & shName1 & "C2," & shName2 & "R[2]C1)"
            ws.Range(ws.Cells(4, i), ws.Cells(43, i)).FormulaR1C1 = fX1
            fX2 = "=SUMIFS('Forecast 1-9'!C4,'Forecast 1-9'!C1," & shName2 & "R4C,'Forecast 1-9'!C2," & shName2 & "R[2]C1)"
            ws.Range(ws.Cells(4, j), ws.Cells(43, j)).FormulaR1C1 = fX2
        Next i
    End If
Next
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True

End Sub

 

[Peter_SSs]

Here is the code for anyone following this.

When posting vba code in the forum, please use the available code tags. It makes your code much easier to read/debug & copy. My signature block below has more details. I have added the tags for you this time.