CODEWITHSUNDEEP
vbaexcel
utkarsh agarwal
utkarsh agarwal

Reputation: 15

Automatically update sheet 2 & sheet 3 from sheet 1 as per data

I want to automatically update sheet 2 & sheet 3 from sheet 1 as per data. It will be helpful if it gets resolved.

I am new to visual basic coding so unaware of its script but aware of c++. Whatever the solution may be it will be helpful.

Screen shot of the three sheets - I was unable to upload the Excel files

screenshot

I have tried the following code:

Sub FindMatches()
    Dim oldrow As Integer
    Dim newrow As Integer
     For oldrow = 4 To 14
        For newrow = 3 To 20
            If Cells(oldrow, 12) = Cells(1, newrow) And Cells(oldrow, 13) = Cells(newrow, 1) Then  'date and brand
                    If Cells(1, 14) = Cells(newrow, 2) Then
                          Cells(newrow, 3).Value = Cells(oldrow, 14).Value ' m1
                    End If
                    If Cells(1, 15) = Cells(newrow + 1, 2) Then
                          Cells(newrow + 1, 3).Value = Cells(oldrow, 15).Value ' m2
                    End If
                    If Cells(1, 16) = Cells(newrow + 2, 2) Then
                                    Cells(newrow + 2, 3).Value = Cells(oldrow, 16).Value ' m3
                    End If
                    If Cells(1, 17) = Cells(newrow + 3, 2) Then
                                        Cells(newrow + 3, 3).Value = Cells(oldrow, 17).Value ' issue
                    End If
                    If Cells(1, 18) = Cells(newrow + 4, 2) Then
                                            Cells(newrow + 4, 3).Value = Cells(oldrow, 18).Value ' repack
                    End If
                    If Cells(1, 19) = Cells(newrow + 5, 2) Then
                                                Cells(newrow + 5, 3).Value = Cells(oldrow, 19).Value ' extra
                    End If
                    If Cells(1, 20) = Cells(newrow + 6, 2) Then
                    Cells(newrow + 6, 3).Value = Cells(oldrow, 20).Value ' wastage
                    End If
         End If

        Next newrow
        Next oldrow
End Sub

Upvotes: 0

Views: 135

Answers (2)

Stefan Winkler
Stefan Winkler

Reputation: 3966

Maybe you even don't need VBA to update the data. You can simply enter a formula =C2 into a cell to reference (and retrieve the data from) a cell.

You also can reference cells from other sheets like this =Sheet1!C2.

And you can use Functions such as IF to do more complex cases and logic.

Upvotes: 1

Luboš Suk
Luboš Suk

Reputation: 1546

You just cant use Cells without sheet referencing. Because excel dont know which sheet you wana to use and assuming active sheet. So you need to you ActiveSheet.cells() and switching active sheet (but its not highly recommended). Instead use sheet declaration like this

Dim myLovelySheet as worksheet
Set mylovelySheet = Sheets("yourCuteSheetname")

and then you can work with sheet like with object (you will be familiar to it from C++) 

myLovelySheet.cells()

or you can perform multiple operations on sheet with construction like this

with myLovelySheet
.cells()
.cells()
'etc

end with

Basicaly your approach is almost correct, but try study more code. I can recommend this which have multiple examples with good practise

Upvotes: 0

Related Questions