VBA to Copy/Paste Formula Into Next Cell (Column)

[Gerwe1988]

Hello everyone,

I'm new to macros and need some assistance with establishing a code for my forecast file. The macro will scan row 3 for the current "reporting" month, to decide which column to update (AS). The macro will then copy & paste the prior months formula from one cell to next, into the current "reporting" month. The prior months formula will need to be hard coded. There are certain rows that need to be updated in this process (Rows 16,17, 27, 28). For example, row 16 "New patients", the prior months formula (Jan-22, AR16), will be copied and pasted into the next column (AS16). The prior months formula (AR16) will be hard coded. Thanks in advance!

 

[airforceone]

many are willing to help if you could give us sample data to work with preferably in xl2bb format, formulas and possible output (if you may)

 

[Gerwe1988]

many are willing to help if you could give us sample data to work with preferably in xl2bb format, formulas and possible output (if you may)

ADS Forecast - 2021 KLF_temp(AutoRecovered)_check.xlsm
	B	C	D	E	F	G	H	I	J	K
1
2
3						Reporting
4		Act	Act	Act	ACT	ACT	Fore	Fore	Fore	Fore
5		Oct-21	Nov-21	Dec-21	Jan-22	Feb-22	Mar-22	Apr-22	May-22	Jun-22
6	Patient Assumptions
7	Number of Sales Reps	27	27	26	26	26	26	26	26	26
8	New Patients / Rep / Month	108	109	134	88	93	88	95	96	104
9	Inbound Marketing Patients (manual add)
10	Inbound Med Adv (manual add)						350	350	350	350
11	Inbound Pharmacy Patients (manual add)	270	260	231	358	231	298	341	286	324
12	Churn Rate	-1.5%	-1.6%	-1.7%	-1.8%	-1.7%	-1.7%	-1.7%	-1.7%	-1.7%
13					2,282	2,409	2,284	2,464	2,486	2,702
14	Patient Build
15	Beginning Population	45,572	48,079	50,540	53,381	55,070	56,759	58,711	60,852	62,924
16	New Patients	3,199	3,208	3,720	2,640	2,640	2,932	3,155	3,123	3,376
17	Churn	(692)	(747)	(879)	(951)	(951)	(980)	(1,014)	(1,051)	(1,087)
18	Ending Patient Population	48,079	50,540	53,381	55,070	56,759	58,711	60,852	62,924	65,213
19					35,058
20	Ending Patients by Payor
21	MEDICARE	33,660	34,875	36,111	35,024	36,735	38,442	40,304	42,095	44,061
22	OTHER	14,419	15,665	17,270	14,635	18,335	18,579	18,859	19,140	19,463
23
24	Sensors
25
26	New Units by Payor Group
27	MEDICARE	10,913	11,113	11,348	7,748	7,748	-	-	-	-
28	OTHER	8,421	8,030	10,230	7,879	7,879	-	-	-	-
Sheet1

Cell Formulas
Range	Formula
F3:G3	F3	=IF(F5=Cover!$C$41,"Reporting"," ")
F5:G5	F5	=EOMONTH(E5,1)
F7:G7	F7	='Dexcom CGM'!AR7
F8:G8	F8	=(F16-F11)/F7
F12:G12	F12	=IFERROR(F17/F15,"n/a")
F13:G13	F13	=F7*F8
F15:G15	F15	=E18
G16	G16	=SUMIF('Attrition Support (Libre)'!$A:$A,"<>"&"*Grand Total*",'Attrition Support (Libre)'!$J:$J)
G17	G17	=-SUMIF('Attrition Support (Libre)'!$A:$A,"<>"&"*Grand Total*",'Attrition Support (Libre)'!$K:$K)
F18:G18	F18	=SUM(F15:F17)
G27	G27	=SUMIFS('Attrition Support (Libre)'!$O:$O,'Attrition Support (Libre)'!$A:$A,"<>"&"*Grand Total*",'Attrition Support (Libre)'!$H:$H,"*Medicare*")
G28	G28	=SUMIFS('Attrition Support (Libre)'!$O:$O,'Attrition Support (Libre)'!$A:$A,"<>"&"*Grand Total*",'Attrition Support (Libre)'!$H:$H,"*Other*")

