Excel VBA adding lines and copy formulas

[jacqu]

I have a template worksheet built with formulas that I regularly copy and modify its size to fit necessary input- meaning I add/delete rows and copy formulas as needed. I have finally decided to develop a macro so it's easier for my coworkers to modify. It's a fairly complex set up that's difficult to explain, but I'll try my best. Basically, the worksheet is made up of 2 tables, Table A and Table B. Table A is where most of the customer's data is input. Table B draws from that data to run its own calculations and Table A runs calculations drawing from the input data and also Table B.

I use 6 rows for my template plus a total line in table A that isn't needed for table B, but the number of rows needed varies by customer. Rows from each table correspond to each other (first row of Table A matches to the first row of Table B and so on). So, if I add rows in table A, I also need to add rows in table B. Currently when I add rows, so as not to mess up my total line in Table A, I insert them above the Last Line of data in each table. After creating blank rows in each table, using the Fill Handle, I copy the formulas down into the new rows.

Starting with a macro I found under a similar thread, I have gotten it to work as far as inserting lines in Table A and Table B and copying one of the lines of Table A formulas into the new blank Table A line successfully. The problem comes when copying the formulas in Table B- it usually just ends up copying the formulas from Table B into Table A. I can guess where the problems are coming from, but I don't know enough VBA to fix it. Can anyone help me ?

I named some of the relevant cells to keep the macro more dynamic since by running the macro, it changes the row value of the last line and it's intended that the macro can be used more than once on the same worksheet.
LastLine = first cell of the last row in Table A; EndLastLine = last cell of the last row in Table A; MPLastLine = first cell of the last row in Table B

Sub Test()
    Range("LastLine").Select
    Dim AR As Long 'Active Row
    Dim r As Variant
    AR = ActiveCell.Row
    r = Application.InputBox(Prompt:="How may rows?", Type:=1)
    If r = False Then Exit Sub
    ActiveCell.Resize(r, 1).EntireRow.Insert
    Range("MPLastLine").Select
    ActiveCell.Resize(r, 1).EntireRow.Insert
    Range("LastLine").Select
    Range("LastLine:EndLastLine").Copy Range("A" & AR & ":A" & AR + r - 1)


End Sub

 

[Kamolga]

Hi,

If both tables are not on the same sheet, this will not work but otherwise you can try this (on a testing copy):

Sub Test()
    Range("LastLine").Select
    Dim AR As Long 'Active Row
    Dim r As Variant
    AR = ActiveCell.Row
    r = Application.InputBox(Prompt:="How may rows?", Type:=1)
    If r = False Then Exit Sub
    ActiveCell.Resize(r, 1).EntireRow.Insert
    Range("MPLastLine").Select
    ActiveCell.Resize(r, 1).EntireRow.Insert
    Range("LastLine:EndLastLine").Copy
    'if pasting is r lines below, then add Range("MPLastLine").Select here
    Activesheet.paste
End Sub

 

[jacqu]

Both tables are on the same worksheet, and to an extent your suggestion is getting me closer. When i put your code in, it creates the 2 new lines (1 in table A and 1 in table B), then it copies the line from table A and pastes it in both Table A & B. What it needs to do is copy a line from table A and paste that on the new table A line, then copy a line from table B and paste in the new table B line. I forgot to supply a range from Table B that could be copied, it would be "MPLastLine:EndMPLastLine"

Hi,

If both tables are not on the same sheet, this will not work but otherwise you can try this (on a testing copy):

Sub Test()
    Range("LastLine").Select
    Dim AR As Long 'Active Row
    Dim r As Variant
    AR = ActiveCell.Row
    r = Application.InputBox(Prompt:="How may rows?", Type:=1)
    If r = False Then Exit Sub
    ActiveCell.Resize(r, 1).EntireRow.Insert
    Range("MPLastLine").Select
    ActiveCell.Resize(r, 1).EntireRow.Insert
    Range("LastLine:EndLastLine").Copy
    'if pasting is r lines below, then add Range("MPLastLine").Select here
    Activesheet.paste
End Sub

 

[jacqu]

I had to play around with a bunch of different ideas but I finally got it to work. I know there are ways to simplify and using AutoFill in code isn't everyone's favorite, but it worked. I'm open to suggestions for improvement, but I'll post my code here if someone is looking for a similar solution.

Sub Test()
    Range("LastLine").Select
    Dim AR As Long 'Active Row
    Dim r As Variant
    AR = ActiveCell.Row
    r = Application.InputBox(Prompt:="How may rows?", Type:=1)
    If r = False Then Exit Sub
    ActiveCell.Resize(r, 1).EntireRow.Insert
    Range("MPLastLine").Select
    ActiveCell.Resize(r, 1).EntireRow.Insert
    Range("E6:P6").Select
    Selection.AutoFill Destination:=Range("E6:EndLastLine"), Type:=xlFillDefault
    Range("MPCopyLine:EndMPCopyLine").Select
    Selection.Copy
    Range("MPLastLine").Offset(-r).Resize(r, 8).Select
    ThisWorkbook.ActiveSheet.Paste


End Sub