Optimising my VBA to improve the efficiency of my Workbook

Tmini · Aug 11, 2023

Hi
I have set up a system that I use for work whereas I will quote up a job with all of the materials output as a list. Each workbook is a new unit that has it's own list of materials. They are all stored in a different job folder for each job. I then have another workbook with VBA code where I click a button find the job folder and then the VBA will work it's magic and open each file within that folder copy all of the materials data from each workbook into one final workbook. It has several sheets within it that will show different data sets depending on the information I need for each job. One of the data sets is a list of materials for each of the units. I have it so it copies all of the materials data from each workbook that it opens, Pastes the values and then I have it delete all of the irrelevant data with no value. The problem is when I have a large job of 500 files or so it can take 7 hours to run through every single workbook and delete all of the data with no value. Now I know my issue is running it so it iterates through it one line at a time but I am unsure how to make it so it will sort it all so all of the no value data is at the top and it deletes that straight away then it re-sorts itself back into the order that it should be. I have guessed that it will take a 7 hour job and reduce it to less than an hour if I can figure out how to optimise it.
With my first screenshot that is my initial job unit workbook as you can see it runs from line 1 all the way through to line 289 - this is where my materials list ends. I have to copy the entire list because every unit will have a different list of materials which is listed from rows 16 through to 289 and only shows when that material is needed for the job. When this is copied over to the new workbook I can have several hundred files copied over and that adds up to hundreds of thousands of rows with many blank rows which are all deleted. My initial thoughts are to try and number each row in alphanumeric order as they are always the same amount of rows and for each new work book that is copied have a formula which will add those numbers up from the previous workbook to number alphanumerically all the way through 1- however many rows it ends up being. I would ideally get it to sort through the data and organise it so all blank data rows are at the top and have it delete all of those rows and then have it do another sort from the alphanumeric data column to go from smallest to largest to put it back in order. I am just unsure how to do this. If anyone can help on this that would be great and it would save me so much time when I'm under the pump

The code that I use to sort the data and delete the non value rows is as follows

VBA Code:

Sub delrowsifzero1()

    Application.ScreenUpdating = False

    Dim LastRow As Long

     Worksheets("Itemised Detail").Activate

     On Error Resume Next

       LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

    Dim x As Long

    ActiveWorkbook.Worksheets("Itemised Detail").Sort.SortFields.Clear

    ActiveWorkbook.Worksheets("Itemised Detail").Sort.SortFields.Add Key:=Range("A:a" & LastRow) _

        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal

    With ActiveWorkbook.Worksheets("Itemised Detail").Sort

        .SetRange Range("A:a" & LastRow)

        .Header = xlGuess

        .MatchCase = False

        .Orientation = xlTopToBottom

        .SortMethod = xlPinYin

        .Apply

    End With

    For x = LastRow To 2 Step -1

        If Cells(x, 1) = "" Or Cells(x, 1) = 0 Then

            Rows(x).EntireRow.Delete

        End If

    Next x

    Application.ScreenUpdating = True

    Application.CutCopyMode = False

  

            'Hide worksheets

    Worksheets("Overall Costs").Visible = xlSheetHidden

    Worksheets("Single Unit Pricing").Visible = xlSheetHidden

    Worksheets("Total Hours For All Units").Visible = xlSheetHidden

    Worksheets("Single Unit Hours").Visible = xlSheetHidden

      End Sub

Peter_SSs · Aug 21, 2023

OK, let's just focus on the deleting rows as fast as possible.
Could we have (with XL2BB, not an image, so that we can copy for testing) a mocked-up smallish sample of a worksheet where rows do need deletion? In that sample, instead of several hundred rows that are not required in a section, just make it 4 or 5 rows. For now, can we also not have white text, I would like to be able to see what is in all the cells.

Tmini said:
zero value data in the price column

I am assuming that in the sample sheet it will be clear which column that is.
If not, please let us know as it appears that is the critical column for determining which rows to delete (or at least for the majority of them)

Tmini · Aug 21, 2023

This is the data
The blank cells contain formulas and are shown to be blank when the price under the total cost column is zero (Column D). I have made it so all cells show the data that was previously hidden by white formatting

0001 Apartment 101 Kitchen.xlsx

A

B

C

D

E

F

G

H

I

J

K

L

M

1

Apartment 101 Kitchen

Overall Costs

Single Unit Cost

Price Per Unit

Unit Multiples

ENTER JOB NAME HERE

2

Overall Labour Cost

$18,260.00

$43,732.00

1

VARIATION Y/N?

N

3

Material Cost

$25,472.00

Approx. Job Volume (m3)

Overall Price

Panelling Qty For Rate Purposes

4

Subcontractor Cost

$0.00

Overall

Single

$43,732.00

Single

Overall

5

TOTAL COST PRICE

Wall panels Sq/M

6

Ceiling Panels Sq/M

7

Total Hrs

Price

Approx. Job Weight (Kg)

Notes & Information

8

Assembly Hours

42.27

$6,087.00

Overall

Single

0

9

Machining Hours

4.71

$791.00

912.26

10

Site Fixing Hours

35.19

$5,067.00

11

Site Manager Hours

4.22

$608.00

12

Timber Dressing Hours

0.64

$93.00

65.41

13

Set Out Hours

33.53

$4,828.00

12.72

14

Delivery

6.55

$786.00

Stone Total Qty Sq/M

Stone Total Costs

15

Materials

Area Of Use

Total Quantity

Total Cost

Rate

UOM

16

17

Board/Laminate Laminex JF-201 Kitchen/Scullery/Robe Carcasses 16mm Black Pearl MR E0 Particle Board DS 3600x1800 3600x1800 (Sheets)

JF-201 Black Carcass Material 2

18

19

20

21

22

23

24

Board/Laminate WWTT-JF-101A - FABRIC/FLOW 19mm Chestnut Veneer MR E0 MDF DS 3000x1200 3000x1200 (Sheets)

JF-101A Material 1 Doors & Panels

Each Unit

Overall Total

25

Board/Laminate WWTT-JF-101A - FABRIC/FLOW 19mm Chestnut Veneer MR E0 MDF SS 3000x1200 3000x1200 (Sheets)

JF-101A Material 2 Doors & Panels

Board/Laminate

26

Hardware

$12,257.00

27

Solid Timber

$59.00

28

Squareform Benchtop

$0.00

29

Board/Laminate WWTT 16mm MDF Plain MR E0 MDF DS 3600x1200 3600x1200 (Sheets)

16 MR MDF Wall Substrates

Glazing

30

Board/Laminate WWTT-JF-101A - FABRIC/FLOW 19mm Chestnut Veneer MR E0 MDF DS 3000x1200 3000x1200 (Sheets)

JF-101A Make Up Sheets

Subcontractor

31

Stonework

$0.00

32

Design & Manufacture

$11,799.00

33

Delivery & Install

$6,461.00

Builder Costings

