Help Required Please

[matthardy19]

Hi,

Just looking if someone could offer some help or advice.

I am wanting to copy some data from one worksheet to another if a condition is met. I have tried doing this with IF Formula's however 1. there is to many nests to complete this and 2 it will work for the first row on a smaller nest but for the remainder it doesn't work as I want it too.

What I am wanting it to do is, when Building qty (F3) on sheet ISP is Greater than 0 then on Customer Materials Sheet in (C22) is filled in with the Item code from Sheet ISP (A3). This then needs to fill down for the rest of the sheet but for the cell down not to duplicate and include one which is a duplicate to keep it all unique. On the ISP Sheet there is 103 rows of data for context.

This will then be replicated in the other worksheets.

is this possible?

Thanks

 

[matthardy19]

Hi,

Got it. You can use this the code below with 3 conditions:

1. Just make sure your Customer Material Sheet has this part already Set up:

   Book1
   	A	B	C	D	E
   18
   19
   20	Materials List
   21	Item ID	Description	Price	Qty	Total
   Customer Material

2. Make sure the Price of materials on the material sheets is always located in Column K and the Qty is always in column as mentioned in post 9
3. Important! make sure the individual materials sheet have a header row hence the first item starts at row 2 (like in your screenshot)

Then run this code. The code will select all lines from the 5 material sheets if the value in the Building Qty column > 0

Sub Fill_Customer_Material()
  Dim a, Sh As Variant
  Dim i, tmp, LastRow As Long
  Dim Total As Double
  Dim CM As Worksheet

  Set CM = Sheets("Customer Material")
 
  For Each Sh In Array("ISP", "OSP", "Electrical", "Patch Leads", "Specials")
   
    If Sheets(Sh).Name = "OSP" Then
    tmp = 7
    Else
    tmp = 6
    End If
   
    LastRow = CM.Cells(Rows.Count, 1).End(xlUp).Row
    With Sheets(Sh).Range("A2:O123")
       
        a = .Value
        For i = 1 To UBound(a)
            Total = Total + a(i, tmp)
        Next i
       
        If Total > 0 Then
           
            'Insert the Header
            CM.Cells(LastRow, 1).Offset(1, 0).Value = Sheets(Sh).Name
            With CM.Range(Cells(LastRow, 1).Offset(1, 0), Cells(LastRow, 1).Offset(1, 4)).Interior
                .Pattern = xlSolid
                .PatternColorIndex = xlAutomatic
                .Color = 15773696
                .TintAndShade = 0
                .PatternTintAndShade = 0
            End With
   
            'Fill the lines on Material Master with ISP lines where Qty > 0
            For i = 1 To UBound(a)
                If Len(a(i, tmp)) > 0 And IsNumeric(a(i, tmp)) Then
                    If a(i, tmp) > 0 Then
                        LastRow = CM.Cells(Rows.Count, 1).End(xlUp).Row
                        CM.Cells(LastRow, 1).Offset(1, 0).Value = a(i, 1) 'Item ID
                        CM.Cells(LastRow, 1).Offset(1, 1).Value = a(i, 4) 'Description
                        CM.Cells(LastRow, 1).Offset(1, 2).Value = a(i, 11) 'price
                        CM.Cells(LastRow, 1).Offset(1, 3).Value = a(i, tmp) 'Qty
                        CM.Cells(LastRow, 1).Offset(1, 4).Value = a(i, tmp) * a(i, 11) 'total
                    End If
                End If
            Next i
        End If
       
    End With
    LastRow = CM.Cells(Rows.Count, 1).End(xlUp).Row
Next Sh
End Sub

Hope this helps

The link to the sheet: Brexons Costing Sheet Share.xlsm

Thanks

 

[matthardy19]

The link to the sheet: Brexons Costing Sheet Share.xlsm

Thanks

I have also highlighted where the data needs to be inserted on the Customer Materials Sheet in yellow for each material list

 

[jorismoerings]

I have also highlighted where the data needs to be inserted on the Customer Materials Sheet in yellow for each material list

Ok. I'll take a look. Remove the shared file now! because it contains company info.

 

[matthardy19]

Ok. I'll take a look. Remove the shared file now! because it contains company info.

Perfect Thank you

 

[jorismoerings]

Hi,

Needed to make some changes but this is your file.
It responds to the building number on the Customer Material Sheet and secured to 4 because your pricing sheets do not contain more building columns.
Based on the building number it looks in the respective sheets if the building column has any numbers in them, if that's true, it will take the lines with a number and copy those to the Material sheet.
When done with all pricing sheets, it will add the summary calculation on the bottom.

Does this come close?

 

[matthardy19]

Hi,

Needed to make some changes but this is your file.
It responds to the building number on the Customer Material Sheet and secured to 4 because your pricing sheets do not contain more building columns.
Based on the building number it looks in the respective sheets if the building column has any numbers in them, if that's true, it will take the lines with a number and copy those to the Material sheet.
When done with all pricing sheets, it will add the summary calculation on the bottom.

Does this come close?

Hi,

That is Perfect. The only thing now is, that when you add a qty to building 1/3/4 it doesn't product a table. How do we add one for these?

 

[jorismoerings]

Hi,

That is Perfect. The only thing now is, that when you add a qty to building 1/3/4 it doesn't product a table. How do we add one for these?

Just run clear data and get data again

 

[matthardy19]

Just run clear data and get data again

Ah sorry that was my bad, I didnt change the building number. Is there any way to have it like the sheet i sent you before, when Building 2 listing goes under building 1 so on and so on?

 

[jorismoerings]

Hi,

Of course but will take some time, will take a look at it tomorrow.

 

[jorismoerings]

Hi,

Of course but will take some time, will take a look at it tomorrow.

Something like this