Update Yearly Workbook With Weekly Workbook On Daily Basis For Each Region
Guys,
By subject it sounds familiar of looping though multiple sheets and updating consolidate multiple sheets, but I am struggling to get through.
Following is the process:
1. Open File DialogBox to select weekly workbook.
2. Find dates in row 1:1 in weekly workbook and the dates in Column A of yearly workbook.
3. Copy cell 200 of the found date column from Weekly workbook and paste special values to found date adjacent column B to Yearly workbook. For i,e; If I am looking for date 05th Feb in Weekly workbook in row 1:1, if its found, then copy cell 200 of that particular column, then look for 05th Feb in Yearly workbook Column A and paste special value to Column B.
4. Above process must run for every Region's worksheet.
I am attaching RegionsMiles(Weekly Workbook) & YearlySummary(Yearly Workbook) samples, please guide me to write a VBA code.
If you have any question, please don't hesitate to ask.
Thanking you in advance
RegionsMiles-0205-0211-.xlsx
YearlySummary.xlsx
By subject it sounds familiar of looping though multiple sheets and updating consolidate multiple sheets, but I am struggling to get through.
Following is the process:
1. Open File DialogBox to select weekly workbook.
2. Find dates in row 1:1 in weekly workbook and the dates in Column A of yearly workbook.
3. Copy cell 200 of the found date column from Weekly workbook and paste special values to found date adjacent column B to Yearly workbook. For i,e; If I am looking for date 05th Feb in Weekly workbook in row 1:1, if its found, then copy cell 200 of that particular column, then look for 05th Feb in Yearly workbook Column A and paste special value to Column B.
4. Above process must run for every Region's worksheet.
I am attaching RegionsMiles(Weekly Workbook) & YearlySummary(Yearly Workbook) samples, please guide me to write a VBA code.
If you have any question, please don't hesitate to ask.
Thanking you in advance
RegionsMiles-0205-0211-.xlsx
YearlySummary.xlsx
ASKER
Actually, this is just a sample for past dates, This I am updating daily, opening the weekly workbook, copying total miles for the day for every region and pasting in yearly workbook for every region. It doesn't take time, but I am struggling to get this process in automation.
It would be good to loop through date in Yearly workbook column, so if I wrongly updated any date's miles, by VBA it would be corrected by selecting that particular week's workbook.
It would be good to loop through date in Yearly workbook column, so if I wrongly updated any date's miles, by VBA it would be corrected by selecting that particular week's workbook.
Okay let me see.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Wow.
That was quick and perfect. I have no words for your exceptional intellectual ability.
Thanks a lot Neeraj.
That was quick and perfect. I have no words for your exceptional intellectual ability.
Thanks a lot Neeraj.
You're welcome Shums! Glad I was able to help.
Thanks for your feedback and compliment. :)
Thanks for your feedback and compliment. :)
While updating YearlySummary workbook, do you need to update only for the dates where column B is currently empty OR you need to loop through all the dates in column A and then fetch the relevant data if any in the weekly workbook?