Get data from more sheets in final sheet based on ingridient name

[Auri]

Sub test()

Dim IngredientNameInSheet1 As String
Dim IngredientNameInSheet2 As String
      
Dim ws1 As Worksheet
Dim ws2 As Worksheet

Set ws1 = Worksheets("Sheet1")
Set ws2 = Worksheets("Sheet2")

'turn off screen updating
Application.ScreenUpdating = False



For Each i In ws1.Range("A1:C30").Rows

'

        For Each j In ws2.Range("A1:I172").Rows
      
            IngredientNameInSheet1 = ws1.Cells(i.Row, 1)
            IngredientNameInSheet2 = ws2.Cells(j.Row, 2)
          
            If (StrComp(IngredientNameInSheet1, IngredientNameInSheet2, vbTextCompare) = 0) Then
          
              
                ws2.Cells(j.Row, 13) = ws1.Cells(i.Row, 3)
            End If
      
        Next j

Next i


Application.ScreenUpdating = True

End Sub

 

[chirayu]

Can you please provide a sample file & please state what your macro is doing.

I assume you are trying to match names between sheet1 & sheet2 & then if name match - sheet2 cell value = sheet 1 cell value

Yes code can be modified to work in each sheet - but you will need to have 1 sheet which will be the main sheet that matches against all the rest

The way to do so would be to add the "For each ws" code around existing code that would be something like below. Obviously I'm not sure if this will work as I don't have sample data.

Code assumes Sheet1 is the main sheet against which all others are compared

Sub IngredientList()

Dim IngredientNameInMain, IngredientNameInSheet As String
Dim ws1, ws2 As Worksheet
ws1 = Worksheets("Sheet1")

For Each ws In ActiveWorkbook.Worksheets

    If ws.Name <> ws1 Then
       
        ws2 = ws.Name
        ws2.Select
       
        For Each i In ws1.Range("A1:C" & Cells(Rows.Count, "A").End(xlUp).Row).Rows 'Dynamic Range for Main sheet for match
        For Each j In ws2.Range("A1:I" & Cells(Rows.Count, "A").End(xlUp).Row).Rows 'Dynamic Range for other sheets for match
       
        IngredientNameInMain = ws1.Cells(i.Row, 1)
        IngredientNameInSheet = ws2.Cells(j.Row, 2)
       
        If (StrComp(IngredientNameInMain, IngredientNameInSheet, vbTextCompare) = 0) Then
       
        ws2.Cells(j.Row, 13) = ws1.Cells(i.Row, 3)
        End If
       
        Next j
        Next i
   
    Else
    End If

Next ws

End Sub

 

[Auri]

Hi, thank zou very much for quick reply my macro is matching the names in sheet 1 and sheet 2 and add the value of the matched name form sheet 1 to sheet 2. What I would like to do now is have 5 sheets, where in last sheet it will put values from all sheets to different columns. Could I also multiple the values from sheets 1,2,3,4 and for with some specific cell in sheet 5? I attached my sample but it's not working...

 

[chirayu]

Column C in each sheet has header of Percentage but values are numerical not percentage e.g. Sheet 1 Milk is 9.76 (number) - not 9.76%? Also this doesn't need macro. I have made formula that can do same. Attached.