Insert New Row if needed

Hello,

First of all I have posted the same thread at mrexcel https://www.mrexcel.com/board/…ew-row-if-needed.1198559/

And here is my question and needed help.

First of all I need to say thank you to all of you that have helped me and write (tried and did) the below code.

The code is working perfectly!. What the code do is that it collect data from 3 or more sheets and insert them to another sheet in order to create an invoice. So far , so good.

The problem is that there is an alert if the inserted data are more than row 70 to end sub. Now there is the need to incremental add rows to invoice and not checking if they are more than 70.

Starts from Row 15 and add row if there is a need (ie retrieve more data from sheets and copy them). I have tried to make it happen using a tutorial from VBA Macro to Insert Row in Excel Based on https://www.exceldemy.com/exce…rt-row-based-on-criteria/ but no luck.

Also the row must have columns A B C and D has a formula (B*C) .Can someone please help to make this work?

You can find at the example workbook. The module needed to change is Invoice

Sub Invoice2()
Dim ws As Variant, sht As Variant
Dim i As Long, lr As Long, nr As Long, c As Long
Dim cell As Range
Dim N$
Dim ws1 As String
ws1 = ActiveSheet.Name
Application.ScreenUpdating = False
' Set array of worksheet names to copy from
ws = Array("Dimmer")
' Array of columns to check
sht = Array("D")
nr = 15
Sheets("FORMA").Range("A15:C70").ClearContents
' Loop through all sheets in sheets array
For i = LBound(ws) To UBound(ws)
' Loop through all columns in the column array
For c = LBound(sht) To UBound(sht)
' Find last row in column with data
With Sheets(ws(i))
lr = .Cells(Rows.Count, sht(c)).End(xlUp).Row
' Loop through all cells in column
For Each cell In .Range(.Cells(1, sht(c)), .Cells(lr, sht(c)))
' Check to see if value is numeric and not 0
If (IsNumeric(cell.Value)) And (cell.Value <> 0) Then
' Copy cells C, D, E to columns A, B, C of main sheet
.Range(.Cells(cell.Row, "C"), .Cells(cell.Row, "E")).Copy
Sheets("FORMA").Cells(nr, "A").PasteSpecial Paste:=xlPasteValues
' Increment nr counter
nr = nr + 1
' Check to see if rows are full
If nr > 70 Then
MsgBox "Data Full"
Exit Sub
End If
End If
Next cell
End With
Next c
Next i
Application.ScreenUpdating = True
End Sub

Thank you!

I think that it will be easier if the default n (for rows) is 10 and then add new row if needed more. But I am stuck (again)

royUK I tried but it let me only upload zip or other format not xlsm. I will try again

royUK Ok I uploaded the file as .zip and also uploaded it at Google Drive

EXAMPLE.xlsm

Dimmer Stock,EXAMPLE PRODUCTS,QUANTITY,PRICE 2,PRODUCT 1,1,€2,00 5,PRODUCT 2,6,€9,00 8,PRODUCT 3,11,€16,00 11,PRODUCT 4,16,€23,00 14,PRODUCT 5,21,€30,00…

docs.google.com

_{(I also noticed I became a Student! yes I like that because I have learned a lot of things in this forum)}

Thank you for the info royUK. I really didnt think of that.

I will try that later and be back.

The new row will be blank yes? If I need at column D lets say to sum B+C do I have to place the formula manual? Or there is a way to do it automatically?

And do I have to delete the check (if n>70) from the code, in order to make table work?

Basically what I want to do is

FORMA sheet has only the below lines as shown in image 1

For example I need to add 20 products (20 rows) but I only have one free row , the starting row 15.

Then the script to automatically add 19 more rows but keep rows 16-21 (the last 5) untouched as they needed for the totals. So the totals will start at row 36 and rows 15-35 will be filled with data from other sheets. Finally the addedd row will need to have the formula in column D.

So need to always add more rows depending the amount of products collected from other sheets, not delete the last 5 rows but "push" them down.

I hope I explain it correct. Than you for your effort royUK !

royUK I tried the solution with the table, but I may do something wrong. It doesnt push down rows and add a new one but it is overwrite the last rows to continue script. My problem is that I dont to overwrite the last 5 rows, but add more rows and continue insert data. I really hope you understand, I know I am getting as tedious and sorry about that, my I am very close to finish this (lets say) project. Thank again.

Quote from royUK

Are you trying to use VBA with the Table?

If you mean VBA , the code to collect data I have yes. Otherwise no... Do I have to use other code?

Quote from royUK

I don't understand. Are you using VBA to add rows to the Table?

How are you inputting lines into the invoice?

Ahhh ! I am sorry . I dont! at the original code there is a limit at 60 rows (including headers etc) . and if they go above Row 70 then ends the script. Because now the items will be many more, this means a lot of rows then I dont need the limit but add a new row if needed. I tried table but no luck! From my previous post

For example I need to add 20 products (20 rows) but I only have one free row , the starting row 15.

Then the script to automatically add 19 more rows but keep rows 16-21 (the last 5) untouched as they needed for the totals. So the totals will start at row 36 and rows 15-35 will be filled with data from other sheets. Finally the addedd row will need to have the formula in column D.

So need to always add more rows depending the amount of products collected from other sheets, not delete the last 5 rows but "push" them down, not overwrite anythink just add rows.

I need rows 1-14 stable and rows 16-21 be stable , add rows between them. I hope this is clear...

Quote from royUK

You haven't answered my question about how you are actually adding the data to the invoice.

By collecting them from Column D from the other sheets of the workgroup. If a product has quantity I collect Quantity and Price and insert them in the FORMA sheet with Description, Price and Quantity. using VBA.

I cant attach it , only in zip mode!

royUK I managed to create the table with the formulas. I have some cell merged thats why it didtnt work. My problem is that I need the last 5 rows of the "original" invoice to stay untouched, so the sums will be last. I cant move rows down and not overwrite the rows that are already there.

Is this possible? To have some rows always there and not be overwriten ? And can this be done with VBA?

I hope you understand, my English are not that good as I am not English. Thank you !

Here is the workbook at google drive. I still cant upload xlsm here

EXAMPLE1.xlsm

Dimmer Stock,EXAMPLE PRODUCTS,QUANTITY,PRICE 2,PRODUCT 1,1,€2,00 5,PRODUCT 2,6,€9,00 8,PRODUCT 3,11,€16,00 11,PRODUCT 4,16,€23,00 14,PRODUCT 5,21,€30,00…

docs.google.com