External file name in sumifs function

[Niraj Baraili]

Hi Experts,

Need on this.....

I have a recorded macro as below:

Dim Wb As Workbook
  Dim wb1 As Workbook
 
  fname = "Data Consolidation" & Sheet1.Range("G1")
  Path = "C:\Users\niraj.baraili\Desktop\Automation\"
 
  Set Wb = Workbooks.Open(Path & fname)
  Set wb1 = ThisWorkbook
   
   
    wb1.Activate
    Sheet1.Select
    Range("B2").Select
    ActiveCell.FormulaR1C1 = _
        "=SUMIFS(indirect([" & Sheet1.Range("D2") & "]Qtr1'!C5),indirect([" & Sheet1.Range("D2") & "]Qtr1'!C2),RC[-1])"

My file name is - "Data Consolidation - May 2016.xlsx". Every month i have to change the file name to "Data Consolidation - June 2016.xlsx", "Data Consolidation - May 2016.xlsx".

Is there a way instead of going to module changing the code, my code runs.

 

[Niraj Baraili]

Sorry formula in activecell is

ActiveCell.FormulaR1C1 = _
"=SUMIFS('[Data Consolidation - May 2016.xlsx]Qtr1'!C5,'[Data Consolidation - May 2016.xlsx]Qtr1'!C2,RC[-1])"

 

[Ashhu]

Niraj
please attach the sheet

 

[Niraj Baraili]

Here you go....

 

[Niraj Baraili]

After googling long time. Found this code. but still it's not working.....

Any idea where is the disconnect ?

Dim Wb As Workbook
  Dim wb1 As Workbook
  Dim Sheet As String
  fname = "Data Consolidation" & Sheet1.Range("G1")
  Path = "C:\Users\niraj.baraili\Desktop\Automation\"
  Sheet = "'[" & fname & "]Qtr1'!"
     
  Set Wb = Workbooks.Open(Path & fname)
  Set wb1 = ThisWorkbook
 
   
    wb1.Activate
    Sheet1.Select
    Range("B2").Select
   
    Range("F3").Formula = _
    "=SUMIFS(" & Sheet & "$E:$E," & Sheet & "$B:$B,""=""&$B3)"
     
   
   
End Sub

 

[jindon]

Missing Sheet in last arg.

"=SUMIFS(" & Sheet & "$E:$E," & Sheet & "$B:$B," & Sheet & $B3)"

 

[Niraj Baraili]

Hi Jindon,
Since $B is the criteria 1 and is in Wb1, i think 'sheet' is not going to work.

 

[Niraj Baraili]

Hi Experts,

Any help on this ?