Hi,
Let me start by saying I am not familiar with VBA but I do know these codes can be very powerful. Any help that you could provide would be greatly appreciated as I am really struggling with this process every month. I maintain a huge 2007 excel workbook (called Main_Model) that reflects actuals from previous months and formulas that forecast KPIs and volume expectations by month going forward. I need help with the copy/paste process I currently do manually every month - i copy paste over 600 cells manually.
The workbook is set-up as follows - this is a simplified version:
There is 5 sheets on which I do copy/paste, one for each vendor
Vendor_1, Vendor_2, Vendor_3, Vendor_4, Vendor_5
Each Vendor contains about 1300 rows of values, formulas, divided between 25-45 locations, depending on vendor. The values I currently update manually by copy/paste are spread out throughout the sheets.
Example:
Vendor_1
Location 1
Row 5: Beg Client Count
Row 6: Avg Client Count
Row 7: Ending Client Count
Row 8: Formula
Row 9: Count of sodas purchased
Row 10: Count of meals purchased
Row 11: Count of deserts purchased
Row 12: Formula
Row 13 Formula
Row 14 Formula
etcc
Location 2
Row 25: Beg Client Count
Row 26: Avg Client Count
Row 27: Ending Client Count
Row 28: Formula
Row 29: Count of sodas purchased
Row 30: Count of meals purchased
Row 31: Count of deserts purchased
Row 32: Formula
Row 33 Formula
Row 34 Formula
etcc
Column C: Oct'14
Column D: Nov'14
Column E: Dec'14
Column F: Jan'15
Column G: Feb'15
Column H: Mar'15
Each sheet has about 25 locations (it varies for each vendor). And at any given time, the row #s where headers and KPIs are located may change as we need to track a new KPI. For instance, for Location 2, we could start offering premium deserts and this would be inserted as row 32 and everything else would shift down.
The cells I want to automate the copy/paste are the ones titled "Count of sodas purchased", "Count of meals purchased", "Count of deserts purchased" for each location for each vendor. I have the actuals in another spreadsheet (called actual_report) in pivot tables format and every month I copy/paste into knowing the rows on which they are located may change and when I perform these updates, it is to update the prior month, so the following columns to the right every month.
I don't mind setting up a mapping/reference table in a separate sheet in the main_model if it helps in this process.
Is it possible to write a code for this? I need to be very careful, if the values gets pasted into the incorrect rows, it could create a lot of problems.
Please let me know if anyone can help.
Thanks.
Let me start by saying I am not familiar with VBA but I do know these codes can be very powerful. Any help that you could provide would be greatly appreciated as I am really struggling with this process every month. I maintain a huge 2007 excel workbook (called Main_Model) that reflects actuals from previous months and formulas that forecast KPIs and volume expectations by month going forward. I need help with the copy/paste process I currently do manually every month - i copy paste over 600 cells manually.
The workbook is set-up as follows - this is a simplified version:
There is 5 sheets on which I do copy/paste, one for each vendor
Vendor_1, Vendor_2, Vendor_3, Vendor_4, Vendor_5
Each Vendor contains about 1300 rows of values, formulas, divided between 25-45 locations, depending on vendor. The values I currently update manually by copy/paste are spread out throughout the sheets.
Example:
Vendor_1
Location 1
Row 5: Beg Client Count
Row 6: Avg Client Count
Row 7: Ending Client Count
Row 8: Formula
Row 9: Count of sodas purchased
Row 10: Count of meals purchased
Row 11: Count of deserts purchased
Row 12: Formula
Row 13 Formula
Row 14 Formula
etcc
Location 2
Row 25: Beg Client Count
Row 26: Avg Client Count
Row 27: Ending Client Count
Row 28: Formula
Row 29: Count of sodas purchased
Row 30: Count of meals purchased
Row 31: Count of deserts purchased
Row 32: Formula
Row 33 Formula
Row 34 Formula
etcc
Column C: Oct'14
Column D: Nov'14
Column E: Dec'14
Column F: Jan'15
Column G: Feb'15
Column H: Mar'15
Each sheet has about 25 locations (it varies for each vendor). And at any given time, the row #s where headers and KPIs are located may change as we need to track a new KPI. For instance, for Location 2, we could start offering premium deserts and this would be inserted as row 32 and everything else would shift down.
The cells I want to automate the copy/paste are the ones titled "Count of sodas purchased", "Count of meals purchased", "Count of deserts purchased" for each location for each vendor. I have the actuals in another spreadsheet (called actual_report) in pivot tables format and every month I copy/paste into knowing the rows on which they are located may change and when I perform these updates, it is to update the prior month, so the following columns to the right every month.
I don't mind setting up a mapping/reference table in a separate sheet in the main_model if it helps in this process.
Is it possible to write a code for this? I need to be very careful, if the values gets pasted into the incorrect rows, it could create a lot of problems.
Please let me know if anyone can help.
Thanks.
