Automate Copy Paste Formulas One Column Over and Hard Code Values In Original Column (w/ Mini Sheet)

M

Mundash

New Member
Joined
Jul 26, 2022
Messages
1
Office Version
  1. 365
Platform
  1. Windows
Anybody know how to automate the process of copying formulas from one column to the next, then hard coding the values on the original column?

Each month it will move one column over (as each month represents a new column), the formulas will overwrite existing hard coded values in the new column. I tried recording a macro but it does not work since it is one column over each month. Any ideas? Thank you in advance!

Sample Data File_7.26.22.xlsx
ABCDEFGHIJKLMNOP
1
2
3Hard CodedHard CodedHard CodedHard CodedHard CodedFormulaNew month column overwrite hard coded values with formulaEach month the formulas move over one month Hard CodedHard CodedHard CodedHard CodedHard Coded
4123456789101112
7Product #1Jan-22Feb-22Mar-22Apr-22May-22Jun-22Jul-22Aug-22Sep-22Oct-22Nov-22Dec-22
8Product 1 Order Order5,3714,4125,1794,9874,7953,5505,3714,4125,1794,9874,7954,987
9 AA+AS At SeaShipping6437062282,0682,1832,1736437062282,0682,1832,068
10IT+TN In-TransitTransit8082,6111,2518041,8451,3108082,6111,2518041,845804
11PAPortWarehouse #12493451921,3104801802493451921,3104801,310
12PSStockWarehouse #236174427620315914361744276203159203
13Sum of MTLY WHSLWhStore Rtl Sales2,8483,0635,3024,5695,9605002,8483,0635,3024,5695,9604,569
14Sum of MTLY WHSLWhStore B2B420202070042020202
15Sum of MTLY WHSLWhStore Emp. Purch0010150200001015015
16Sum of MTLY RTLRtStore Rtl Sales3,1322,8665,4254,2336,1534003,1322,8665,4254,2336,1534,233
17Sum of MTLY RTLRtStore B2B01,05702080001,0570202
18Sum of MTLY RTLRtStore Emp. Purch200250135120275100200250135120275120
19Sum of Dealer StockRtl Sales458656533869675300458656533869675869
20Sum of Dealer StockStockB2B1,0550002119001,0550002110
21Sum of Dealer StockEmp. Purch6626126006626126
22
23Product #2Jan-22Feb-22Mar-22Apr-22May-22Jun-22
24
25Product 2 Production Order15,72918,79823,01828,7703,4371,25615,72918,79823,01828,7703,43723,018
26 AA+AS WaterShipping5,5723,1992,0166,1738,7531,7905,5723,1992,0166,1738,7532,016
27IT+TNTransitTransit6,2348,1186,7522,5096,2638996,2348,1186,7522,5096,2636,752
28PAWarehouse #1Warehouse #11,8871,7341,3771,8951,4564211,8871,7341,3771,8951,4561,377
29PSWarehouse #2Warehouse #28691,6983,4101,922568408691,6983,4101,9225683,410
30Sum of MTLY WHSLWhStore Rtl Sales9,10715,23216,44817,36515,7735009,10715,23216,44817,36515,77316,448
31Sum of MTLY WHSLWhStore B2B1,9451,5361,4234056757001,9451,5361,4234056751,423
32Sum of MTLY WHSLWhStore Emp. Purch6210384200621038410
33Sum of MTLY RTLRtStore Rtl Sales10,44813,37317,71217,41716,30440010,44813,37317,71217,41716,30417,712
34Sum of MTLY RTLRtStore B2B5752,2317121,3471,3438005752,2317121,3471,343712
35Sum of MTLY RTLRtStore Emp. Purch21283121100212831218
36Sum of Dealer StockRtl Sales1,8343,6922,4282,3771,8453001,8343,6922,4282,3771,8452,428
37Sum of Dealer StockStockB2B2,2311,6752,5801,6369739002,2311,6752,5801,6369732,580
38Sum of Dealer StockEmp. Purch1710215136001710215132
39
22CY RDOWN
Cell Formulas
RangeFormula
I4:O4I4=+H4+1
I8,I25I8=INDEX(CY!$D$6:$O$30,MATCH($A8,CY!$B$6:$B$30,0),MATCH(I$4,CY!$D$4:$O$4,0))
I9,I26I9=INDEX(Top!$L$4:$O$30,MATCH($A8,Top!$J$4:$J$30,0),MATCH($A9,Top!$L$3:$O$3,0))
I10,I27I10=INDEX(Top!$L$4:$O$30,MATCH($A8,Top!$J$4:$J$30,0),MATCH($A10,Top!$L$3:$O$3,0))
I11,I28I11=INDEX(Top!$L$4:$O$30,MATCH($A8,Top!$J$4:$J$30,0),MATCH($A11,Top!$L$3:$O$3,0))
I12,I29I12=INDEX(Top!$L$4:$O$30,MATCH($A8,Top!$J$4:$J$30,0),MATCH($A12,Top!$L$3:$O$3,0))
I13,I30I13=INDEX('Bottom (CY)'!$D$7:$F$38,MATCH($A8,'Bottom (CY)'!$A$7:$A$38,0),MATCH($A13,'Bottom (CY)'!$D$6:$F$6,0))
I14,I31I14=INDEX('Bottom (CY)'!$D$90:$F$114,MATCH($A8,'Bottom (CY)'!$A$90:$A$114,0),MATCH($A14,'Bottom (CY)'!$D$89:$F$89,0))
I15,I32I15=INDEX('Bottom (CY)'!$D$45:$F$82,MATCH($A8,'Bottom (CY)'!$A$45:$A$82,0),MATCH($A15,'Bottom (CY)'!$D$44:$F$44,0))
I16,I33I16=INDEX('Bottom (CY)'!$D$7:$F$38,MATCH($A8,'Bottom (CY)'!$A$7:$A$38,0),MATCH($A16,'Bottom (CY)'!$D$6:$F$6,0))
I17,I34I17=INDEX('Bottom (CY)'!$D$90:$F$114,MATCH($A8,'Bottom (CY)'!$A$90:$A$114,0),MATCH($A17,'Bottom (CY)'!$D$89:$F$89,0))
I18,I35I18=INDEX('Bottom (CY)'!$D$45:$F$82,MATCH($A8,'Bottom (CY)'!$A$45:$A$82,0),MATCH($A18,'Bottom (CY)'!$D$44:$F$44,0))
I19,I36I19=INDEX('Bottom (CY)'!$D$7:$F$38,MATCH($A8,'Bottom (CY)'!$A$7:$A$38,0),MATCH($A19,'Bottom (CY)'!$D$6:$F$6,0))
I20,I37I20=INDEX('Bottom (CY)'!$D$90:$F$114,MATCH($A8,'Bottom (CY)'!$A$90:$A$114,0),MATCH($A20,'Bottom (CY)'!$D$89:$F$89,0))
I21,I38I21=INDEX('Bottom (CY)'!$D$45:$F$82,MATCH($A8,'Bottom (CY)'!$A$45:$A$82,0),MATCH($A21,'Bottom (CY)'!$D$44:$F$44,0))
I23I23=I$5
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
You must log in or register to reply here.

Similar threads

N
  • Solved
Dynamic formula to pull in column headers based on column values within a row
Replies
2
Views
243
n64kps
N
N
  • Solved
VBA auto fill in information from the list in one sheet to another sheet and save as the excel file.
Replies
5
Views
213
ngochien251088
N
T
  • Solved
Copy paste value when selecting from drop-down list
Replies
2
Views
608
tinezi
T
M
  • Question Question
Copy selected column data and paste into another sheet.
Replies
3
Views
340
motilulla
M
Unlucky
  • Solved
INDEX & MATCH
Replies
4
Views
689
Fluff
Fluff

Forum statistics

Threads
1,224,587
Messages
6,179,738
Members
452,940
Latest member
Lawrenceiow

Share this page

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