Insert New Row if needed

M

makis1023

New Member
Joined
Jun 16, 2021
Messages
49
Office Version
  1. 365
  2. 2013
Platform
  1. Windows
Hello all
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 add rows if there is a need. I have tried to make it happen using a tutorial from VBA Macro to Insert Row in Excel Based on Criteria (4 Methods) but no luck.
Also the row must have columns A B C D and D has a formula (B*C) .Can someone please help to make this work?

Here it the code

VBA Code: 
Sub BuildInvoiceAll()
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
Const sPw As String = "*****"  ' <--- Lock code
For Each shtName In ThisWorkbook.Worksheets
shtName.Unprotect sPw
Next shtName
ws1 = ActiveSheet.Name
Application.ScreenUpdating = False
' Set array of worksheet names to copy from
ws = Array("1.Power Distribution - Dimmer", "2.POWER CABLES - ADAPTORS", "3.CABLES (OTHER) - CABLE CROSS")
' Array of columns to check
sht = Array("D")
nr = 15
Sheets("PROFORMA DRYHIRE").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("PROFORMA DRYHIRE").Cells(nr, "A").PasteSpecial Paste:=xlPasteValues
' Increment nr counter
nr = nr + 1
' Check to see if rows are full
If nr > 70 Then
MsgBox "Rows are full"
Exit Sub
End If
End If
Next cell
End With
Next c
Next i
MsgBox "Done! Invoice created."
End Sub

EXAMPLE.xlsm
ABCD
1
2ΥΠΕΥΘΥΝΟΣ
3ΠΕΛΑΤΗΣ
4ΥΠΟΨΙΝ
5ΔΙΕΥΘΥΝΣΗ
6ΠΟΛΗ
7ΑΦΜ
8ΔOY
9ΤΗΛ
10email
11ΠΑΡΑΓΩΓΗ
12ΠΕΡΙΟΔΟΣ
13
14ΤΥΠΟΣ - ΠΕΡΙΓΡΑΦΗΤΕΜΑΧΙΑΤΙΜΗ ΜΟΝΑΔΟΣΣΥΝΟΛΟ
150,00 €
160,00 €
170,00 €
180,00 €
190,00 €
200,00 €
210,00 €
220,00 €
230,00 €
240,00 €
250,00 €
260,00 €
270,00 €
280,00 €
290,00 €
300,00 €
310,00 €
320,00 €
330,00 €
340,00 €
350,00 €
360,00 €
370,00 €
380,00 €
390,00 €
400,00 €
410,00 €
420,00 €
430,00 €
440,00 €
450,00 €
460,00 €
470,00 €
480,00 €
490,00 €
500,00 €
510,00 €
520,00 €
530,00 €
540,00 €
550,00 €
560,00 €
570,00 €
580,00 €
590,00 €
600,00 €
610,00 €
620,00 €
630,00 €
640,00 €
650,00 €
660,00 €
670,00 €
680,00 €
690,00 €
700,00 €
71ΣΥΝΟΛΟ ΗΜΕΡΑΣ0,00 €
72ΧΡΕΩΣΕΙΣ
73ΣΥΝΟΛΟ0,00 €
74ΕΚΠΤΩΣΗ0,00 €
75ΦΠΑ0,00 €
76ΤΕΛΙΚΟ ΣΥΝΟΛΟ0,00 €
77
PROFORMA DRYHIRE
Cell Formulas
RangeFormula
D15:D70D15=B15*C15
D71D71= SUM(D15:D70)
D73D73=D72*D71
D74D74=D73*0.8
D75D75=D74*0.24
D76D76=D74+D75



