Adding rows & copying formulas above

Esco · Dec 20th 2021

I have a summary sheet which gets information feed to the cells from other sheets.

The attached example file does include the other sheets.

As this file has several users, which have beginner excel skills. Their only job is to fill in the "Paid date".

The created rows will come to an end at some point, so I wanted to create a button, which will ask the user

how many rows they would like to add. To make the file easy to use.

This far I came and now I get a warning: Run-time error '1004', Method 'Range' of object_Global' failed.

And the code does not copy formulas from row 4.

VBA code so far:

Code

'adding rows to a sheet
Sub Add_rows()
Dim iRow As Long
Dim iCount As Long
Dim i As Long
Dim SourceCell As Range
Dim FillRange As Range
iCount = InputBox(Prompt:="How many rows?")
'should I ask where to insert the new rows or can
'it be inserted automatically before the grey row 34?
iRow = InputBox _
(Prompt:="Row nbr where the latest reference is? :")
For i = 1 To iCount
Rows(iRow).EntireRow.Insert
Next i
'Add formula to new rows - which does not work!
Set SourceCell = ActiveSheet.Cells(iRow - 1, Range("A4;H4"))
Set FillRange = ActiveSheet.Range("A4;H4")
SourceCell.AutoFill Destination:=FillRange
End Sub

Display More

My questions:

1) Is it possible to skip asking "where to insert the new rows" -> insert the new rows straight after latest cell on column A, where is a value.

2) Copy the formulas from the first row number 4, to the new rows?

I'm really a beginner with VBA, so any help would be just wonderful!

ps. the file attached.

**royUK** · Dec 20th 2021

Welcome to the Forum. Please read the Forum Rules to understand how the Forum works and why I have added Code Tags to your post

All VBA code posted in the forum must be wrapped in code tags, which you omitted, including single-line code snippets.Be sure to use them in future posts.

How to use code tags

Just highlight all of the code and press the <> in the post menu above button to add the code tags.

Thanks.

**royUK** · Dec 20th 2021

That code is totally unnecessary.

I would convert the data to a Table, then formatting and formulas will automatically copy down.

Note: this is possible to set up without Tables, but Tables are more useful in many ways.

Read this

Overview of Excel Tables.

Data in Excel

Esco · Dec 20th 2021

Yes it can be done with Tables but then the other VBA's does not feed info to the cells from the other sheets. They feed the data after the Table.

Just FYI: here is the code for that.

Code

Sub AddSheet()
 Dim strName As String
 Dim intNum As Integer, intLastNum As Integer
 Dim wsh As Worksheet
 Dim lngRow
 Dim lngStartNumber As Long
 
 With Worksheets("Summary")
    lngStartNumber = .Range("B1").Value
    .Range("B1") = lngStartNumber + 1
End With
     
     strName = " Com- " & Format(lngStartNumber, "000")
    'Create the new Invoice sheet
    Worksheets("TemplateSheet").Copy After:=Worksheets(Worksheets.Count)
 Set wsh = Worksheets(Worksheets.Count)
 wsh.Name = strName
 'defines invoice number on cell K3
  Range("K3").Value = strName
 
 With Worksheets("Yhteenveto")
 lngRow = .Range("A65536").End(xlUp).Row + 1
 .Range("B" & lngRow).Formula = "='" & strName & "'!$K$4"
 .Range("C" & lngRow).Formula = "='" & strName & "'!$C$3"
 .Range("D" & lngRow).Formula = "='" & strName & "'!$M$33"
 .Range("E" & lngRow).Formula = "='" & strName & "'!$K$6"
 .Hyperlinks.Add Anchor:=.Range("A" & lngRow), Address:="", _
 SubAddress:="'" & strName & "'!A1", TextToDisplay:=strName
 End With
 End Sub

Display More

**royUK** · Dec 20th 2021

Attach an example workbook.

If you are using code then you need to adapt the code to work with a Table.

Esco · Jan 4th 2022

Progress after few days:

I converted the Summary-sheets Range of Data as Table but it stopped working.

I haven't worked with Tables and VBA before. Got it working without a Table but realized Table would be so much better solution, but can not execute it.

How can I

insert a Row straight after Table (on summary sheet) and

copy the formulas as there is now (to import data from the recently added Invoice).

See attached excel, perhaps it is easier to understand?

**royUK** · Jan 5th 2022

This will add a new row

Code

Sub AddNewRow()
    Dim oWs As Worksheet
    Dim oTbl As ListObject
    Dim oNewRow As ListRow
    
    Set oWs = ThisWorkbook.Worksheets("Summary")
    Set oTbl = oWs.ListObjects(1)
    
    
    Set oTbl = oWs.ListObjects(1)
    ''///AlwaysInsert:=True ensures data and formulas are copied down
    Set oNewRow = oTbl.ListRows.Add(AlwaysInsert:=True)
    With oNewRow
        .Range.Cells(1, 1).Value = "data here"
    End With
End Sub

Display More

Esco · Jan 5th 2022

Thank you. It certainly does add a new row where it should do.

I realize that my request was not 100% correct. As adding a new row copies the formulas down, so that all the data on the Table is exactly the same.

How could the code be altered so that it would keep the existing data on the Table? Perhaps just inserting a new row, where it is and not copying formulas, as the code copies new data from the generated invoice at the end.

Code

With oNewrow.Range
         .Cells(2).Formula = "='" & strName & "'!$A$7"
        .Cells(3).Formula = "='" & strName & "'!$K$4"
        .Cells(4).Formula = "='" & strName & "'!$K$6"
        .Cells(5).Formula = "='" & strName & "'!$M$34"
        .Cells(6).Formula = "='" & strName & "'!$I$40"
        .Parent.Hyperlinks.Add Anchor:=.Cells(1), Address:="", _
                                      SubAddress:="'" & strName & "'!A1", TextToDisplay:=strName

My bad, I'm sorry but really greatful for your help and input already!

Esco · Jan 5th 2022

I just realized my Topic should have been: Adding a row straight after Header on a Table.

**royUK** · Jan 6th 2022

You can add the position in the Table like this

Code

Set oNewRow = oTbl.ListRows.Add (1)

Adding rows & copying formulas above

Participate now!

Share

Tags