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

Valo

New Member
Joined
Jan 25, 2021
Messages
4
Office Version
  1. 2016
Platform
  1. Windows
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!!

VBA Code:
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
 

Attachments

  • Schermafbeelding 2021-01-25 100902.png
    Schermafbeelding 2021-01-25 100902.png
    31.2 KB · Views: 12

Some videos you may like

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
1,614
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
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

New Member
Joined
Jan 25, 2021
Messages
4
Office Version
  1. 2016
Platform
  1. Windows
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
ABCDEFGHIP
1
2
3Data
4Starting Account Balance[€]€ 1.000,00
5Risk [%]2,00%TO DO:
6Max SL[pips]12Adding rows based on a variable number
7Min TP[pips]75Winrate %
8R/R ratio6,25Deposit after [x] weeks
9Deposit amount
10Deposit after [x] weeks4
11Deposit amount[€]€ 200,00
12
13Max Lot-Size[Yes/No]YesAmount of Weeks5Minimum amount of 2
14If Yes, then[LOT-Size]10,00Winrate40%
15
16
17WeeksAccount BalanceLOT-sizeR/R per pipRiskRewardWin/LossNew Balance
18[€][€][€][€][W/L][€]
191€ 1.000,000,16€ 1,60€ 19,20€ 120,00W€ 1.120,00
202€ 1.120,000,18€ 1,80€ 21,60€ 135,00W€ 1.255,00
21
22
Profit-Loss Calc.
Cell Formulas
RangeFormula
C8C8=C7/C6
B19B19=C4
C19:C20C19=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:D20D19=(C19/$K$6)*$L$6
E19:E20E19=D19*$C$6
F19:F20F19=D19*$C$7
A20A20=A19+1
B20B20=H19
H19:H20H19=IFS(G19=$O$6,B19+F19,G19=$O$7,B19-E19)
Cells with Conditional Formatting
CellConditionCell FormatStop If True
G19:G34Cell Valuecontains "W"textNO
G19:G34Cell Valuecontains "L"textNO
Cells with Data Validation
CellAllowCriteria
C13List=$N$6:$N$7
 

Valo

New Member
Joined
Jan 25, 2021
Messages
4
Office Version
  1. 2016
Platform
  1. Windows
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

Well-known Member
Joined
Oct 22, 2012
Messages
1,614
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows

ADVERTISEMENT

Try This:
VBA Code:
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
 
Solution

Valo

New Member
Joined
Jan 25, 2021
Messages
4
Office Version
  1. 2016
Platform
  1. Windows
Try This:
VBA Code:
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!(y)
 

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
1,614
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
You're Welcome & Thanks for Feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,370
Messages
5,624,294
Members
416,018
Latest member
mirceaon

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
Top