Forum Discussion

rbbansal's avatar
rbbansal
Copper Contributor
Dec 05, 2021

Auto-populate sheet 2 when updates are made in sheet 1

I have followings Tabs in my workbook (Apple, Orange, Cherry, Fruits) 

Apple, Orange and Cherry has following columns (Date-D, CustomerName-N, Count-C, PricePerCount - P, TotalPrice- T) , total price is C*P 

We add rows in the end only these tabs

 

In Fruits tab , I have  columns (Fruit, Date-D, CustomerName-N, Count-C, PricePerCount-P, TotalPrice-T)

 

This is what we desire

a) if I add a row in any of Apple, Orange, Cherry tab, a row is added in the end in Fruits tab, 

 

e.g if I add 

12/01/2021, TestName, 5, 2, 10  in Apple tab

then

Apple, 12/01/2021, TestName, 5, 2, 10  should get added in Fruits tab

 

excel
Formulas and Functions
Macros and VBA

3 Replies

  • Subodh_Tiwari_sktneer's avatar
    Subodh_Tiwari_sktneer
    Silver Contributor
    Dec 05, 2021

    rbbansal 

     

    You may place the following code on ThisWorkbook Module for the Workbook_SheetChange event.

    Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Target.CountLarge > 1 Then Exit Sub
If Sh.Name <> "Apple" And Sh.Name <> "Orange" And Sh.Name <> "Cherry" Then Exit Sub

Dim Rng As Range
Dim dlr As Long
Dim r   As Long
Dim n   As Long
Dim dws As Worksheet

r = Target.Row
If r = 1 Then Exit Sub

Set dws = Worksheets("Fruits")

Set Rng = Union(Range("C" & r), Range("D" & r), Range("N" & r), Range("P" & r))

n = Application.CountA(Rng)
If n <> 4 Then Exit Sub

On Error GoTo Skip
Application.EnableEvents = False

dlr = dws.Cells(Rows.Count, "B").End(xlUp).Row + 1
dws.Range("B" & dlr).Value = Sh.Name
dws.Range("C" & dlr).Value = Range("C" & r).Value
dws.Range("D" & dlr).Value = Range("D" & r).Value
dws.Range("N" & dlr).Value = Range("N" & r).Value
dws.Range("P" & dlr).Value = Range("P" & r).Value
dws.Range("T" & dlr).Value = Range("T" & r).Value
Skip:
Application.EnableEvents = True
End Sub

     

    As per the above code, once you fill all the four entries i.e. Count in column C, Date in column D, Name in column N and Price per count in column P on Apple or Orange or Cherry Tabs, the complete record will be automatically be copied to the Fruits Tab also.

     

    Please find the attached with the code in place to test.

     

     

    • rbbansal's avatar
      rbbansal
      Copper Contributor
      Dec 05, 2021

      Subodh_Tiwari_sktneer 

       

      Thanks Subodh for your quick reply.
      This is what I was looking for.
      Sorry for confusion regarding the column headers. Name(N) was not column N but just to represent a variable for name .
      There is some slight change in the file (please see attached) . I have sales and purchases in same workbook.
      The tabs are S-Apples, S-Orange, S-Cherry and S-Fruits for sales
      There are tabs P-Apples, P-Orange, P-Cherry and P-Fruits for purchases.
      The requirement is that sales of individual fruits get consolidated in S-Fruits and similarly Purchases of Individual fruits gets consolidated in P-Fruits

      Can you help me with this 

       

Resources