Cell Formulas
Range	Formula
A1,B8:B14,A6,K5:L6,I2	A1	='Total Quantities'!A1
J1	J1	='Total Quantities'!J1:M1
B2	B2	=ROUND(SUM(C8:C14),0)
C2:C5	C2	=IF('Builder Costings'!$I$2>1,ROUND(B2/'Builder Costings'!$I$2,0),B2)
B3:B4	B3	=ROUND('Total Quantities'!B3*(1+'Total Quantities'!G2),0)
B5	B5	=ROUND(B3+B2+B4,0)
H2,H4	H2	=ROUND('Total Quantities'!H2,0)
F5:G5	F5	='Total Quantities'!F6
D7	D7	=IF('Builder Costings'!$I$2>1,"Single Unit Hrs","")
C8:C10	C8	=ROUND(B8Pricing!P7(1+'Total Quantities'!$G$1),0)
D8:D10	D8	=IF('Builder Costings'!$I$2>1,B8/'Builder Costings'!$I$2,"")
C11:C14	C11	=ROUND(B11Pricing!P9(1+'Total Quantities'!$G$1),0)
D11	D11	=IF('Builder Costings'!$I$2>1,B12/'Builder Costings'!$I$2,"")
D12	D12	=IF('Builder Costings'!I2=0,0,(IF(AND('Base Units'!D11=0,'Base Units'!D47=0,'Base Units'!D83=0,'Base Units'!D119=0),0,'Base Units'!Q24+'Base Units'!R24)) +(IF(AND('OS Base Units'!D10=0,'OS Base Units'!D40=0,'OS Base Units'!D70=0,'OS Base Units'!D100=0),0,'OS Base Units'!Q22+'OS Base Units'!R22)) +(IF(AND('Corner Base Unit'!D11=0,'Corner Base Unit'!D52=0,'Corner Base Unit'!D93=0,'Corner Base Unit'!D134=0),0,'Corner Base Unit'!Q24+'Corner Base Unit'!R24)) +(IF(AND('Drawer Units'!D11=0,'Drawer Units'!D51=0,'Drawer Units'!D91=0,'Drawer Units'!D131=0),0,'Drawer Units'!Q24+'Drawer Units'!R24)) +(IF(AND('Oven Units'!D11=0,'Oven Units'!D48=0,'Oven Units'!D85=0,'Oven Units'!D122=0),0,'Oven Units'!Q24+'Oven Units'!R24)) +(IF(AND('MW Units'!D11=0,'MW Units'!D46=0,'MW Units'!D81=0,'MW Units'!D116=0),0,'MW Units'!Q25+'MW Units'!R25)) +(IF(AND('Tall Units'!D11=0,'Tall Units'!D51=0,'Tall Units'!D93=0,'Tall Units'!D135=0),0,'Tall Units'!Q23+'Tall Units'!R23)) +(IF(AND('OS Tall Units'!D10=0,'OS Tall Units'!D45=0,'OS Tall Units'!D80=0,'OS Tall Units'!D115=0),0,'OS Tall Units'!Q21+'OS Tall Units'!R21)) +(IF(AND('Robe Units'!D12=0,'Robe Units'!D54=0,'Robe Units'!D96=0,'Robe Units'!D138=0),0,'Robe Units'!Q27+'Robe Units'!R27)) +(IF(AND('Fridge Recess'!D14=0,'Fridge Recess'!D52=0,'Fridge Recess'!D90=0,'Fridge Recess'!D128=0),0,'Fridge Recess'!Q26+'Fridge Recess'!R26)) +(IF(AND('Overhead Units'!D11=0,'Overhead Units'!D45=0,'Overhead Units'!D79=0,'Overhead Units'!D113=0),0,'Overhead Units'!Q24+'Overhead Units'!R24)) +(IF(AND('OS Overheads'!D10=0,'OS Overheads'!D37=0,'OS Overheads'!D64=0,'OS Overheads'!D91=0),0,'OS Overheads'!Q22+'OS Overheads'!R22)) +(IF(AND('Vanity Top Substrates'!D9=0,'Vanity Top Substrates'!D24=0,'Vanity Top Substrates'!D39=0,'Vanity Top Substrates'!D54=0),0,'Vanity Top Substrates'!O20+'Vanity Top Substrates'!P20)) +(IF(AND(Walls!D11=0,Walls!D33=0,Walls!D55=0,Walls!D77=0),0,Walls!Q21+Walls!R21)) +(IF(AND('Bench Tops'!D12=0,'Bench Tops'!D35=0,'Bench Tops'!D58=0,'Bench Tops'!D81=0),0,'Bench Tops'!Q19+'Bench Tops'!R19)) +IF(SUM(Extras!C6:C15,Extras!C17:C19,Extras!C21:C26,Extras!C28:C33,Extras!C35:C40,Extras!C44:C49,Extras!C51:C55)<>0,Extras!R10,0) +(('Wall Panelling'!W13)/'Builder Costings'!I2) +(('Ceiling Panelling'!W13)/'Builder Costings'!I2)) +(IF(AND(Doors!D12=0,Doors!D39=0,Doors!D66=0,Doors!D93=0),0,Doors!Q18+Doors!R18)) +Subcontractor!R21
D13	D13	=IFERROR(IF('Builder Costings'!I2=0,0,IF(OR('Total Quantities'!G10="N",'Total Quantities'!G9="Y"),0,(IF(AND('Base Units'!D11=0,'Base Units'!D47=0,'Base Units'!D83=0,'Base Units'!D119=0),0,'Base Units'!Q25+'Base Units'!R25)) +(IF(AND('OS Base Units'!D10=0,'OS Base Units'!D40=0,'OS Base Units'!D70=0,'OS Base Units'!D100=0),0,'OS Base Units'!Q23+'OS Base Units'!R23)) + (IF(AND('Corner Base Unit'!D11=0,'Corner Base Unit'!D52=0,'Corner Base Unit'!D93=0,'Corner Base Unit'!D134=0),0,'Corner Base Unit'!Q25+'Corner Base Unit'!R25)) +(IF(AND('Drawer Units'!D11=0,'Drawer Units'!D51=0,'Drawer Units'!D91=0,'Drawer Units'!D131=0),0,'Drawer Units'!Q25+'Drawer Units'!R25)) +(IF(AND('Oven Units'!D11=0,'Oven Units'!D48=0,'Oven Units'!D85=0,'Oven Units'!D122=0),0,'Oven Units'!Q25+'Oven Units'!R25)) +(IF(AND('MW Units'!D11=0,'MW Units'!D46=0,'MW Units'!D81=0,'MW Units'!D116=0),0,'MW Units'!Q26+'MW Units'!R26)) +(IF(AND('Tall Units'!D11=0,'Tall Units'!D51=0,'Tall Units'!D93=0,'Tall Units'!D135=0),0,'Tall Units'!Q24+'Tall Units'!R24)) +(IF(AND('OS Tall Units'!D10=0,'OS Tall Units'!D45=0,'OS Tall Units'!D80=0,'OS Tall Units'!D115=0),0,'OS Tall Units'!Q22+'OS Tall Units'!R22)) +(IF(AND('Robe Units'!D12=0,'Robe Units'!D54=0,'Robe Units'!D96=0,'Robe Units'!D138=0),0,'Robe Units'!Q28+'Robe Units'!R28)) +(IF(AND('Fridge Recess'!D14=0,'Fridge Recess'!D52=0,'Fridge Recess'!D90=0,'Fridge Recess'!D128=0),0,'Fridge Recess'!Q27+'Fridge Recess'!R27)) +(IF(AND('Overhead Units'!D11=0,'Overhead Units'!D45=0,'Overhead Units'!D79=0,'Overhead Units'!D113=0),0,'Overhead Units'!Q25+'Overhead Units'!R25)) +(IF(AND('OS Overheads'!D10=0,'OS Overheads'!D37=0,'OS Overheads'!D64=0,'OS Overheads'!D91=0),0,'OS Overheads'!Q23+'OS Overheads'!R23)) +(IF(AND('Vanity Top Substrates'!D9=0,'Vanity Top Substrates'!D24=0,'Vanity Top Substrates'!D39=0,'Vanity Top Substrates'!D54=0),0,'Vanity Top Substrates'!O21+'Vanity Top Substrates'!P21)) +(IF(AND(Walls!D11=0,Walls!D33=0,Walls!D55=0,Walls!D77=0),0,Walls!Q22+Walls!R22)) +(IF(AND('Bench Tops'!D12=0,'Bench Tops'!D35=0,'Bench Tops'!D58=0,'Bench Tops'!D81=0),0,'Bench Tops'!Q20+'Bench Tops'!R20)) +IF(SUM(Extras!C6:C15,Extras!C17:C19,Extras!C21:C26,Extras!C28:C33,Extras!C35:C40,Extras!C44:C49,Extras!C51:C55)<>0,Extras!R11,0) +(('Wall Panelling'!W14)/'Builder Costings'!I2) +(('Ceiling Panelling'!W14)/'Builder Costings'!I2) +(IF(AND(Doors!D12=0,Doors!D39=0,Doors!D66=0,Doors!D93=0),0,Doors!Q19+Doors!R19)))) +(Subcontractor!R22/'Builder Costings'!I2),0)
J8	J8	='Total Quantities'!J8:M12
F9:G9	F9	='Total Quantities'!F7
H15:H23	H15	=IF(J15>0,'_Total Quantities_'!J6,"")
I15:I23	I15	='_Total Quantities_'!K6
J15:J23	J15	=ROUND('_Total Quantities_'!L6*(1+'_Total Quantities_'!$G$4),0)
A16:A33	A16	=IF('Total Quantities'!C16>0,'Total Quantities'!A16,"")
B16:B33	B16	=IF('Total Quantities'!C16>0,'Total Quantities'!B16,"")
C16:C33	C16	=IF('Total Quantities'!C16>0,'Total Quantities'!C16,"")
D16:D33	D16	=IF('Total Quantities'!C16>0,ROUND(('Total Quantities'!E16(1+'Total Quantities'!$G$2))(1+'Total Quantities'!$I$89),0),"")
E16:E33	E16	=IF('Total Quantities'!C16>0,('Total Quantities'!F16(1+'Total Quantities'!$G$2))(1+'Total Quantities'!$I$89),"")
F16:F33	F16	=IF('Total Quantities'!C16>0,'Total Quantities'!G16,"")
I25:I33	I25	=ROUND(J25/$I$2,0)
J25:J33	J25	='_Total Quantities_'!J16

Peter_SSs · Aug 21, 2023

Thanks for the sample. So in that sheet, you would want to delete rows where column A is "" starting from row 33 and working up to row 15 or right up to row 2 or some other point?

.. and confirming that it is okay to delete rows even though data does/may exist in columns F:M in those rows?

In your original second image, the worksheet had more than one section like this sample (eg 'Materials' heading in row 126 and in row 142). Is that more like the real situation? If so, we still just delete all rows that are "" in column A?

Tmini · Aug 21, 2023

I want to only delete the rows from row 33 right up to row 15 - Above that doesn't need to be deleted as that stays the same.
Yes that is correct you can delete the rows even though there is data in E:M - the column that matters is if Column D is greater than 0 - the total cost.
The second image of my original is like the real situation the mini sheet above is just one of many workbooks where the data comes from. The second image of my original shows the data from 2 workbooks and this is repeated by however many workbooks a job may have - another example showing more than 2 workbooks is attached. It's hard to get much more than that as it doesn't fit on my screen.

Peter_SSs · Aug 21, 2023

Tmini said:
the column that matters is if Column D is greater than 0

The code below does not quite use that test, at least as numbers. Given the discussion above, that column also has repeat headings and the code below leaves all the heading and only deletes rows that are empty or have just "" in column D. It can be adjusted if you want the repeat headings removed as well.

I suggest test first with a copy of the sample file. If that works as expected then try with copies of the larger files.

VBA Code:

