VBA code to insert lines and copy formulas

dracer

Board Regular
Joined
Jul 30, 2014
Messages
71
Hi there,

I'm hoping that someone can send me VBA code to insert lines in a file. I have a cell A4 that tells me how many customers I'm missing (with a formula). I want it to insert the number of rows after the last cell with a value column C based on this formula. e.g. the value is 4 and C75 is the last cell in that column with a value so I would want it to insert 4 rows after row 75. Additionally since the last row is row 75 I want to also copy cells B75 to I75 down 4 rows.

To summarize -
1)Insert number of rows after the last row that has a value in column C based on a formula in Cell A4
2) Copy the cells in that last row from column B to column I down the same number or rows.

Thanks so much
 

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.
Try:
Code:
Sub insertRows()
    Application.ScreenUpdating = False
    Dim bottomC As Long
    bottomC = Range("C" & Rows.Count).End(xlUp).Row
    Range("B" & bottomC & ":I" & bottomC).Copy
    Cells(bottomC + 1, 2).Resize(Range("A4").Value).Insert
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
End Sub
 
Upvote 0
I have another minor issue. Right now if the value in A4 is zero than the macro stops because there would be no lines to insert. What I would like it to do is that if It sees the value is zero then run xxxx code (Which i have and can insert) otherwise run the below. Can you help? Thanks

Range("B" & bottomC & ":I" & bottomC).Copy
Cells(bottomC + 1, 2).Resize(Range("A4").Value).Insert
Application.CutCopyMode = False
Application.ScreenUpdating = True
 
Upvote 0
Try:
Code:
Sub insertRows()
    Application.ScreenUpdating = False
    Dim bottomC As Long
    bottomC = Range("C" & Rows.Count).End(xlUp).Row
    If Range("A4") = 0 Then
        'your code here
    Else
        Range("B" & bottomC & ":I" & bottomC).Copy
        Cells(bottomC + 1, 2).Resize(Range("A4").Value).Insert
    End If
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
End Sub
 
Upvote 0

Forum statistics

Threads
1,213,526
Messages
6,114,136
Members
448,551
Latest member
Sienna de Souza

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