Named Ranges
Name	Refers To	Cells
'Attrition Support (Libre)'!_FilterDatabase	='Attrition Support (Libre)'!$A$2:$P$1395	G27:G28, G16:G17

 

[airforceone]

there are missing sheet and the formula to copy is not that clear, but kindly try the following code if it helps...

Sub Gerwe1988()
    
    Dim rFind As Range, iCharNum As Integer, LColChar As String
    With Range("A3:J3")
        Set rFind = .Find(What:="Reporting", LookAt:=xlWhole, MatchCase:=False, SearchFormat:=False)
        If Not rFind Is Nothing Then
           iCharNum = rFind.Column
            LColChar = Split(Cells(1, iCharNum).Address, "$")(1)
        End If
    End With
    Range("AR8").Formula = Range(Last_Char00 & "8").Formula
    Range("AR13").Formula = Range(Last_Char00 & "13").Formula
    Range("AR15").Formula = Range(Last_Char00 & "15").Formula
    Range("AR18").Formula = Range(Last_Char00 & "18").Formula
    
End Sub     'End of Gerwe1988

 

[airforceone]

Updated Code [TESTED]

Sub Gerwe1988()
    
    Dim rFind As Range, iCharNum As Integer, LColChar As String
    With Range("A3:J3")
        Set rFind = .Find(What:="Reporting", LookAt:=xlWhole, MatchCase:=False, SearchFormat:=False)
        If Not rFind Is Nothing Then
           iCharNum = rFind.Column
            LColChar = Split(Cells(1, iCharNum).Address, "$")(1)
        End If
    End With
    Range("AR8").Formula = Range(LColChar & "8").Formula
    Range("AR13").Formula = Range(LColChar & "13").Formula
    Range("AR15").Formula = Range(LColChar & "15").Formula
    Range("AR18").Formula = Range(LColChar & "18").Formula
    
End Sub     'End of Gerwe1988

 

[Gerwe1988]

Updated Code [TESTED]

Sub Gerwe1988()
   
    Dim rFind As Range, iCharNum As Integer, LColChar As String
    With Range("A3:J3")
        Set rFind = .Find(What:="Reporting", LookAt:=xlWhole, MatchCase:=False, SearchFormat:=False)
        If Not rFind Is Nothing Then
           iCharNum = rFind.Column
            LColChar = Split(Cells(1, iCharNum).Address, "$")(1)
        End If
    End With
    Range("AR8").Formula = Range(LColChar & "8").Formula
    Range("AR13").Formula = Range(LColChar & "13").Formula
    Range("AR15").Formula = Range(LColChar & "15").Formula
    Range("AR18").Formula = Range(LColChar & "18").Formula
   
End Sub     'End of Gerwe1988

Appreciate the help! I only need to copy and paste the formulas in 16, 17, 27, 28. I'm receiving the following error when I run the macro.

 

[airforceone]

Appreciate the help! I only need to copy and paste the formulas in 16, 17, 27, 28. I'm receiving the following error when I run the macro.

can you tell me what line is the problem, on my end its working properly.
and could you follow "Fix 2" on this link Runtime Error Fix, might help.

 

[Gerwe1988]

can you tell me what line is the problem, on my end its working properly.
and could you follow "Fix 2" on this link Runtime Error Fix, might help.

Tried "Fix 2" and no luck. The line that error is occurred is on the following: Range("AR8").Formula = Range(LColChar & "8").Formula

 

[airforceone]

kindly try my sample file based on your data Sample Worksheet (in Macro Enabled Format)

 

[Gerwe1988]

kindly try my sample file based on your data Sample Worksheet (in Macro Enabled Format)

Works with your file. How would you specify a sheet or tab within this code? Is it possible to paste the code in the next column (month), rather than a specific location or cell? Also, how would you copy, paste values (hardcode) the reporting month column so that the formula isn't active in two columns?