Row Insertion

arehman1289

New Member
Joined
Dec 10, 2020
Messages
22
Office Version
  1. 365
Platform
  1. Windows
Hello
I have a sheet in which I ask the user to input the number of branches. Based on this input Id like to write a VBA that could dublicate the next rows and formulas in it. As an example in the attached image, if the user enters 3, i would like to duplicate the next 4 rows (highligted in yellow), 3 times.

I have very basic skills at the moment in the developer TAB and I am learning at the moment. Any help would be highly appreciated.

Thank you
 

Attachments

  • Capture.PNG
    Capture.PNG
    5.1 KB · Views: 9

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
Welcome to the Board!

Can you supply more details, such as:

- What cell are they entering this number into (cell address, please)?

- Will there always be exactly four rows to copy, and will they always be in the same place (right below the cell where you are entering the number of times to copy)?
Or is this an ever-growing list, and you will want it to copy after the last row in the list (which will be changing)?
 
Upvote 0
Thank you for the reply.

So the user would enter in cell B1, and in the case of the picture attached, the four rows below would be replicated exactly below the first row. There will be exactly four rows and exactly below the first row. The list wont be ever growing, just based on the initial input from the user. So the output would like something like the attached pic.

Thanks so much
 

Attachments

  • Capture1.PNG
    Capture1.PNG
    14.8 KB · Views: 2
Upvote 0
Right-click on the sheet tab name at the bottom of the screen, select "View Code", and paste this code in the VB Editor window tht pops up:
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)

    Dim i As Long
    Dim nr As Long

'   Exit if multiple cells updating simultaneously
    If Target.CountLarge > 1 Then Exit Sub
    
'   Only run if cell B1 updated
    If Target.Address = Range("B1").Address Then
'       Only run if numeric value entered
        If IsNumeric(Target.Value) And (Target.Value > 0) Then
            Application.ScreenUpdating = False
            Application.EnableEvents = False
'           Set default value of next row
            nr = 6
'           Loop through number of times to copy
            For i = 1 To Int(Target.Value)
'               Copy values from B2:C5 to next range
                Range("B2:C5").Copy Cells(nr, "B")
'               Increment next start by 4 rows
                nr = nr + 4
            Next i
            Application.EnableEvents = True
            Application.ScreenUpdating = True
        End If
    End If
    
End Sub
Now, whenever you enter a number in cell B1, it will automatically copy the range B2:C5 down that many times.
 
Upvote 0
Solution
Thank you so so much. It works perfectly. Very gratefil to you Joe.

To study the functions in VBA excel and syntax is there any recommended source ? Starting from the basics of course.
 
Upvote 0
There are lots of resources you can use. MrExcel has written a variety of books, so you may want to investigate some of those: The MrExcel Store - Welcome to The MrExcel Store
There also a host of online tutorials and videos that can be found with Google searches.
 
Upvote 0
Hello

Because of your kind help I progressed on this sheet and completed much of it, just encountering two obstacles.

1. I am copying cells from a row (on Another Sheet 1) with formulas referenced to other sheets as follows:

=VLOOKUP('Another Sheet'!C10,'Another Sheet2'!$A$2:$B$11,2,FALSE)

When I copy it to a cell on the same sheet i get:

=VLOOKUP('Another Sheet'!C17,'Another Sheet2'!$A$2:$B$11,2,FALSE)

Instead of an increment of 7 that is C17, I would like to an increment of 15, how would I do this? Without VBA I would have used the offset function.

2. In the code above, whenever there is a change in value in cell B1, the sheet updates only on the first time. Is it possible to do it everytime there is a change in the value of B1 and undo the previos changes ?

Thank you
 
Upvote 0
Instead of an increment of 7 that is C17, I would like to an increment of 15, how would I do this? Without VBA I would have used the offset function.
You can go ahead and use the OFFSET function, and then VBA should pick that up when it is copying over.

In the code above, whenever there is a change in value in cell B1, the sheet updates only on the first time. Is it possible to do it everytime there is a change in the value of B1 and undo the previos changes ?
The code should run anytime there is a numeric change manually made to cell B1, unless for some reason the code is getting interrupted before the end and never reaches to lines of code that re-enable events.

I am not sure what you mean by "undo" the previous change. Can you explain exactly what you mean by this?
 
Upvote 0
I think I made a mistake by saying the offset function could be used. I would like to offset the cell reference in the formula not the value in a cell. As in the example above:

=VLOOKUP('Another Sheet'!C10,'Another Sheet2'!$A$2:$B$11,2,FALSE)

When I copy it to a cell on the same sheet i want C25 instead of C17t:

=VLOOKUP('Another Sheet'!C17,'Another Sheet2'!$A$2:$B$11,2,FALSE)

Secondly by undo, i meant that if i entered 4 the first time and then 2 the second time, it would delete the additional 2 sets of rows copy pasted in the first go. Im not sure if this is possible.
 
Upvote 0
=VLOOKUP('Another Sheet'!C10,'Another Sheet2'!$A$2:$B$11,2,FALSE)

When I copy it to a cell on the same sheet i want C25 instead of C17t:

=VLOOKUP('Another Sheet'!C17,'Another Sheet2'!$A$2:$B$11,2,FALSE)
What row is it copying FROM and what row is it copying TO?

Secondly by undo, i meant that if i entered 4 the first time and then 2 the second time, it would delete the additional 2 sets of rows copy pasted in the first go. Im not sure if this is possible.
By default, you cannot undo VBA code (without creating a special VBA Procedure to do that). Your best bet would be to have your VBA code store the last number it used (i.e. put "2" in some unused cell on the spreadsheet). Then you can program your VBA code when it first runs to look at this value, and delete that many rows (from the botttom-up) to start before running the rest of the code.

Note that these two questions are really "new" questions (outside the scope of your original question). You should post all "new" questions to "new threads". That way they appear as new unanswered questions and show up on the "Unanswered threads" listing that most people use to look for new, unanswered questions. Otherwise, most people will probably not see these new questions. So I would recommend if you still have questions about either of these two new items, to post them to their own new threads.

Also, if your original question was answered, we ask that you please please mark the appropriate reply as the solution, as described here: Mark as Solution

Thanks
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,236
Members
448,555
Latest member
RobertJones1986

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