Hello Everyone,
I'm very new to VBA/Macros and don't know how to get the required results. I've searched but not sure if it's been posted before. I'm only able to create the vba for one month, however as I have all 12 months of data in separate columns, I'm not sure how to get it to select the month based on a cell within the workbook.
Goal 1: To create a VBA macro to copy > Paste Special Values for the earliest forecast (in this example March) over to column P based on the Month in cell P6 or P7 (+1) to get to the next month (i.e. 2 or Feb + 1 = 3 or March).
Goal 2: I plan to have the same sheet multiple times, How would you be able to create a macro/vba for each tab to perform the same function of copying over March > column P for each tab so you execute it once for the entire workbook?
Thank you,
Harry
I'm very new to VBA/Macros and don't know how to get the required results. I've searched but not sure if it's been posted before. I'm only able to create the vba for one month, however as I have all 12 months of data in separate columns, I'm not sure how to get it to select the month based on a cell within the workbook.
Goal 1: To create a VBA macro to copy > Paste Special Values for the earliest forecast (in this example March) over to column P based on the Month in cell P6 or P7 (+1) to get to the next month (i.e. 2 or Feb + 1 = 3 or March).
Goal 2: I plan to have the same sheet multiple times, How would you be able to create a macro/vba for each tab to perform the same function of copying over March > column P for each tab so you execute it once for the entire workbook?
Thank you,
Harry
VBA Range Columns and Rows Example.xlsm | |||||||||||||||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | T | U | V | W | X | Y | Z | AA | AB | AC | AD | AE | |||
1 | 2 | Feburary | Drop-down menu for cells C1 & D1 | ||||||||||||||||||||||||||||||
2 | |||||||||||||||||||||||||||||||||
3 | 1 | January | |||||||||||||||||||||||||||||||
4 | Actual | Actual | Forecast | Forecast | Forecast | Forecast | Forecast | Forecast | Forecast | Forecast | Forecast | Forecast | Forecast | 2 | Feburary | ||||||||||||||||||
5 | 3 | March | |||||||||||||||||||||||||||||||
6 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 | 2 | Formula based on cell C1 | 4 | April | |||||||||||||||||
7 | January | Feburary | March | April | May | June | July | August | September | October | November | December | Feburary | Formula based on cell D1 | 5 | May | |||||||||||||||||
8 | Account 1 | 10 | 11 | 15 | 10 | 10 | 10 | 10 | 10 | 10 | 10 | 10 | 10 | 15 | 6 | June | |||||||||||||||||
9 | Account 2 | 20 | 21 | 25 | 10 | 10 | 10 | 10 | 10 | 10 | 10 | 10 | 10 | 25 | 7 | July | |||||||||||||||||
10 | Account 3 | 30 | 31 | 35 | 10 | 10 | 10 | 10 | 10 | 10 | 10 | 10 | 10 | 35 | 8 | August | |||||||||||||||||
11 | Account 4 | 40 | 41 | 45 | 10 | 10 | 10 | 10 | 10 | 10 | 10 | 10 | 10 | 45 | 9 | September | |||||||||||||||||
12 | Account 5 | 50 | 51 | 55 | 10 | 10 | 10 | 10 | 10 | 10 | 10 | 10 | 10 | 55 | 10 | October | |||||||||||||||||
13 | Account 6 | 60 | 61 | 65 | 10 | 10 | 10 | 10 | 10 | 10 | 10 | 10 | 10 | 65 | 11 | November | |||||||||||||||||
14 | Account 7 | 70 | 71 | 75 | 10 | 10 | 10 | 10 | 10 | 10 | 10 | 10 | 10 | 75 | 12 | December | |||||||||||||||||
15 | Account 8 | 80 | 81 | 85 | 10 | 10 | 10 | 10 | 10 | 10 | 10 | 10 | 10 | 85 | |||||||||||||||||||
16 | Account 9 | 90 | 91 | 95 | 10 | 10 | 10 | 10 | 10 | 10 | 10 | 10 | 10 | 95 | |||||||||||||||||||
17 | Account 10 | 100 | 101 | 105 | 10 | 10 | 10 | 10 | 10 | 10 | 10 | 10 | 10 | 105 | |||||||||||||||||||
18 | |||||||||||||||||||||||||||||||||
19 | |||||||||||||||||||||||||||||||||
20 | Goal 1: | To create a VBA macro to copy > Paste Special Values for the earliest forecast (in this example March) over to column P based on the Month in cell P6 or P7 (+1) to get to the next month (i.e. 2 or Feb + 1 = 3 or March). | |||||||||||||||||||||||||||||||
21 | The March forecast values in column E will then be overwritten with actuals. | ||||||||||||||||||||||||||||||||
22 | The following month, the process would be repeated, and the April forecast in column F would be copied over ot column P. | ||||||||||||||||||||||||||||||||
23 | |||||||||||||||||||||||||||||||||
24 | |||||||||||||||||||||||||||||||||
25 | Goal 2: | There will be exact duplicates of this sheet (e.g. Sheet1) within the same workbook/file with different data. | |||||||||||||||||||||||||||||||
26 | How would you be able to create a macro/vba for each tab to perform the same function of copying over March > column P for each tab so you execute it once for the entire workbook? | ||||||||||||||||||||||||||||||||
Sheet1 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
P6 | P6 | =C1 |
P7 | P7 | =D1 |
Cells with Data Validation | ||
---|---|---|
Cell | Allow | Criteria |
C1 | List | =MonthNum |
D1 | List | =Month |