EXAMPLE.xlsm
BCDEF
2Power Distribution - Dimmer
3Power Distribution Three Phase
4StockThree Phase 400A BookDaily Rate
5
6
71Power Distribution Rack Three Phase 400A -1 CEE
8
9
10StockThree Phase 125A Lighting - TrussBookDaily Rate
11
12
132Power Distribution Box - Splitter Three Phase 125A Male / 1x125A 2x63A Female CEE
142Power Distribution Rack - Splitter Three Phase 125A Male / 1x125A 2x63A 1x32A Female CEE
156Power Distribution Rack Three Phase 125A CEE Type 1
164Power Distribution Rack Three Phase 125A CEE Type 1 New
177Power Distribution Rack Three Phase 125A CEE Type 2
18
19
20
21StockThree Phase 63A Lighting - TrussBookDaily Rate
22
23
246Power Distribution Box Indu Three Phase 63A CEE
253Power Distribution Box Indu Three Phase 63A CEE (RCD)
264Power Distribution Box Indu Three Phase 63A CEE (RCD+Multimeter)
276Power Distribution Rack Three Phase 63A CEE Type 1
284Power Distribution Rack Three Phase 63A CEE Type 1 New
296Power Distribution Rack Three Phase 63A Modular
30
31
32
33StockThree Phase 32A Lighting - TrussBookDaily Rate
34
35
364Power Distribution Box Indu Three Phase 32 CEE (3 X 32A Mono)
3734Power Distribution Box Indu Three Phase 32A CEE (6x16A)
383Power Distribution Box Indu Three Phase 32A CEE (6xSchuko)
392Power Distribution Box Indu Three Phase 32A CEE New (RCD+Multimeter)
404Power Distribution Rack 3U Three Phase 32A CEE
414Power Distribution Rack 3U Three Phase 32A CEE (6 x Twin Schuko)
425Power Distribution Rack 3U Three Phase 32A CEE (RCD)
43
44
45
46
47StockThree Phase 125A AudioBookDaily Rate
48
49
504125A CEE Type 1 S Three Phase Power Distribution Rack
511125A CEE Type 2 S Three Phase Power Distribution Rack
52
53
54StockThree Phase 63A AudioBookDaily Rate
55
56
57463A CEE Type 1 S Three Phase Power Distribution Rack
58263A CEE Type 2 S Three Phase Power Distribution Box Indu
59263A CEE Type 3 S Three Phase Power Distribution Box Indu
60363A CEE Type 4 S Three Phase Power Distribution Box Walther
61
62
63StockThree Phase 32A AudioBookDaily Rate
64
65
66432A CEE Type 1 S Three Phase Power Distribution Rack
67632A CEE Type 2 S Three Phase Power Distribution Box Walther
68432A CEE Type 3 S Three Phase Power Distribution Box Walther
69
70
71Dimmer
72StockDimmer SetBookDaily Rate
73
74
755Elecrtron 2 x Actor 716 12 Ch. Rack Total 24Ch. x 3 Kw Set
762Elecrtron 2 x Actor 716 12 Ch + 2 x Actor 616 6 Ch. Rack Total 36Ch. x 3 Kw Set (Blue)
773Elecrtron 3 x Actor 716 12 Ch. Rack Total 36Ch. x 3 Kw Set (Black) Set
782MA Lighting Digital Dimmer 2 x 12 x 3,7 Kw + MA Lighting Power Distributor 125A Red In / 2x63A Red - 1 x Schuko Set Out
791MA Lighting Dimmer 2 x 12 x 2 Kw Set
803SLS DigiLight 36ch x 3 Kw Set
815SLS Dimmer DigiLight 24ch x 3 Kw A Set(2x12ch)
824SLS Dimmer DigiLight 24ch x 3 Kw B Set (1x12-2x6ch)
83
84
85StockDimmer BookDaily Rate
86
874Atel Lights 6ch Dimmer 6 x 2 Kw
881Celco Fusion Dimmer 12ch x 2 Kw
896Electron 6Ch x 3 Kw Dimmerpack E1550DC
9010Electron Actor 616 6 x 3 Kw
913Electron Actor 625 6 x 5 Kw
921Electron Actor E1550 6ch x 5 Kw
938FOS Technologies 1CH. Dimmer
944SLS DigiLight 6ch x 3 Kw
951SLS DigiLight 12ch x 3 Kw
96
97
98
99StockBookDaily Rate
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
1.Power Distribution - Dimmer


Thank you in advance!

​

 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
Sort by date Sort by votes
You must log in or register to reply here.

Similar threads

M
  • Solved
VBA script to insert new rows based on data in other cells... then cut data from those cells and paste into those new rows
Replies
6
Views
1K
mbnnyc
M
J
  • Question
Double click to insert picture within protected worksheet
2
Replies
10
Views
2K
cmowla
C
G
  • Question
Insert new line on three worksheets
Replies
3
Views
733
AFPathfinder
A
S
  • Question
VBA to Insert a New Row In a Worksheet - Extra Code Needed!
Replies
5
Views
3K
Simonc64
S
F
  • Question
Inherited Slow running, memory hog.
Replies
4
Views
1K
bertie
bertie

Forum statistics

Threads
1,215,006
Messages
6,122,666
Members
449,091
Latest member
peppernaut

Share this page

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