Sub Del_Rows()
  Dim a As Variant, b As Variant
  Dim nc As Long, i As Long, k As Long
 
  With Sheets("Builder Costings")
    nc = .Cells.Find(What:="*", LookIn:=xlFormulas, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column + 2
    a = .Range("D15", .Range("D" & Rows.Count).End(xlUp)).Value
    ReDim b(1 To UBound(a), 1 To 1)
    For i = 1 To UBound(a)
      If Len(a(i, 1)) = 0 Then
        b(i, 1) = 1
        k = k + 1
      End If
    Next i
    If k > 0 Then
      Application.ScreenUpdating = False
      With .Range("A15").Resize(UBound(a), nc)
        .Columns(nc).Value = b
        .Sort Key1:=.Columns(nc), Order1:=xlAscending, Header:=xlNo
        .Resize(k).EntireRow.Delete
      End With
      Application.ScreenUpdating = True
    End If
  End With
End Sub

Tmini · Aug 21, 2023

Peter_SSs said:
The code below does not quite use that test, at least as numbers. Given the discussion above, that column also has repeat headings and the code below leaves all the heading and only deletes rows that are empty or have just "" in column D. It can be adjusted if you want the repeat headings removed as well.

I suggest test first with a copy of the sample file. If that works as expected then try with copies of the larger files.

VBA Code:

Sub Del_Rows() Dim a As Variant, b As Variant Dim nc As Long, i As Long, k As Long With Sheets("Builder Costings") nc = .Cells.Find(What:="*", LookIn:=xlFormulas, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column + 2 a = .Range("D15", .Range("D" & Rows.Count).End(xlUp)).Value ReDim b(1 To UBound(a), 1 To 1) For i = 1 To UBound(a) If Len(a(i, 1)) = 0 Then b(i, 1) = 1 k = k + 1 End If Next i If k > 0 Then Application.ScreenUpdating = False With .Range("A15").Resize(UBound(a), nc) .Columns(nc).Value = b .Sort Key1:=.Columns(nc), Order1:=xlAscending, Header:=xlNo .Resize(k).EntireRow.Delete End With Application.ScreenUpdating = True End If End With End Sub

Thanks for that Peter
It worked but it did delete the repeated portions I need to be left. The way around that would be to have it that anything in column c and column d greater than 0 the row can be deleted. With the execution it was only 2 seconds quicker over 5 files than my original workbook. I'm not sure if that will be quicker over a larger job - something I will need to test next
This is how it looks with your code

Test Mr Excel MOD 1 EXECUTED.xlsm

A

B

C

D

E

F

1

Apartment 101 Kitchen

Overall Costs

Single Unit Cost

Overall Price

Price Per Unit

Unit Multiples

2

Overall Labour Cost

$18,260.00

$43,732.00

1

3

Material Cost

$25,472.00

4

Subcontractor Cost

$0.00

5

TOTAL COST PRICE

$43,732.00

6

0

7

Total Hrs

Price

8

Assembly Hours

42.27

$6,087.00

9

Machining Hours

4.71

$791.00

10

Site Fixing Hours

35.19

$5,067.00

11

Site Manager Hours

4.22

$608.00

12

Timber Dressing Hours

0.64

$93.00

13

Set Out Hours

33.53

$4,828.00

14

Delivery

6.55

$786.00

15

Materials

Area Of Use

Total Quantity

Total Cost

Rate

UOM

16

Board/Laminate Laminex JF-201 Kitchen/Scullery/Robe Carcasses 16mm Black Pearl MR E0 Particle Board DS 3600x1800 3600x1800 (Sheets)

JF-201 Black Carcass Material 2

6.40

$1,165.00

$28.12

Sq/M

17

Board/Laminate WWTT-JF-101A - FABRIC/FLOW 19mm Chestnut Veneer MR E0 MDF DS 3000x1200 3000x1200 (Sheets)

JF-101A Material 1 Doors & Panels

5.79

$4,001.00

$191.84

Sq/M

18

Board/Laminate WWTT-JF-101A - FABRIC/FLOW 19mm Chestnut Veneer MR E0 MDF SS 3000x1200 3000x1200 (Sheets)

JF-101A Material 2 Doors & Panels

1.35

$628.00

$129.29

Sq/M

19

Board/Laminate WWTT 16mm MDF Plain MR E0 MDF DS 3600x1200 3600x1200 (Sheets)

16 MR MDF Wall Substrates

0.13

$12.00

$21.40

Sq/M

20

Board/Laminate WWTT-JF-101A - FABRIC/FLOW 19mm Chestnut Veneer MR E0 MDF DS 3000x1200 3000x1200 (Sheets)

JF-101A Make Up Sheets

0.65

$449.00

$191.84

Sq/M

21

Board/Laminate WWTT 16mm MDF Plain MR E0 MDF DS 3600x1200 3600x1200 (Sheets)

16 MR MDF Kicker Substrate

0.78

$72.00

$21.40

Sq/M

22

Board/Laminate WWTT 16mm MDF Plain MR E0 MDF DS 3600x1200 3600x1200 (Sheets)

16 MR MDF Pelmet Substrate

0.11

$10.00

$21.40

Sq/M

23

Board/Laminate WWTT-JF-101A - FABRIC/FLOW 19mm Chestnut Veneer MR E0 MDF DS 3000x1200 3000x1200 (Sheets)

JF-101A Wall Panel 01

2.00

$1,383.00

$191.84

Sq/M

24

Edging Laminex JF-201 Kitchen/Scullery/Robe Carcasses Black Stipple ABS 21mm x 1mm x 25 mtr roll 21 (L/M)

JF-201 Black Carcass Material 2

52.43

$50.00

$0.96

L/M

25

Edging WWTT - JF-101A - FABRIC/FLOW Chestnut Veneer Chestnut 21mm x 1mm x 50 mtr roll 21 (L/M)

JF-101A Material 1 Doors & Panels

83.69

$289.00

$3.46

L/M

26

Edging WWTT - JF-101A - FABRIC/FLOW Chestnut Veneer Chestnut 21mm x 1mm x 50 mtr roll 21 (L/M)

JF-101A Wall Panel 01

27.31

$94.00

$3.46

L/M

27

Painting/Polishing 2-Pac Clear Satin Polish Sq/M (Sq/M)

Clear Satin Polish 2-Pac 1 Clear

30.91

$4,998.00

$161.70

Sq/M

28

Solid Timber Wood Austim H3 Structural Pine 90mmx35mm L/M (L/M)

Pine 90x35 Wall & Door Structural Pine

1.00

$12.00

$12.05

L/M

29

Solid Timber Wood Austim Victorian Ash Select 75mmx25mm L/M (L/M)

Vic Ash 75x25 Door Edge Timber 1

3.88

$47.00

$12.05

L/M

30

Hardware Hafele FX-115 Kesseboehmer Pull Out Rack 549.24.320 Ea (Ea)

FX-115 Pull Out Tea Towel Rack

1.00

$320.00

$320.40

Ea

31

Hardware Hafele FX-110 Portero Cleaning Rack 545.48.762 Ea (Ea)

FX-110 Cleaning Rack

1.00

$541.00

$541.27

Ea

32

Hardware Hafele FX-118 Hideaway Concelo CR235D Dark Grey Cinder 70L Bin Unit 503.64.624 Ea (Ea)

FX-118 Bin Units

1.00

$864.00

$864.45

Ea

33

Hardware Hafele FX-112 Tipmatic Push Catch 356.03.510 Ea (Ea)

FX-112 Handles 1

7.00

$93.00

$13.30

Ea

34

Hardware Wilson & Bradley Blum 110 Degree Full Overlay Soft Close Inserta Hinge & Hinge Plate BM71B3590/BP174610 Ea (Ea)

Standard Hinge Hinges

16.00

$158.00

$9.86

Ea

35

Hardware Hafele FX-114 Free Fold S Edrive Flap Fittings 372.29.704 Ea (Ea)

FX-114 Flap Hinges

6.00

$6,616.00

$1,102.72

Ea

36

Hardware Hafele FX-113 Nova Pro Scala 450mm Deep Drawer Runners 63mmH 551.93.504/551.80.000/551.90.161/551.80.005 Ea (Ea)

FX-113 Drawer Runners 1

11.00

$1,579.00

$143.56

Ea

37

Hardware Hafele FX-113 & FX-117 Sensomatic V2 Single Drawer Auto Opener Drawer Runners 553.00.509 Ea (Ea)

FX-113/FX-117 Drawer Runners 2

2.00

$1,193.00

$596.75

Ea

38

Hardware The Lock & Handle DR-403 Hydraulic Matte Black Pivot Hinge Ea (Ea)

DR-403 Door Pivot Hinges

1.00

$441.00

Ea

39

Hardware Alspec Split Battens AD44 Sliding Door Interlock 6500mmx50mmx6mm - 1602440 L/M (L/M)

Split Battens Split Battens

23.54

$202.00

$8.60

L/M

40

Hardware Hafele Hafele Clear Plastic Shelf Supports 13mmx10.5mmx14.5mm - 282.12.403 Ea (Ea)

Clear Shelf Supports Shelf Supports

4.00

$1.00

$0.13

Ea

41

Hardware Hafele FX-103 Cusio 450mm Graphite Cutlery Tray 553.65.824 Ea (Ea)

FX-103 Cutlery Insert 1

2.00

$182.00

$91.10

Ea

42

Hardware Hafele FX-116 Salsa & Spice 390mm Wide Cutlery Tray 556.63.753 Ea (Ea)

FX-116 Cutlery Insert 2

1.00

$17.00

$17.24

Ea

43

Hardware Officeworks Officeworks PPS Foam Wrap 50000mmx1200mmx1.5mm - PPFOAM1200 L/M (L/M)

Foam Packaging Protection

17.00

$50.00

$2.94

L/M

44

Apartment 101 Scullery

Overall Costs

Single Unit Cost

Overall Price

Price Per Unit

Unit Multiples

45

Overall Labour Cost

$9,236.00

$17,708.00

1

46

Materials

Area Of Use

Total Quantity

Total Cost

Rate

UOM

47

Board/Laminate Laminex JF-201 Kitchen/Scullery/Robe Carcasses 16mm Black Pearl MR E0 Particle Board DS 3600x1800 3600x1800 (Sheets)

JF-201 Black Carcass Material 2

4.49

$817.00

$28.12

Sq/M

48

Board/Laminate WWTT-JF-101A - FABRIC/FLOW 19mm Chestnut Veneer MR E0 MDF DS 3000x1200 3000x1200 (Sheets)

JF-101A Material 1 Doors & Panels

4.28

$2,958.00

$191.84

Sq/M

49

Board/Laminate WWTT 16mm MDF Plain MR E0 MDF DS 3600x1200 3600x1200 (Sheets)

16 MR MDF Kicker Substrate

0.48

$45.00

$21.40

Sq/M

50

Edging Laminex JF-201 Kitchen/Scullery/Robe Carcasses Black Stipple ABS 21mm x 1mm x 25 mtr roll 21 (L/M)

JF-201 Black Carcass Material 2

42.41

$41.00

$0.96

L/M

51

Edging WWTT - JF-101A - FABRIC/FLOW Chestnut Veneer Chestnut 21mm x 1mm x 50 mtr roll 21 (L/M)

JF-101A Material 1 Doors & Panels

58.37

$202.00

$3.46

L/M

52

Painting/Polishing 2-Pac Clear Satin Polish Sq/M (Sq/M)

Clear Satin Polish 2-Pac 1 Clear

17.00

$2,750.00

$161.70

Sq/M

53

Hardware Hafele FX-118 Hideaway Concelo CR235D Dark Grey Cinder 70L Bin Unit 503.64.624 Ea (Ea)

FX-118 Bin Units

1.00

$864.00

$864.45

Ea

54

Hardware Hafele FX-112 Tipmatic Push Catch 356.03.510 Ea (Ea)

FX-112 Handles 1

5.00

$67.00

$13.30

Ea

55

Hardware Wilson & Bradley Blum 110 Degree Full Overlay Soft Close Inserta Hinge & Hinge Plate BM71B3590/BP174610 Ea (Ea)

Standard Hinge Hinges

20.00

$197.00

$9.86

Ea

56

Hardware Hafele FX-111 & FX-113 Nova Pro Scala 450mm Deep Drawer Runners 551.94.504/551.89.509 (1/2 Length) Ea (Ea)

FX-111 Drawer Runners 3

5.00

$504.00

$100.83

Ea

57

Hardware Hafele Hafele Clear Plastic Shelf Supports 13mmx10.5mmx14.5mm - 282.12.403 Ea (Ea)

Clear Shelf Supports Shelf Supports

26.00

$3.00

$0.13

Ea

58

Hardware Officeworks Officeworks PPS Foam Wrap 50000mmx1200mmx1.5mm - PPFOAM1200 L/M (L/M)

Foam Packaging Protection

8.79

$26.00

$2.94

L/M

59

Apartment 101 Laundry

Overall Costs

Single Unit Cost

Overall Price

Price Per Unit

Unit Multiples

60

Overall Labour Cost

$3,593.00

$7,686.00

1

61

Materials

Area Of Use

Total Quantity

Total Cost

Rate

UOM

62

Board/Laminate Laminex JF-201 Bathroom/Ensuite/Laundry Carcasses 16mm TE White Melamine Flint MR E0 Particle Board DS 3600x1800 3600x1800 (Sheets)

JF-201 White Carcass Material 1

2.27

$293.00

$19.96

Sq/M

63

Board/Laminate WWTT-JF-101A - FABRIC/FLOW 19mm Chestnut Veneer MR E0 MDF DS 3000x1200 3000x1200 (Sheets)

JF-101A Material 1 Doors & Panels

2.33

$1,611.00

$191.84

Sq/M

64

Board/Laminate WWTT 16mm MDF Plain MR E0 MDF DS 3600x1200 3600x1200 (Sheets)

16 MR MDF Kicker Substrate

0.17

$16.00

$21.40

Sq/M

65

Edging Laminex JF-201 Bathroom/Ensuite/Laundry Carcasses White Stipple ABS 21mm x 1mm x 200 mtr roll 21 (L/M)

JF-201 White Carcass Material 1

21.00

$10.00

$0.46

L/M

66

Edging WWTT - JF-101A - FABRIC/FLOW Chestnut Veneer Chestnut 21mm x 1mm x 50 mtr roll 21 (L/M)

JF-101A Material 1 Doors & Panels

36.70

$127.00

$3.46

L/M

67

Painting/Polishing 2-Pac Clear Satin Polish Sq/M (Sq/M)

Clear Satin Polish 2-Pac 1 Clear

11.16

$1,805.00

$161.70

Sq/M

68

Hardware Hafele FX-112 Tipmatic Push Catch 356.03.510 Ea (Ea)

FX-112 Handles 1

7.00

$93.00

$13.30

Ea

69

Hardware Wilson & Bradley Blum 110 Degree Full Overlay Soft Close Inserta Hinge & Hinge Plate BM71B3590/BP174610 Ea (Ea)

Standard Hinge Hinges

12.00

$118.00

$9.86

Ea

70

Hardware Hafele Hafele Clear Plastic Shelf Supports 13mmx10.5mmx14.5mm - 282.12.403 Ea (Ea)

Clear Shelf Supports Shelf Supports

21.00

$3.00

$0.13

Ea

71

Hardware Officeworks Officeworks PPS Foam Wrap 50000mmx1200mmx1.5mm - PPFOAM1200 L/M (L/M)

Foam Packaging Protection

5.53

$16.00

$2.94

L/M

72

Apartment 101 Robe 2

Overall Costs

Single Unit Cost

Overall Price

Price Per Unit

Unit Multiples

73

Overall Labour Cost

$8,211.00

$24,746.00

1

74

Materials

Area Of Use

Total Quantity

Total Cost

Rate

UOM

75

Board/Laminate Laminex JF-201 Kitchen/Scullery/Robe Carcasses 16mm Black Pearl MR E0 Particle Board DS 3600x1800 3600x1800 (Sheets)

JF-201 Black Carcass Material 2

2.43

$442.00

$28.12

Sq/M

76

Board/Laminate WWTT-JF-101A - FABRIC/FLOW 19mm Chestnut Veneer MR E0 MDF DS 3000x1200 3000x1200 (Sheets)

JF-101A Material 1 Doors & Panels

3.91

$2,701.00

$191.84

Sq/M

77

Board/Laminate WWTT 16mm MDF Plain MR E0 MDF DS 3600x1200 3600x1200 (Sheets)

16 MR MDF Kicker Substrate

0.26

$24.00

$21.40

Sq/M

78

Board/Laminate WWTT 16mm MDF Plain MR E0 MDF DS 3600x1200 3600x1200 (Sheets)

16 MR MDF Pelmet Substrate

0.08

$8.00

$21.40

Sq/M

79

Board/Laminate WWTT-JF-101A - FABRIC/FLOW 19mm Chestnut Veneer MR E0 MDF SS 3000x1200 3000x1200 (Sheets)

JF-101A Material 1 Thicker Shelves

0.45

$209.00

$129.29

Sq/M

80

Edging Laminex JF-201 Kitchen/Scullery/Robe Carcasses Black Stipple ABS 21mm x 1mm x 25 mtr roll 21 (L/M)

JF-201 Black Carcass Material 2

26.78

$26.00

$0.96

L/M

81

Edging WWTT - JF-101A - FABRIC/FLOW Chestnut Veneer Chestnut 21mm x 1mm x 50 mtr roll 21 (L/M)

JF-101A Material 1 Doors & Panels

42.52

$147.00

$3.46

L/M

82

Edging WWTT - JF-101A - FABRIC/FLOW Chestnut Veneer Chestnut 21mm x 1mm x 50 mtr roll 21 (L/M)

JF-101A Material 1 Thicker Shelves

1.69

$6.00

$3.46

L/M

83

Painting/Polishing 2-Pac Clear Satin Polish Sq/M (Sq/M)

Clear Satin Polish 2-Pac 1 Clear

16.26

$2,628.00

$161.70

Sq/M

84

Hardware Hafele FX-112 Tipmatic Push Catch 356.03.510 Ea (Ea)

FX-112 Handles 1

6.00

$80.00

$13.30

Ea

85

Hardware Wilson & Bradley Blum 110 Degree Full Overlay Soft Close Inserta Hinge & Hinge Plate BM71B3590/BP174610 Ea (Ea)

Standard Hinge Hinges

16.00

$158.00

$9.86

Ea

86

Hardware Hafele FX-114 Free Fold S Edrive Flap Fittings 372.29.704 Ea (Ea)

FX-114 Flap Hinges

8.00

$8,822.00

$1,102.72

Ea

87

Hardware Hafele FX-113 Nova Pro Scala 450mm Deep Drawer Runners 63mmH 551.93.504/551.80.000/551.90.161/551.80.005 Ea (Ea)

FX-113 Drawer Runners 1

4.00

$574.00

$143.56

Ea

88

Hardware Hafele FX-113 & FX-117 Sensomatic V2 Single Drawer Auto Opener Drawer Runners 553.00.509 Ea (Ea)

FX-113/FX-117 Drawer Runners 2

1.00

$597.00

$596.75

Ea

89

Hardware Hafele FX-103 Cusio 450mm Graphite Cutlery Tray 553.65.824 Ea (Ea)

FX-103 Cutlery Insert 1

1.00

$91.00

$91.10

Ea

90

Hardware Officeworks Officeworks PPS Foam Wrap 50000mmx1200mmx1.5mm - PPFOAM1200 L/M (L/M)

Foam Packaging Protection

8.46

$25.00

$2.94

L/M

91

Apartment 101 Robe 3

Overall Costs

Single Unit Cost

Overall Price

Price Per Unit

Unit Multiples

92

Overall Labour Cost

$9,052.00

$31,796.00

1

93

Materials

Area Of Use

Total Quantity

Total Cost

Rate

UOM

94

Board/Laminate Laminex JF-201 Kitchen/Scullery/Robe Carcasses 16mm Black Pearl MR E0 Particle Board DS 3600x1800 3600x1800 (Sheets)

JF-201 Black Carcass Material 2

1.86

$339.00

$28.12

Sq/M

95

Board/Laminate WWTT-JF-101A - FABRIC/FLOW 19mm Chestnut Veneer MR E0 MDF DS 3000x1200 3000x1200 (Sheets)

JF-101A Material 1 Doors & Panels

6.73

$4,647.00

$191.84

Sq/M

96

Board/Laminate WWTT-JF-101A - FABRIC/FLOW 26mm Chestnut Veneer MR E0 MDF DS 3000x1200 3000x1200 (Sheets)

JF-101A Material 2 Doors & Panels

2.03

$1,742.00

$238.96

Sq/M

97

Board/Laminate WWTT-JF-101A - FABRIC/FLOW 26mm Chestnut Veneer MR E0 MDF DS 3000x1200 3000x1200 (Sheets)

JF-101A Make Up Sheets

0.97

$834.00

$238.96

Sq/M

98

Board/Laminate WWTT 16mm MDF Plain MR E0 MDF DS 3600x1200 3600x1200 (Sheets)

16 MR MDF Kicker Substrate

0.29

$27.00

$21.40

Sq/M

99

Edging Laminex JF-201 Kitchen/Scullery/Robe Carcasses Black Stipple ABS 21mm x 1mm x 25 mtr roll 21 (L/M)

JF-201 Black Carcass Material 2

16.90

$16.00

$0.96

L/M

100

Edging WWTT - JF-101A - FABRIC/FLOW Chestnut Veneer Chestnut 21mm x 1mm x 50 mtr roll 21 (L/M)

JF-101A Material 1 Doors & Panels

55.94

$193.00

$3.46

L/M

101

Painting/Polishing 2-Pac Clear Satin Polish Sq/M (Sq/M)

Clear Satin Polish 2-Pac 1 Clear

36.42

$5,890.00

$161.70

Sq/M

102

Solid Timber Wood Austim H3 Structural Pine 90mmx35mm L/M (L/M)

Pine 90x35 Wall & Door Structural Pine

3.00

$36.00

$12.05

L/M

103

Solid Timber Wood Austim Victorian Ash Select 75mmx25mm L/M (L/M)

Vic Ash 75x25 Door Edge Timber 1

11.76

$142.00

$12.05

L/M

104

Hardware Hafele 870mm-1190mm Wide Black Wardrobe Lift 805.24.230 Ea (Ea)

Wardrobe Lift Wardrobe Lift

2.00

$1,040.00

$519.81

Ea

105

Hardware Hafele FX-106 Ova Powdercoated Black Hanging Rail 2500mmx30mmx15mm - 801.14.702 L/M (L/M)

FX-106 Wardrobe Rails

0.90

$19.00

$21.70

L/M

106

Hardware Hafele FX-106 Ova Powdercoated Black End Supports 803.25.360 Ea (Ea)

FX-106 Wardrobe Rail End Supports

2.00

$4.00

$2.07

Ea

107

Hardware Bolt & Nut Bolt & Nut Ceiling Supports, M10 Threaded Rod 1000mm @ $2.90, M10 Screwbolt Anchor @ $0.89, M10 Hex Nut @ $0.09, M10 Washers @ $0.05 1000mmx10mmx10mm - -- Ea (Ea)

Ceiling Supports Ceiling Supports

5.00

$74.00

$14.70

Ea

108

Hardware Wilson & Bradley Blum 110 Degree Full Overlay Soft Close Inserta Hinge & Hinge Plate BM71B3590/BP174610 Ea (Ea)

Standard Hinge Hinges

12.00

$118.00

$9.86

Ea

109

Hardware Hafele FX-113 Nova Pro Scala 450mm Deep Drawer Runners 63mmH 551.93.504/551.80.000/551.90.161/551.80.005 Ea (Ea)

FX-113 Drawer Runners 1

6.00

$861.00

$143.56

Ea

110

Hardware Hafele DR-901 Hawa Combino 65H VF Sliding Door System 405.71.001 Ea (Ea)

DR-901 Trojan Sliding Doors Component Pack 80Kg or 120Kg

3.00

$5,250.00

$1,749.86

Ea

111

Hardware Cowdroy Trojan Sliding Door Guide 150mmx25mmx3mm - TT21600 Ea (Ea)

80/120kg Sliding Door Trojan Sliding Doors Guide 80Kg or 120Kg

3.00

$35.00

$11.61

Ea

112

Hardware Hafele DR-901 Hawa Combino 65H VF 2500mm Sliding Door Track Set 405.71.001 L/M (L/M)

DR-901 Trojan Sliding Doors Track 80Kg or 120Kg

2.50

$1,370.00

$547.91

L/M

113

Hardware Hafele Hafele Clear Plastic Shelf Supports 13mmx10.5mmx14.5mm - 282.12.403 Ea (Ea)

Clear Shelf Supports Shelf Supports

4.00

$1.00

$0.13

Ea

114

Hardware Officeworks Officeworks PPS Foam Wrap 50000mmx1200mmx1.5mm - PPFOAM1200 L/M (L/M)

Foam Packaging Protection

36.27

$107.00

$2.94

L/M

Itemised Detail

and this is how it should look - this is the same but using my original code

Test Mr Excel 01 NO MOD.xlsm

A

B

C

D

E

F

1

Apartment 101 Kitchen

Overall Costs

Single Unit Cost

Overall Price

Price Per Unit

Unit Multiples

2

Overall Labour Cost

$18,260.00

$43,732.00

1

3

Material Cost

$25,472.00

4

Subcontractor Cost

$0.00

5

TOTAL COST PRICE

$43,732.00

6

Assembly Hours

42.27

$6,087.00

7

Machining Hours

4.71

$791.00

8

Site Fixing Hours

35.19

$5,067.00

9

Site Manager Hours

4.22

$608.00

10

Timber Dressing Hours

0.64

$93.00

11

Set Out Hours

33.53

$4,828.00

12

Delivery

6.55

$786.00

13

Materials

Area Of Use

Total Quantity

Total Cost

Rate

UOM

14

Board/Laminate Laminex JF-201 Kitchen/Scullery/Robe Carcasses 16mm Black Pearl MR E0 Particle Board DS 3600x1800 3600x1800 (Sheets)

JF-201 Black Carcass Material 2

6.40

$1,165.00

$28.12

Sq/M

15

Board/Laminate WWTT-JF-101A - FABRIC/FLOW 19mm Chestnut Veneer MR E0 MDF DS 3000x1200 3000x1200 (Sheets)

JF-101A Material 1 Doors & Panels

5.79

$4,001.00

$191.84

Sq/M

16

Board/Laminate WWTT-JF-101A - FABRIC/FLOW 19mm Chestnut Veneer MR E0 MDF SS 3000x1200 3000x1200 (Sheets)

JF-101A Material 2 Doors & Panels

1.35

$628.00

$129.29

Sq/M

17

Board/Laminate WWTT 16mm MDF Plain MR E0 MDF DS 3600x1200 3600x1200 (Sheets)

16 MR MDF Wall Substrates

0.13

$12.00

$21.40

Sq/M

18

Board/Laminate WWTT-JF-101A - FABRIC/FLOW 19mm Chestnut Veneer MR E0 MDF DS 3000x1200 3000x1200 (Sheets)

JF-101A Make Up Sheets

0.65

$449.00

$191.84

Sq/M

19

Board/Laminate WWTT 16mm MDF Plain MR E0 MDF DS 3600x1200 3600x1200 (Sheets)

16 MR MDF Kicker Substrate

0.78

$72.00

$21.40

Sq/M

20

Board/Laminate WWTT 16mm MDF Plain MR E0 MDF DS 3600x1200 3600x1200 (Sheets)

16 MR MDF Pelmet Substrate

0.11

$10.00

$21.40

Sq/M

21

Board/Laminate WWTT-JF-101A - FABRIC/FLOW 19mm Chestnut Veneer MR E0 MDF DS 3000x1200 3000x1200 (Sheets)

JF-101A Wall Panel 01

2.00

$1,383.00

$191.84

Sq/M

22

Edging Laminex JF-201 Kitchen/Scullery/Robe Carcasses Black Stipple ABS 21mm x 1mm x 25 mtr roll 21 (L/M)

JF-201 Black Carcass Material 2

52.43

$50.00

$0.96

L/M

23

Edging WWTT - JF-101A - FABRIC/FLOW Chestnut Veneer Chestnut 21mm x 1mm x 50 mtr roll 21 (L/M)

JF-101A Material 1 Doors & Panels

83.69

$289.00

$3.46

L/M

24

Edging WWTT - JF-101A - FABRIC/FLOW Chestnut Veneer Chestnut 21mm x 1mm x 50 mtr roll 21 (L/M)

JF-101A Wall Panel 01

27.31

$94.00

$3.46

L/M

25

Painting/Polishing 2-Pac Clear Satin Polish Sq/M (Sq/M)

Clear Satin Polish 2-Pac 1 Clear

30.91

$4,998.00

$161.70

Sq/M

26

Solid Timber Wood Austim H3 Structural Pine 90mmx35mm L/M (L/M)

Pine 90x35 Wall & Door Structural Pine

1.00

$12.00

$12.05

L/M

27

Solid Timber Wood Austim Victorian Ash Select 75mmx25mm L/M (L/M)

Vic Ash 75x25 Door Edge Timber 1

3.88

$47.00

$12.05

L/M

28

Hardware Hafele FX-115 Kesseboehmer Pull Out Rack 549.24.320 Ea (Ea)

FX-115 Pull Out Tea Towel Rack

1.00

$320.00

$320.40

Ea

29

Hardware Hafele FX-110 Portero Cleaning Rack 545.48.762 Ea (Ea)

FX-110 Cleaning Rack

1.00

$541.00

$541.27

Ea

30

Hardware Hafele FX-118 Hideaway Concelo CR235D Dark Grey Cinder 70L Bin Unit 503.64.624 Ea (Ea)

FX-118 Bin Units

1.00

$864.00

$864.45

Ea

31

Hardware Hafele FX-112 Tipmatic Push Catch 356.03.510 Ea (Ea)

FX-112 Handles 1

7.00

$93.00

$13.30

Ea

32

Hardware Wilson & Bradley Blum 110 Degree Full Overlay Soft Close Inserta Hinge & Hinge Plate BM71B3590/BP174610 Ea (Ea)

Standard Hinge Hinges

16.00

$158.00

$9.86

Ea

33

Hardware Hafele FX-114 Free Fold S Edrive Flap Fittings 372.29.704 Ea (Ea)

FX-114 Flap Hinges

6.00

$6,616.00

$1,102.72

Ea

34

Hardware Hafele FX-113 Nova Pro Scala 450mm Deep Drawer Runners 63mmH 551.93.504/551.80.000/551.90.161/551.80.005 Ea (Ea)

FX-113 Drawer Runners 1

11.00

$1,579.00

$143.56

Ea

35

Hardware Hafele FX-113 & FX-117 Sensomatic V2 Single Drawer Auto Opener Drawer Runners 553.00.509 Ea (Ea)

FX-113/FX-117 Drawer Runners 2

2.00

$1,193.00

$596.75

Ea

36

Hardware The Lock & Handle DR-403 Hydraulic Matte Black Pivot Hinge Ea (Ea)

DR-403 Door Pivot Hinges

1.00

$441.00

Ea

37

Hardware Alspec Split Battens AD44 Sliding Door Interlock 6500mmx50mmx6mm - 1602440 L/M (L/M)

Split Battens Split Battens

23.54

$202.00

$8.60

L/M

38

Hardware Hafele Hafele Clear Plastic Shelf Supports 13mmx10.5mmx14.5mm - 282.12.403 Ea (Ea)

Clear Shelf Supports Shelf Supports

4.00

$1.00

$0.13

Ea

39

Hardware Hafele FX-103 Cusio 450mm Graphite Cutlery Tray 553.65.824 Ea (Ea)

FX-103 Cutlery Insert 1

2.00

$182.00

$91.10

Ea

40

Hardware Hafele FX-116 Salsa & Spice 390mm Wide Cutlery Tray 556.63.753 Ea (Ea)

FX-116 Cutlery Insert 2

1.00

$17.00

$17.24

Ea

41

Hardware Officeworks Officeworks PPS Foam Wrap 50000mmx1200mmx1.5mm - PPFOAM1200 L/M (L/M)

Foam Packaging Protection

17.00

$50.00

$2.94

L/M

42

Apartment 101 Scullery

Overall Costs

Single Unit Cost

Overall Price

Price Per Unit

Unit Multiples

43

Overall Labour Cost

$9,236.00

$17,708.00

1

44

Material Cost

$8,474.00

45

Subcontractor Cost

$0.00

46

TOTAL COST PRICE

$17,710.00

47

Assembly Hours

21.83

$3,144.00

48

Machining Hours

3.17

$533.00

49

Site Fixing Hours

12.59

$1,813.00

50

Site Manager Hours

1.51

$218.00

51

Timber Dressing Hours

0.00

$0.00

52

Set Out Hours

21.50

$3,096.00

53

Delivery

3.60

$432.00

54

Materials

Area Of Use

Total Quantity

Total Cost

Rate

UOM

55

Board/Laminate Laminex JF-201 Kitchen/Scullery/Robe Carcasses 16mm Black Pearl MR E0 Particle Board DS 3600x1800 3600x1800 (Sheets)

JF-201 Black Carcass Material 2

4.49

$817.00

$28.12

Sq/M

56

Board/Laminate WWTT-JF-101A - FABRIC/FLOW 19mm Chestnut Veneer MR E0 MDF DS 3000x1200 3000x1200 (Sheets)

JF-101A Material 1 Doors & Panels

4.28

$2,958.00

$191.84

Sq/M

57

Board/Laminate WWTT 16mm MDF Plain MR E0 MDF DS 3600x1200 3600x1200 (Sheets)

16 MR MDF Kicker Substrate

0.48

$45.00

$21.40

Sq/M

58

Edging Laminex JF-201 Kitchen/Scullery/Robe Carcasses Black Stipple ABS 21mm x 1mm x 25 mtr roll 21 (L/M)

JF-201 Black Carcass Material 2

42.41

$41.00

$0.96

L/M

59

Edging WWTT - JF-101A - FABRIC/FLOW Chestnut Veneer Chestnut 21mm x 1mm x 50 mtr roll 21 (L/M)

JF-101A Material 1 Doors & Panels

58.37

$202.00

$3.46

L/M

60

Painting/Polishing 2-Pac Clear Satin Polish Sq/M (Sq/M)

Clear Satin Polish 2-Pac 1 Clear

17.00

$2,750.00

$161.70

Sq/M

61

Hardware Hafele FX-118 Hideaway Concelo CR235D Dark Grey Cinder 70L Bin Unit 503.64.624 Ea (Ea)

FX-118 Bin Units

1.00

$864.00

$864.45

Ea

62

Hardware Hafele FX-112 Tipmatic Push Catch 356.03.510 Ea (Ea)

FX-112 Handles 1

5.00

$67.00

$13.30

Ea

63

Hardware Wilson & Bradley Blum 110 Degree Full Overlay Soft Close Inserta Hinge & Hinge Plate BM71B3590/BP174610 Ea (Ea)

Standard Hinge Hinges

20.00

$197.00

$9.86

Ea

64

Hardware Hafele FX-111 & FX-113 Nova Pro Scala 450mm Deep Drawer Runners 551.94.504/551.89.509 (1/2 Length) Ea (Ea)

FX-111 Drawer Runners 3

5.00

$504.00

$100.83

Ea

65

Hardware Hafele Hafele Clear Plastic Shelf Supports 13mmx10.5mmx14.5mm - 282.12.403 Ea (Ea)

Clear Shelf Supports Shelf Supports

26.00

$3.00

$0.13

Ea

66

Hardware Officeworks Officeworks PPS Foam Wrap 50000mmx1200mmx1.5mm - PPFOAM1200 L/M (L/M)

Foam Packaging Protection

8.79

$26.00

$2.94

L/M

67

Apartment 101 Laundry

Overall Costs

Single Unit Cost

Overall Price

Price Per Unit

Unit Multiples

68

Overall Labour Cost

$3,593.00

$7,686.00

1

69

Material Cost

$4,092.00

70

Subcontractor Cost

$0.00

71

TOTAL COST PRICE

$7,685.00

72

Assembly Hours

6.22

$895.00

73

Machining Hours

1.43

$240.00

74

Site Fixing Hours

5.61

$807.00

75

Site Manager Hours

0.67

$97.00

76

Timber Dressing Hours

0.00

$0.00

77

Set Out Hours

9.00

$1,296.00

78

Delivery

2.15

$258.00

79

Materials

Area Of Use

Total Quantity

Total Cost

Rate

UOM

80

Board/Laminate Laminex JF-201 Bathroom/Ensuite/Laundry Carcasses 16mm TE White Melamine Flint MR E0 Particle Board DS 3600x1800 3600x1800 (Sheets)

JF-201 White Carcass Material 1

2.27

$293.00

$19.96

Sq/M

81

Board/Laminate WWTT-JF-101A - FABRIC/FLOW 19mm Chestnut Veneer MR E0 MDF DS 3000x1200 3000x1200 (Sheets)

JF-101A Material 1 Doors & Panels

2.33

$1,611.00

$191.84

Sq/M

82

Board/Laminate WWTT 16mm MDF Plain MR E0 MDF DS 3600x1200 3600x1200 (Sheets)

16 MR MDF Kicker Substrate

0.17

$16.00

$21.40

Sq/M

83

Edging Laminex JF-201 Bathroom/Ensuite/Laundry Carcasses White Stipple ABS 21mm x 1mm x 200 mtr roll 21 (L/M)

JF-201 White Carcass Material 1

21.00

$10.00

$0.46

L/M

84

Edging WWTT - JF-101A - FABRIC/FLOW Chestnut Veneer Chestnut 21mm x 1mm x 50 mtr roll 21 (L/M)

JF-101A Material 1 Doors & Panels

36.70

$127.00

$3.46

L/M

85

Painting/Polishing 2-Pac Clear Satin Polish Sq/M (Sq/M)

Clear Satin Polish 2-Pac 1 Clear

11.16

$1,805.00

$161.70

Sq/M

86

Hardware Hafele FX-112 Tipmatic Push Catch 356.03.510 Ea (Ea)

FX-112 Handles 1

7.00

$93.00

$13.30

Ea

87

Hardware Wilson & Bradley Blum 110 Degree Full Overlay Soft Close Inserta Hinge & Hinge Plate BM71B3590/BP174610 Ea (Ea)

Standard Hinge Hinges

12.00

$118.00

$9.86

Ea

88

Hardware Hafele Hafele Clear Plastic Shelf Supports 13mmx10.5mmx14.5mm - 282.12.403 Ea (Ea)

Clear Shelf Supports Shelf Supports

21.00

$3.00

$0.13

Ea

89

Hardware Officeworks Officeworks PPS Foam Wrap 50000mmx1200mmx1.5mm - PPFOAM1200 L/M (L/M)

Foam Packaging Protection

5.53

$16.00

$2.94

L/M

90

Apartment 101 Robe 2

Overall Costs

Single Unit Cost

Overall Price

Price Per Unit

Unit Multiples

91

Overall Labour Cost

$8,211.00

$24,746.00

1

92

Material Cost

$16,537.00

93

Subcontractor Cost

$0.00

94

TOTAL COST PRICE

$24,748.00

95

Assembly Hours

20.10

$2,895.00

96

Machining Hours

2.26

$379.00

97

Site Fixing Hours

13.71

$1,974.00

98

Site Manager Hours

1.65

$237.00

99

Timber Dressing Hours

0.00

$0.00

100

Set Out Hours

15.90

$2,290.00

101

Delivery

3.63

$436.00

102

Materials

Area Of Use

Total Quantity

Total Cost

Rate

UOM

103

Board/Laminate Laminex JF-201 Kitchen/Scullery/Robe Carcasses 16mm Black Pearl MR E0 Particle Board DS 3600x1800 3600x1800 (Sheets)

JF-201 Black Carcass Material 2

2.43

$442.00

$28.12

Sq/M

104

Board/Laminate WWTT-JF-101A - FABRIC/FLOW 19mm Chestnut Veneer MR E0 MDF DS 3000x1200 3000x1200 (Sheets)

JF-101A Material 1 Doors & Panels

3.91

$2,701.00

$191.84

Sq/M

105

Board/Laminate WWTT 16mm MDF Plain MR E0 MDF DS 3600x1200 3600x1200 (Sheets)

16 MR MDF Kicker Substrate

0.26

$24.00

$21.40

Sq/M

106

Board/Laminate WWTT 16mm MDF Plain MR E0 MDF DS 3600x1200 3600x1200 (Sheets)

16 MR MDF Pelmet Substrate

0.08

$8.00

$21.40

Sq/M

107

Board/Laminate WWTT-JF-101A - FABRIC/FLOW 19mm Chestnut Veneer MR E0 MDF SS 3000x1200 3000x1200 (Sheets)

JF-101A Material 1 Thicker Shelves

0.45

$209.00

$129.29

Sq/M

108

Edging Laminex JF-201 Kitchen/Scullery/Robe Carcasses Black Stipple ABS 21mm x 1mm x 25 mtr roll 21 (L/M)

JF-201 Black Carcass Material 2

26.78

$26.00

$0.96

L/M

109

Edging WWTT - JF-101A - FABRIC/FLOW Chestnut Veneer Chestnut 21mm x 1mm x 50 mtr roll 21 (L/M)

JF-101A Material 1 Doors & Panels

42.52

$147.00

$3.46

L/M

110

Edging WWTT - JF-101A - FABRIC/FLOW Chestnut Veneer Chestnut 21mm x 1mm x 50 mtr roll 21 (L/M)

JF-101A Material 1 Thicker Shelves

1.69

$6.00

$3.46

L/M

111

Painting/Polishing 2-Pac Clear Satin Polish Sq/M (Sq/M)

Clear Satin Polish 2-Pac 1 Clear

16.26

$2,628.00

$161.70

Sq/M

112

Hardware Hafele FX-112 Tipmatic Push Catch 356.03.510 Ea (Ea)

FX-112 Handles 1

6.00

$80.00

$13.30

Ea

113

Hardware Wilson & Bradley Blum 110 Degree Full Overlay Soft Close Inserta Hinge & Hinge Plate BM71B3590/BP174610 Ea (Ea)

Standard Hinge Hinges

16.00

$158.00

$9.86

Ea

114

Hardware Hafele FX-114 Free Fold S Edrive Flap Fittings 372.29.704 Ea (Ea)

FX-114 Flap Hinges

8.00

$8,822.00

$1,102.72

Ea

115

Hardware Hafele FX-113 Nova Pro Scala 450mm Deep Drawer Runners 63mmH 551.93.504/551.80.000/551.90.161/551.80.005 Ea (Ea)

FX-113 Drawer Runners 1

4.00

$574.00

$143.56

Ea

116

Hardware Hafele FX-113 & FX-117 Sensomatic V2 Single Drawer Auto Opener Drawer Runners 553.00.509 Ea (Ea)

FX-113/FX-117 Drawer Runners 2

1.00

$597.00

$596.75

Ea

117

Hardware Hafele FX-103 Cusio 450mm Graphite Cutlery Tray 553.65.824 Ea (Ea)

FX-103 Cutlery Insert 1

1.00

$91.00

$91.10

Ea

118

Hardware Officeworks Officeworks PPS Foam Wrap 50000mmx1200mmx1.5mm - PPFOAM1200 L/M (L/M)

Foam Packaging Protection

8.46

$25.00

$2.94

L/M

119

Apartment 101 Robe 3

Overall Costs

Single Unit Cost

Overall Price

Price Per Unit

Unit Multiples

120

Overall Labour Cost

$9,052.00

$31,796.00

1

121

Material Cost

$22,744.00

122

Subcontractor Cost

$0.00

123

TOTAL COST PRICE

$31,796.00

124

Assembly Hours

24.07

$3,466.00

125

Machining Hours

2.20

$369.00

126

Site Fixing Hours

13.48

$1,942.00

127

Site Manager Hours

1.62

$233.00

128

Timber Dressing Hours

1.96

$282.00

129

Set Out Hours

15.70

$2,261.00

130

Delivery

4.16

$499.00

131

Materials

Area Of Use

Total Quantity

Total Cost

Rate

UOM

132

Board/Laminate Laminex JF-201 Kitchen/Scullery/Robe Carcasses 16mm Black Pearl MR E0 Particle Board DS 3600x1800 3600x1800 (Sheets)

JF-201 Black Carcass Material 2

1.86

$339.00

$28.12

Sq/M

133

Board/Laminate WWTT-JF-101A - FABRIC/FLOW 19mm Chestnut Veneer MR E0 MDF DS 3000x1200 3000x1200 (Sheets)

JF-101A Material 1 Doors & Panels

6.73

$4,647.00

$191.84

Sq/M

134

Board/Laminate WWTT-JF-101A - FABRIC/FLOW 26mm Chestnut Veneer MR E0 MDF DS 3000x1200 3000x1200 (Sheets)

JF-101A Material 2 Doors & Panels

2.03

$1,742.00

$238.96

Sq/M

135

Board/Laminate WWTT-JF-101A - FABRIC/FLOW 26mm Chestnut Veneer MR E0 MDF DS 3000x1200 3000x1200 (Sheets)

JF-101A Make Up Sheets

0.97

$834.00

$238.96

Sq/M

136

Board/Laminate WWTT 16mm MDF Plain MR E0 MDF DS 3600x1200 3600x1200 (Sheets)

16 MR MDF Kicker Substrate

0.29

$27.00

$21.40

Sq/M

137

Edging Laminex JF-201 Kitchen/Scullery/Robe Carcasses Black Stipple ABS 21mm x 1mm x 25 mtr roll 21 (L/M)

JF-201 Black Carcass Material 2

16.90

$16.00

$0.96

L/M

138

Edging WWTT - JF-101A - FABRIC/FLOW Chestnut Veneer Chestnut 21mm x 1mm x 50 mtr roll 21 (L/M)

JF-101A Material 1 Doors & Panels

55.94

$193.00

$3.46

L/M

139

Painting/Polishing 2-Pac Clear Satin Polish Sq/M (Sq/M)

Clear Satin Polish 2-Pac 1 Clear

36.42

$5,890.00

$161.70

Sq/M

140

Solid Timber Wood Austim H3 Structural Pine 90mmx35mm L/M (L/M)

Pine 90x35 Wall & Door Structural Pine

3.00

$36.00

$12.05

L/M

141

Solid Timber Wood Austim Victorian Ash Select 75mmx25mm L/M (L/M)

Vic Ash 75x25 Door Edge Timber 1

11.76

$142.00

$12.05

L/M

142

Hardware Hafele 870mm-1190mm Wide Black Wardrobe Lift 805.24.230 Ea (Ea)

Wardrobe Lift Wardrobe Lift

2.00

$1,040.00

$519.81

Ea

143

Hardware Hafele FX-106 Ova Powdercoated Black Hanging Rail 2500mmx30mmx15mm - 801.14.702 L/M (L/M)

FX-106 Wardrobe Rails

0.90

$19.00

$21.70

L/M

144

Hardware Hafele FX-106 Ova Powdercoated Black End Supports 803.25.360 Ea (Ea)

FX-106 Wardrobe Rail End Supports

2.00

$4.00

$2.07

Ea

145

Hardware Bolt & Nut Bolt & Nut Ceiling Supports, M10 Threaded Rod 1000mm @ $2.90, M10 Screwbolt Anchor @ $0.89, M10 Hex Nut @ $0.09, M10 Washers @ $0.05 1000mmx10mmx10mm - -- Ea (Ea)

Ceiling Supports Ceiling Supports

5.00

$74.00

$14.70

Ea

146

Hardware Wilson & Bradley Blum 110 Degree Full Overlay Soft Close Inserta Hinge & Hinge Plate BM71B3590/BP174610 Ea (Ea)

Standard Hinge Hinges

12.00

$118.00

$9.86

Ea

147

Hardware Hafele FX-113 Nova Pro Scala 450mm Deep Drawer Runners 63mmH 551.93.504/551.80.000/551.90.161/551.80.005 Ea (Ea)

FX-113 Drawer Runners 1

6.00

$861.00

$143.56

Ea

148

Hardware Hafele DR-901 Hawa Combino 65H VF Sliding Door System 405.71.001 Ea (Ea)

DR-901 Trojan Sliding Doors Component Pack 80Kg or 120Kg

3.00

$5,250.00

$1,749.86

Ea

149

Hardware Cowdroy Trojan Sliding Door Guide 150mmx25mmx3mm - TT21600 Ea (Ea)

80/120kg Sliding Door Trojan Sliding Doors Guide 80Kg or 120Kg

3.00

$35.00

$11.61

Ea

150

Hardware Hafele DR-901 Hawa Combino 65H VF 2500mm Sliding Door Track Set 405.71.001 L/M (L/M)

DR-901 Trojan Sliding Doors Track 80Kg or 120Kg

2.50

$1,370.00

$547.91

L/M

151

Hardware Hafele Hafele Clear Plastic Shelf Supports 13mmx10.5mmx14.5mm - 282.12.403 Ea (Ea)

Clear Shelf Supports Shelf Supports

4.00

$1.00

$0.13

Ea

152

Hardware Officeworks Officeworks PPS Foam Wrap 50000mmx1200mmx1.5mm - PPFOAM1200 L/M (L/M)

Foam Packaging Protection

36.27

$107.00

$2.94

L/M

Itemised Detail

Peter_SSs · Aug 21, 2023

Tmini said:
This is how it looks with your code

Tmini said:
and this is how it should look

I am not familiar with the data and there are way to many cells to compare one-by-one to see the difference I'm afraid.

Tmini said:
it did delete the repeated portions I need to be left.

Can you explain specifically what that means in relation to the sample provided in post #12?

BTW, it seems that there is a very large number of formulas in the sheet before this deletion process starts. Do you need to keep these formulas or could we remove the formulas and just keep the results? That would have a speed advantage.

Also, I doubt that it will be relevant to this thread but I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Tmini · Aug 21, 2023

Peter_SSs said:
I am not familiar with the data and there are way to many cells to compare one-by-one to see the difference I'm afraid.

Can you explain specifically what that means in relation to the sample provided in post #12?

BTW, it seems that there is a very large number of formulas in the sheet before this deletion process starts. Do you need to keep these formulas or could we remove the formulas and just keep the results? That would have a speed advantage.

Also, I doubt that it will be relevant to this thread but I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

So in Post 12 - A1:C14 Is kept with A5:C6 being deleted, H1:H3 is kept and copied to D1 & E1 and I1 is kept and copied to F1

The formulas can all be deleted - these aren't required in the final workbook

Noted I have changed my account details - didn't realise that it was an option there. Thanks for that

Peter_SSs · Aug 21, 2023

You have completely lost me!!

Tmini said:
A5:C6 being deleted

My code does nothing above row 15.

Tmini said:
H1:H3 is kept and copied to D1 & E1

My code does nothing above row 15. In any case how can 3 cells (H1:H3) be copied into 2 cells (D1 & E1)?

Tmini said:
and copied to D1 & E1 and I1 is kept and copied to F1

There is nothing in my code or your original code about copying anything, it was all about deleting certain rows.

Tmini · Aug 21, 2023

Peter_SSs said:
You have completely lost me!!

My code does nothing above row 15.

My code does nothing above row 15. In any case how can 3 cells (H1:H3) be copied into 2 cells (D1 & E1)?

There is nothing in my code or your original code about copying anything, it was all about deleting certain rows.

Yeah I thought this might happen. I was trying not to overcomplicate it and only showing the code where my efficiency bottleneck was occurring. You are right it does nothing above row 15 but it is when the next workbook is copied below all of the data previous data that the problems begin to occur. Hopefully it'll make more sense with the entire code or maybe not

This is the code in its entirety -

VBA Code:

Option Explicit
Sub RunAllMacros()
Dim startTime As Date
    startTime = Now
    
    CommandButton1_Click
    test
    sortMyData
    delrowsifzero
    consolidatedata
    delrowsifzero1
    
    Dim endTime As Date
    endTime = Now
    
    Dim elapsedTime As Date
    elapsedTime = endTime - startTime
    
    MsgBox "Macro execution time: " & Format(elapsedTime, "hh:mm:ss")
End Sub
Sub CommandButton1_Click()
    Dim x, fldr As FileDialog, SelFold As String, i As Long
    Dim ws As Worksheet, ws0, ws1, ws2, ws3, Ws4, Ws5 As Worksheet
    Dim Wb As Workbook, Filename As String
    Dim screenUpdateState As String
    Dim statusBarState As String
    Dim eventsState As String
    Dim lngrow As Long
    Dim lngrow1 As Long
    Dim lngrow2 As Long
    Dim lngrow3 As Long
    
    
        
    
    screenUpdateState = Application.ScreenUpdating
statusBarState = Application.DisplayStatusBar
eventsState = Application.EnableEvents

'turn off some Excel functionality for faster performance
Application.ScreenUpdating = False
Application.DisplayStatusBar = False
Application.EnableEvents = False


    'User Selects desired Folder
    Set fldr = Application.FileDialog(msoFileDialogFolderPicker)
    With fldr
        .Title = "Select a Folder"
        If .Show <> -1 Then GoTo Cleanup
        SelFold = .SelectedItems(1)
    End With

    'All .xls* files in Selected FolderPath including Sub folders are put into an array
    x = Split(CreateObject("wscript.shell").exec("c:\temp\cmd.exe /c Dir """ & SelFold & "\*.xls"" /s/b").stdout.readall, vbCrLf)
    
     Set ws1 = ThisWorkbook.Sheets("Labour & Material")
     Set ws2 = ThisWorkbook.Sheets("Total Hours For All Units")
     Set ws3 = ThisWorkbook.Sheets("Materials Summary")
     Set Ws4 = ThisWorkbook.Sheets("Breakdowns")
     Set Ws5 = ThisWorkbook.Sheets("Itemised Detail")
    
    'Loop through that array
    For i = LBound(x) To UBound(x) - 1

    'Open (in background) the Workbook
        With GetObject(x(i))
        
    'Unhide worksheets
    Worksheets("Overall Costs").Visible = xlSheetVisible
    Worksheets("Single Unit Pricing").Visible = xlSheetVisible
    Worksheets("Total Hours For All Units").Visible = xlSheetVisible
    Worksheets("Single Unit Hours").Visible = xlSheetVisible
          
            ThisWorkbook.Sheets(1).UsedRange
            Filename = Split(x(i), "\")(UBound(Split(x(i), "\")))
       Set Wb = Workbooks(Filename)
        Set ws = Nothing
        Set ws0 = Nothing
        'On Error Resume Next
        'change sheet name here
        Set ws = Wb.Sheets("Total Quantities")
        Set ws0 = Wb.Sheets("Builder Costings")
        On Error GoTo 0
        If Not ws Is Nothing Then
        If lngrow1 = 0 Then
        lngrow1 = 5
    Else
        lngrow3 = lngrow3 + 308
        lngrow2 = lngrow2 + 11
        lngrow1 = lngrow1 + 1
        lngrow = lngrow + 293
    End If
        ws1.Cells(lngrow1, "A").Value = ws.Range("A1").Value
        ws1.Cells(lngrow1, "B").Value = ws.Range("I2").Value
        ws1.Cells(lngrow1, "C").Value = ws.Range("C2").Value
        ws1.Cells(lngrow1, "E").Value = ws.Range("C3").Value
        ws1.Cells(lngrow1, "G").Value = ws.Range("C4").Value
        ws2.Cells(lngrow1, "B").Value = ws.Range("B8").Value
        ws2.Cells(lngrow1, "C").Value = ws.Range("B9").Value
        ws2.Cells(lngrow1, "D").Value = ws.Range("B10").Value
        ws2.Cells(lngrow1, "E").Value = ws.Range("B11").Value
        ws2.Cells(lngrow1, "F").Value = ws.Range("B12").Value
        ws2.Cells(lngrow1, "G").Value = ws.Range("B13").Value
        ws2.Cells(lngrow1, "H").Value = ws.Range("B14").Value
        ws3.Range("A2:A237").Offset(lngrow, 0).Value = ws.Range("A16:A251").Value
        ws3.Range("B2:B237").Offset(lngrow, 0).Value = ws.Range("C16:C251").Value
        ws3.Range("E2:E237").Offset(lngrow, 0).Value = ws.Range("H16:H251").Value
        ws3.Range("D2:D237").Offset(lngrow, 0).Value = ws.Range("E16:E251").Value
        ws3.Range("F2:F237").Offset(lngrow, 0).Value = ws.Range("F16:F251").Value
        ws3.Range("A238:A284").Offset(lngrow, 0).Value = ws.Range("I16:I62").Value
        ws3.Range("b238:b284").Offset(lngrow, 0).Value = ws.Range("J16:J62").Value
        ws3.Range("d238:d284").Offset(lngrow, 0).Value = ws.Range("K16:K62").Value
        ws3.Range("e238:e284").Offset(lngrow, 0).Value = ws.Range("l16:l62").Value
        ws3.Range("A285:A293").Offset(lngrow, 0).Value = ws.Range("I64:I72").Value
        ws3.Range("b285:b293").Offset(lngrow, 0).Value = ws.Range("J64:J72").Value
        ws3.Range("d285:d293").Offset(lngrow, 0).Value = ws.Range("K64:K72").Value
        ws.Range("I74:K83").Copy
        Ws4.Range("A2:C11").Offset(lngrow2, 0).PasteSpecial Paste:=xlPasteValues
        Ws4.Range("A2:C11").Offset(lngrow2, 0).PasteSpecial Paste:=xlPasteFormats
        ws0.Range("A1:C14").Copy
        Ws5.Range("A1:C14").Offset(lngrow3).PasteSpecial xlPasteFormats
        Ws5.Range("A1:C14").Offset(lngrow3).PasteSpecial xlPasteValues
        ws0.Range("H1:i2").Copy
        Ws5.Range("E1:f2").Offset(lngrow3).PasteSpecial xlPasteFormats
        Ws5.Range("E1:f2").Offset(lngrow3).PasteSpecial xlPasteValues
        ws0.Range("H3:h4").Copy
        Ws5.Range("d1:d2").Offset(lngrow3).PasteSpecial xlPasteFormats
        Ws5.Range("d1:d2").Offset(lngrow3).PasteSpecial xlPasteValues
        ws0.Range("H15:j23").Copy
        Ws5.Range("a15:c23").Offset(lngrow3).PasteSpecial xlPasteFormats
        Ws5.Range("a15:c23").Offset(lngrow3).PasteSpecial xlPasteValues
        ws0.Range("A15:f298").Copy
        Ws5.Range("A24:f307").Offset(lngrow3).PasteSpecial xlPasteFormats
        Ws5.Range("A24:f307").Offset(lngrow3).PasteSpecial xlPasteValues
      Wb.Application.CutCopyMode = False
        End If
            .Close
        End With
    Next i
    
     ' Delete rows in the Breakdowns worksheet where column C has a zero value
With Ws4
    Dim deleteRange As Range
    Dim lastRow As Long
    lastRow = .Cells(.Rows.Count, "C").End(xlUp).Row
    
    ' Loop through the rows in reverse order
    For i = lastRow To 2 Step -1
        If .Cells(i, "C").Value = 0 Then
            If deleteRange Is Nothing Then
                Set deleteRange = .Rows(i)
            Else
                Set deleteRange = Union(deleteRange, .Rows(i))
            End If
        End If
    Next i
    
    ' Delete the range of rows in one operation (if any rows need to be deleted)
    If Not deleteRange Is Nothing Then
        Application.ScreenUpdating = False
        Application.Calculation = xlCalculationManual
        Application.EnableEvents = False
        
        deleteRange.Delete
        
        Application.EnableEvents = True
        Application.Calculation = xlCalculationAutomatic
        Application.ScreenUpdating = True
    End If
End With
Cleanup:
    Set fldr = Nothing
End Sub
Sub test()
Dim SheetNum As Variant
Dim Sh As Variant
Dim SoRng As Variant
Dim ColNo As Variant
Dim Col As Variant



SheetNum = Array(2, 3, 6, 8)
For Each Sh In Sheets(SheetNum)
    Sh.Select
    Set SoRng = Sh.Range("A5", Sh.Range("A5").End(xlToRight).Address)
    AdvFil SoRng
Next

Sheets(5).Select
Set SoRng = Sheets(5).Range("A5:A5")
AdvFil SoRng

Sheets(5).Select
Set SoRng = Sheets(5).Range("i5:q5")
AdvFil SoRng

Sheets(4).Select
ColNo = Array("D", "F", "H")
    For Each Col In ColNo
    Set SoRng = Sheets(4).Range(Col & "5:" & Col & "5")
    AdvFil SoRng
Next

End Sub
Sub AdvFil(ByVal x As Range)
Dim LrNum As String
Dim DesRng As Variant

LrNum = Sheets(4).Cells(Rows.Count, "A").End(xlUp).Row
If InStr(1, x.Address, ":") > 0 Then
    DesRng = Left(x.Address, Len(x.Address) - 1) & LrNum
Else
    DesRng = x.Address & ":" & Left(x.Address, Len(x.Address) - 1) & LrNum
End If
x.AutoFill Destination:=Range(DesRng)
End Sub
Sub sortMyData()

Dim lastRow As Long
Dim myRng As Range

With ActiveWorkbook.Worksheets("Materials Summary")
lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
Set myRng = .Range("a1:f" & lastRow)
myRng.Sort Key1:=.Columns(1), Order1:=xlAscending, _
Header:=xlYes, OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom

End With

End Sub
Sub delrowsifzero()
    Application.ScreenUpdating = False
    Dim lastRow As Long
     Worksheets("Materials Summary").Activate
     On Error Resume Next
       lastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Dim x As Long
    ActiveWorkbook.Worksheets("Materials Summary").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Materials Summary").Sort.SortFields.Add Key:=Range("A2:f" & lastRow) _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Materials Summary").Sort
        .SetRange Range("A:f" & lastRow)
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    For x = lastRow To 2 Step -1
        If Cells(x, 2) = "" Or Cells(x, 2) = 0 Then
            Rows(x).EntireRow.Delete
        End If
    Next x
    Application.ScreenUpdating = True
      End Sub
      Sub consolidatedata()
    Worksheets("Materials Summary").Range("h2").Consolidate _
    Sources:=Array("Materials Summary!data"), _
    Function:=xlSum, LeftColumn:=True
    Sheets("Overall Summary").Select
End Sub

Sub delrowsifzero1()
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    Dim lastRow As Long
     Worksheets("Itemised Detail").Activate
     On Error Resume Next
       lastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Dim x As Long
    ActiveWorkbook.Worksheets("Itemised Detail").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Itemised Detail").Sort.SortFields.Add Key:=Range("A:a" & lastRow) _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Itemised Detail").Sort
        .SetRange Range("A:a" & lastRow)
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    For x = lastRow To 2 Step -1
        If Cells(x, 1) = "" Or Cells(x, 1) = 0 Then
            Rows(x).EntireRow.Delete
        End If
    Next x
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationAutomatic
    Application.CutCopyMode = False
    
            'Hide worksheets
    Worksheets("Overall Costs").Visible = xlSheetHidden
    Worksheets("Single Unit Pricing").Visible = xlSheetHidden
    Worksheets("Total Hours For All Units").Visible = xlSheetHidden
    Worksheets("Single Unit Hours").Visible = xlSheetHidden
      End Sub

Optimising my VBA to improve the efficiency of my Workbook

Tmini

New Member

Attachments

Peter_SSs

MrExcel MVP, Moderator

Excel Facts

Tmini

New Member

Peter_SSs

MrExcel MVP, Moderator

Tmini

New Member

Attachments

Peter_SSs

MrExcel MVP, Moderator

Tmini

New Member

Peter_SSs

MrExcel MVP, Moderator

Tmini

New Member

Peter_SSs

MrExcel MVP, Moderator

Tmini

New Member

Similar threads

Forum statistics

Share this page

Optimising my VBA to improve the efficiency of my Workbook

New Member

Attachments

MrExcel MVP, Moderator

Excel Facts

New Member

MrExcel MVP, Moderator

New Member

Attachments

MrExcel MVP, Moderator

New Member

MrExcel MVP, Moderator

New Member

MrExcel MVP, Moderator

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock