Solved: Copy update value from changing file name

[blastpwr1970]

Hi all,

I have a workbook1(sheet1) and I need to get data from a specific cell in workbook2 but workbook2 keeps changing name due to a daily update.

Name sample
report 01-01.xls
report 01-02.xls
report 01-03.xls
*
report 01-31.xls

All the way to the end of the month and then it goes in to the next month.

report 02-01.xls
report 02-02.xls
report 02-03.xls
*
report 02-28.xls

See attachment for more information.

Could somebody please help me.

Thank you
Julio

[mdmackillop]

This should find the last file for the month corresponding to the cell in column B
[vba]
'Select the worksheet to get data from
Dat = ThisWorkbook.Path & "\report " & _
Format(Month(DateValue(ActiveCell.Offset(, -1))), "00") & "-"
For i = 31 To 1 Step -1
testname = Dat & Format(i, "00") & ".xls"
If Len(Dir(testname)) > 0 Then
Workbooks.Open(Filename:=testname).RunAutoMacros Which:=xlAutoOpen
Sheets("PC").Select
ActiveCell.SpecialCells(xlLastCell).Select
Exit For
End If
Next
[/vba]

[blastpwr1970]

I am sorry,

I didn't explaint right. Hopefully I will this time around, once again thank you all.

I have a worksheet E:\\updatepath\work.xls this one is the one I need to update and then I have another work sheet in H:\\computer\project 05-30.xls this worksheet is read only, this is where I am getting the data, I can not put any code in this workbook.
Now tomorrow a new worksheet will be created with the new file name H:\\computer\project 05-31.xls, yesterdays file still in there for future reference.

This is what I have so far.

Sub GetPSRData()

Dim Cel As Range
Dim Row As Long
Dim Col As Long


'Turn off ScreenUpdating for faster macro runtime so screen won't flash while running
Application.ScreenUpdating = False


'Select the worksheet to get data from
Workbooks.Open(Filename:= _
"\\Newnas01\PSR\Archive\report 06-06.xls"). _
RunAutoMacros Which:=xlAutoOpen
Sheets("PC").Select
ActiveCell.SpecialCells(xlLastCell).Select


'----------This is the part I can not incorporate to update---------
''' 'Select the worksheet to get data from
'''Dat = ThisWorkbook.Path & "\report " & Format(Month(DateValue(ActiveCell.Offset(, -1))), "00") & "-"
'''For i = 31 To 1 Step -1
''' testname = Dat & Format(i, "00") & ".xls"
''' If Len(Dir(testname)) > 0 Then
''' Workbooks.Open(Filename:=testname).RunAutoMacros Which:=xlAutoOpen
''' Sheets("PC").Select
''' ActiveCell.SpecialCells(xlLastCell).Select
''' Exit For
''' End If
'''Next



'Select row and column to get the build amount

Set Cel = Range("6:6").Find(What:=" Month-To-Date Prod", LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=True)
If Cel Is Nothing Then
GoTo ExitSub:
Else
Col = Cel.Column
End If
Set Cel = Range("B:B").Find(What:="Product1", LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=True)
If Cel Is Nothing Then
GoTo ExitSub:
Else
Row = Cel.Row
End If


Range("A1").Select
ActiveCell.FormulaR1C1 = Cells(Row, Col).Text
With Worksheets("pc")
.Range("A1").copy

End With

Windows("FinalProducts.xls").Activate
Sheets("Product Produced").Select
Range("I3").PasteSpecial _

Workbooks("report 06-01.xls").Close SaveChanges:=False



ExitSub:

Set Cel = Nothing

'Turn ScreenUpdating back on
Application.ScreenUpdating = True

End Sub

Month to date-------Total

April-06-----------------5,907
May-06------------------4,954
June-06-----------------524
July-06------------------