Hi,
I am trying to update an old macro and only have a basic knowledge of VBA. Each month we receive input files from multiple sources. Each file has data on a "Current Fcst" tab. Dates are in column C and Data resides in various columns moving out. The format is always the same, new dates are simply added to the bottom.
The existing macro copies a set range from each file into a consolidated sheet. The issue with this is that we continually pull expired data and have no protection against changes to the input format. I would like to alter the macro to use a vlookup on the date in column C (referenceing the date in column A in the consolidation tab.) Since the headers do not change it is okay to simply ref the number of columns to count over, however I would not be averse to an hlookup within the vlookup.
Here is my current code:
Sub Copy_ST_Example()
Dim wrkbkincurr As Workbook
Dim wrkbkcurr As Workbook
Dim wrkshtin As Worksheet
Dim str As String
Set wrkbkcurr = ActiveWorkbook
str = wrkbkcurr.Sheets("ST All in").Cells(1, 2)
Set wrkbkincurr = Workbooks.Open(Trim(str), no)
Set wrkshtin = wrkbkincurr.Sheets("Current Fcst")
wrkshtin.Activate
wrkshtin.Range ("R102:R209")
Result = Application.WorksheetFunction.VLookup(Value, Range, 3, False)
wrkbkcurr.Sheets("ST All in").Activate
Range("A3").Select
Set wrkshtin = Nothing
wrkbkcurr.Save
wrkbkincurr.Close False
Set wrkbkincurr = Nothing
End Sub
The location of the input file is identifed in row 2 (EX: C:\Documents and Settings\Desktop\LossModel_example.xls).
I will create a seperate sub for each input file changing the reference.
Once this data is in these files there are many other processes performed, so linking to each file is not an option as the size explodes.
Thanks for your help!
I am trying to update an old macro and only have a basic knowledge of VBA. Each month we receive input files from multiple sources. Each file has data on a "Current Fcst" tab. Dates are in column C and Data resides in various columns moving out. The format is always the same, new dates are simply added to the bottom.
The existing macro copies a set range from each file into a consolidated sheet. The issue with this is that we continually pull expired data and have no protection against changes to the input format. I would like to alter the macro to use a vlookup on the date in column C (referenceing the date in column A in the consolidation tab.) Since the headers do not change it is okay to simply ref the number of columns to count over, however I would not be averse to an hlookup within the vlookup.
Here is my current code:
Sub Copy_ST_Example()
Dim wrkbkincurr As Workbook
Dim wrkbkcurr As Workbook
Dim wrkshtin As Worksheet
Dim str As String
Set wrkbkcurr = ActiveWorkbook
str = wrkbkcurr.Sheets("ST All in").Cells(1, 2)
Set wrkbkincurr = Workbooks.Open(Trim(str), no)
Set wrkshtin = wrkbkincurr.Sheets("Current Fcst")
wrkshtin.Activate
wrkshtin.Range ("R102:R209")
Result = Application.WorksheetFunction.VLookup(Value, Range, 3, False)
wrkbkcurr.Sheets("ST All in").Activate
Range("A3").Select
Set wrkshtin = Nothing
wrkbkcurr.Save
wrkbkincurr.Close False
Set wrkbkincurr = Nothing
End Sub
The location of the input file is identifed in row 2 (EX: C:\Documents and Settings\Desktop\LossModel_example.xls).
I will create a seperate sub for each input file changing the reference.
Once this data is in these files there are many other processes performed, so linking to each file is not an option as the size explodes.
Thanks for your help!
