VBA copy and paste rows based on a cell value as a integer

[Valo]

Hello,

I got a macro button to copy a row and paste it right below, I have to press the button numerous of time to get a [x] amount of rows and have to manually delete some rows. Right now what I am trying to do is to Copy a row and Paste it below based on a Call value.

Example:
I want to Copy the Row of Week 2 (Cell A20:H20) and paste it below Week 2 with the amount based on G13.

Need help on this, thanks!!

Sub CopyPasteRows_pl()
         
    'Disable following Excel properties, whilst macro runs
     With Application
        .Calculation = xlCalculationManual
        .EnableEvents = False
        .ScreenUpdating = False
     End With

        'Declare object variables
         Dim i As Integer
         Dim r As Integer


        'Activate sheet
         Sheets("Profit-Loss Calc.").Select
         
            'Loop through the numbers of rows down the column
             i = Range("A19").End(xlDown).Row

                For r = 2 To i

                    If Range("A" & r) = "2" Then

                        Range("A" & r).EntireRow.Copy
                        Range("A" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial
                        
                        'Need to ad the amount based on Cell G13 and if possible delete the button
                        'So that the Macro is fully funcional based on Cell G13
                        
                    End If
                
                'Move the next row down the column
                 Next
        
    'Re-enable Excel properties once macro has run
     With Application
        .Calculation = xlCalculationAutomatic
        .EnableEvents = True
        .ScreenUpdating = True
     End With
     
     
End Sub

 

[maabadi]

What Means Based Cell G13?
Please upload your example file & Desired Results with XL2BB ADDIN (Preferable) OR upload it at free uploading site e.g. www.dropbox.com or googledrive or onedrive and insert link here.

 

[Valo]

What Means Based Cell G13?
Please upload your example file & Desired Results with XL2BB ADDIN (Preferable) OR upload it at free uploading site e.g. www.dropbox.com or googledrive or onedrive and insert link here.

What i mean by based Cell G13:
The amount of rows that should be added is based on the number value in that cell

Trade Journal.xlsm
	A	B	C	D	E	F	G	H	I							P
1
2
3	Data
4	Starting Account Balance	[€]	€ 1.000,00
5	Risk	[%]	2,00%					TO DO:
6	Max SL	[pips]	12					Adding rows based on a variable number
7	Min TP	[pips]	75					Winrate %
8	R/R ratio		6,25					Deposit after [x] weeks
9								Deposit amount
10	Deposit after [x] weeks		4
11	Deposit amount	[€]	€ 200,00
12
13	Max Lot-Size	[Yes/No]	Yes		Amount of Weeks		5	Minimum amount of 2
14	If Yes, then	[LOT-Size]	10,00		Winrate		40%
15
16
17	Weeks	Account Balance	LOT-size	R/R per pip	Risk	Reward	Win/Loss	New Balance
18		[€]		[€]	[€]	[€]	[W/L]	[€]
19	1	€ 1.000,00	0,16	€ 1,60	€ 19,20	€ 120,00	W	€ 1.120,00
20	2	€ 1.120,00	0,18	€ 1,80	€ 21,60	€ 135,00	W	€ 1.255,00
21
22
Profit-Loss Calc.

Cell Formulas
Range	Formula
C8	C8	=C7/C6
B19	B19	=C4
C19:C20	C19	=IF($C$13=$N$7,ROUNDDOWN((B19*$C$5)/($C$6*$L$6)*$K$6,2),MIN(ROUNDDOWN((B19*$C$5)/($C$6*$L$6)*$K$6,2),$C$14))
D19:D20	D19	=(C19/$K$6)*$L$6
E19:E20	E19	=D19*$C$6
F19:F20	F19	=D19*$C$7
A20	A20	=A19+1
B20	B20	=H19
H19:H20	H19	=IFS(G19=$O$6,B19+F19,G19=$O$7,B19-E19)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
G19:G34	Cell Value	contains "W"	text	NO
G19:G34	Cell Value	contains "L"	text	NO

Cells with Data Validation
Cell	Allow	Criteria
C13	List	=$N$6:$N$7

 

[Valo]

What Means Based Cell G13?
Please upload your example file & Desired Results with XL2BB ADDIN (Preferable) OR upload it at free uploading site e.g. www.dropbox.com or googledrive or onedrive and insert link here.

If i change the value on cell G13, for example from 5 to 17, then the amount of weeks (where only 2 weeks are calculated) should calculate 17 weeks instead of the 2 weeks.

 

[maabadi]

Try This:

Sub ADDWeeks()
Dim i As Long, Lr As Long, K As Long
Lr = Range("A" & Rows.Count).End(xlUp).Row
K = Range("G13").Value - Application.WorksheetFunction.Max(Range("A19:A" & Lr).Value)

Range("A" & Lr & ":H" & Lr).AutoFill Destination:=Range("A" & Lr & ":H" & Lr + K)

End Sub

 

[Valo]

Try This:

Sub ADDWeeks()
Dim i As Long, Lr As Long, K As Long
Lr = Range("A" & Rows.Count).End(xlUp).Row
K = Range("G13").Value - Application.WorksheetFunction.Max(Range("A19:A" & Lr).Value)

Range("A" & Lr & ":H" & Lr).AutoFill Destination:=Range("A" & Lr & ":H" & Lr + K)

End Sub

Yes it Works Thank you very much!

 

[maabadi]

You're Welcome & Thanks for Feedback.