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

 

[jorismoerings]

Hi,

Your first screenshot (probably Sheet ISP) containes over 30 lines while your second screenshot (probably Sheet Customer Materials Sheet) containes only 5 entrie rows. IS there a possibility that all Building qty (F3:F30) on sheet ISP is greater than 0 and subsequently the ISP section should be automatically enlarged?

 

[matthardy19]

Hi,

Your first screenshot (probably Sheet ISP) containes over 30 lines while your second screenshot (probably Sheet Customer Materials Sheet) containes only 5 entrie rows. IS there a possibility that all Building qty (F3:F30) on sheet ISP is greater than 0 and subsequently the ISP section should be automatically enlarged?

Hi Yes if this is possible.
On ISP The Colum goes from F3:F103. So if we had more than I have set up at the current time being, for it to auto generate more cells would be great.

 

[jorismoerings]

Hi Yes if this is possible.
On ISP The Colum goes from F3:F103. So if we had more than I have set up at the current time being, for it to auto generate more cells would be great.

So a VBA solution would be good for you.

Try something like this:

Sub Fill_Customer_Material()
  Dim a As Variant
  Dim i, tmp, LastRow As Long
  Dim Total As Double
  Dim CM, ISP As Worksheet
  
  Set CM = Sheets("Customer Material")
  Set ISP = Sheets("ISP")
  
  
  LastRow = CM.Cells(Rows.Count, 1).End(xlUp).Row
  
    With ISP.Range("A3:G103")
        a = .Value
        For i = 1 To UBound(a)
            Total = Total + a(i, 6)
        Next i
        
        If Total > 0 Then
            
            'Insert the Header
            CM.Cells(LastRow, 1).Offset(1, 0).Value = "ISP"
            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, 6)) > 0 And IsNumeric(a(i, 6)) Then
                    If a(i, 6) > 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, 7) 'price
                        CM.Cells(LastRow, 1).Offset(1, 3).Value = a(i, 6) 'Qty
                        CM.Cells(LastRow, 1).Offset(1, 4).Value = a(i, 6) * a(i, 7) 'total
                    End If
                End If
            Next i
        End If
        
    End With
  
End Sub

 

[matthardy19]

So a VBA solution would be good for you.

Try something like this:

Sub Fill_Customer_Material()
  Dim a As Variant
  Dim i, tmp, LastRow As Long
  Dim Total As Double
  Dim CM, ISP As Worksheet
 
  Set CM = Sheets("Customer Material")
  Set ISP = Sheets("ISP")
 
 
  LastRow = CM.Cells(Rows.Count, 1).End(xlUp).Row
 
    With ISP.Range("A3:G103")
        a = .Value
        For i = 1 To UBound(a)
            Total = Total + a(i, 6)
        Next i
       
        If Total > 0 Then
           
            'Insert the Header
            CM.Cells(LastRow, 1).Offset(1, 0).Value = "ISP"
            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, 6)) > 0 And IsNumeric(a(i, 6)) Then
                    If a(i, 6) > 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, 7) 'price
                        CM.Cells(LastRow, 1).Offset(1, 3).Value = a(i, 6) 'Qty
                        CM.Cells(LastRow, 1).Offset(1, 4).Value = a(i, 6) * a(i, 7) 'total
                    End If
                End If
            Next i
        End If
       
    End With
 
End Sub

Thank you, is there any formula that I would need to input in to C22 on the Customer Materials Page to make this work?

 

[jorismoerings]

Thank you, is there any formula that I would need to input in to C22 on the Customer Materials Page to make this work?

Sorry, but from the screenshots I can't see what C22 is or does.

The C column holds the price of the material and i don't know if you manually enter it or if it comes from the ISP sheet.
In my code I assumed the material price is located in column G on the ISP sheet. If that column is empty the formula in the code multiplies everything with zero.
So if you enter a price of the materials in column G on ISP sheet, it will work.

 

[matthardy19]

Sorry, but from the screenshots I can't see what C22 is or does.

The C column holds the price of the material and i don't know if you manually enter it or if it comes from the ISP sheet.
In my code I assumed the material price is located in column G on the ISP sheet. If that column is empty the formula in the code multiplies everything with zero.
So if you enter a price of the materials in column G on ISP sheet, it will work.

C22 on the Customer Materials Page is the First row where the data should start for ISP.
On the screenshot I included of ISP I couldn't fit all of the data in sorry. The materials Price is in Colum K and the Colum's go to O
I have it set up to find the data from the sheets using a VLOOKUP when the Item ID is in placed in C22 downwards on the customer materials sheet. That is the part which I am seeking to become automated to be automatically filled in when the Qty on the ISP Sheet is greater than 0.

I hope this make sense?

 

[jorismoerings]

C22 on the Customer Materials Page is the First row where the data should start for ISP.
On the screenshot I included of ISP I couldn't fit all of the data in sorry. The materials Price is in Colum K and the Colum's go to O
I have it set up to find the data from the sheets using a VLOOKUP when the Item ID is in placed in C22 downwards on the customer materials sheet. That is the part which I am seeking to become automated to be automatically filled in when the Qty on the ISP Sheet is greater than 0.

I hope this make sense?

No, not completely because in an earlier post you told me

So if we had more than I have set up at the current time being, for it to auto generate more cells would be great.

This means there's a possibility that not every section holds 5 lines but the OSP can be blank and the ISP section could go up to 100 lines (if every cell on the ISP sheet is filled with a number)
Hence the ISP section can't always start at row 22 because that depends on the other section as well..

Just from looking at the screenshots I assume there are other sheets as well, 1 sheet holding all the OSP materials, 1 for the Patch leads materials, etc.
If that's the case we need to automate inserting the lines from the other sheets for the other sections of the customer material sheet as well.
Does that make sense?

 

[matthardy19]

No, not completely because in an earlier post you told me

This means there's a possibility that not every section holds 5 lines but the OSP can be blank and the ISP section could go up to 100 lines (if every cell on the ISP sheet is filled with a number)
Hence the ISP section can't always start at row 22 because that depends on the other section as well..

Just from looking at the screenshots I assume there are other sheets as well, 1 sheet holding all the OSP materials, 1 for the Patch leads materials, etc.
If that's the case we need to automate inserting the lines from the other sheets for the other sections of the customer material sheet as well.
Does that make sense?

Yes that makes sense. There is always going to be the possibility that one of the materials sheet and section will be blank as you said and one could have many more than the other.

The reason why I have said it starts in C22 is because above this is a fill out form to fill out the job details so the table doesn't start till Row 19 and the first Data input at C22

There sheets with the Materials are
ISP (Building Qty F3:F103)
OSP (Building Qty G1:G123)
Electrical (Building Qty F3:F47)
Patch Leads (Building Qty F3:F77)
Specials (Building Qty F3:F19)

 

[jorismoerings]

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