VBA: Range on Columns and Rows

1dwn5up

New Member
Joined
Dec 8, 2023
Messages
1
Office Version
  1. 365
Platform
  1. Windows
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

VBA Range Columns and Rows Example.xlsm
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAE
12FeburaryDrop-down menu for cells C1 & D1
2
31January
4ActualActualForecastForecastForecastForecastForecastForecastForecastForecastForecastForecastForecast2Feburary
53March
61234567891011122Formula based on cell C14April
7JanuaryFeburaryMarchAprilMayJuneJulyAugustSeptemberOctoberNovemberDecemberFeburaryFormula based on cell D15May
8Account 1101115101010101010101010156June
9Account 2202125101010101010101010257July
10Account 3303135101010101010101010358August
11Account 4404145101010101010101010459September
12Account 55051551010101010101010105510October
13Account 66061651010101010101010106511November
14Account 77071751010101010101010107512December
15Account 880818510101010101010101085
16Account 990919510101010101010101095
17Account 10100101105101010101010101010105
18
19
20Goal 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).
21The March forecast values in column E will then be overwritten with actuals.
22The following month, the process would be repeated, and the April forecast in column F would be copied over ot column P.
23
24
25Goal 2:There will be exact duplicates of this sheet (e.g. Sheet1) within the same workbook/file with different data.
26How 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
RangeFormula
P6P6=C1
P7P7=D1
Cells with Data Validation
CellAllowCriteria
C1List=MonthNum
D1List=Month
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
Hello Harry and welcome to the forum :)
Personally, I wouldn't use VBA when you could achieve the same result with some inbuilt 365 functions. Please consider the following alternative, using the Filter function. If all your sheets are the same layout/format then you can simply copy the first sheet - the formulas will refer to the same ranges in each sheet. I've assumed that when you select December that you may want January's forecast? Anyhow, please note the formulas in P6, P7 and P8.

Harry.xlsx
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABAC
12Feburary
2
31January
4ActualActualForecastForecastForecastForecastForecastForecastForecastForecastForecastForecastForecast2Feburary
53March
612345678910111234April
7JanuaryFeburaryMarchAprilMayJuneJulyAugustSeptemberOctoberNovemberDecemberMarch5May
8Account 1101115101010101010101010156June
9Account 2202125101010101010101010257July
10Account 3303135101010101010101010358August
11Account 4404145101010101010101010459September
12Account 55051551010101010101010105510October
13Account 66061651010101010101010106511November
14Account 77071751010101010101010107512December
15Account 880818510101010101010101085
16Account 990919510101010101010101095
17Account 10100101105101010101010101010105
18
Sheet1
Cell Formulas
RangeFormula
P6P6=IF(C1=12,1,C1+1)
P7P7=IF(D1="December","January",OFFSET(INDEX($C$7:$N$7,MATCH($D$1,$C$7:$N$7,0)),0,1,1,1))
P8:P17P8=FILTER($C$8:$N$17,$C$7:$N$7=$P$7,"")
Dynamic array formulas.
Cells with Data Validation
CellAllowCriteria
C1List=$AB$3:$AB$14
D1List=$AC$3:$AC$14
 
Upvote 0

Forum statistics

Threads
1,223,227
Messages
6,170,848
Members
452,361
Latest member
d3ad3y3

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top