Enter data from Different sheets to single sheet in same workbook once we enter data in single sheet

[m_vishal_c]

hi,
i have 1 workbook having 6 sheets
Mater invoice, Sheet2, Sheet3, Sheet4, Sheet5, Sheet6.
Sheet 2 to 6 have invoice no and invoice amount column. when i enter invoice no and amount in any Sheet then that Sheet name, invoice no and amount should be copied to Mater invoice file
and Mater Invoice sheet has Invoice and amount column too
Please let me know how can i fix by VBA
Thanks in Advance

 

[MickG]

The code is a WorkBook Event code, that means when you alter a cell in columns "A & B" of any sheet except "Master invoice" then the data you enter will automatically be sent to sheet "Master invoice" Columns "A,B & C".

 

[m_vishal_c]

The code is a WorkBook Event code, that means when you alter a cell in columns "A & B" of any sheet except "Master invoice" then the data you enter will automatically be sent to sheet "Master invoice" Columns "A,B & C".

HI Sorry to say that . this code does not work. Please guide me. thanks

 

[MickG]

Try this Example file:-
https://app.box.com/s/u4a4i7lwsaaqv6zm6xxxbvqjqpacuk7p

 

[m_vishal_c]

hi MickG. i download this file but when i enter in Sheet2, column A and B, those information does not comes up automatically in Mater Invoice sheet. do i need to press something

 

[MickG]

Did anything happen when you entered data???
NB:- You need to add data in both column "A & B"
Have you got Macro's Enabled, if not see this:-

https://www.addictivetips.com/windows-tips/enable-all-macros-in-excel-2010/

 

[MickG]

Also you need Click "Enter" or select another cell after entering data"

 

[m_vishal_c]

HI MickG. thanks a lot. finally it works. i need data to be copied in "Master Invoice" sheet- B6(Sheet Name), E6(Invoice no), F6(Invoice amount) from other sheets(2,3,4). and invoice no and amount is start from G12, H12 in sheets(2,3 4). so what can be changed in this code. please guide me. if you want i can share file. please let me know. heaps heaps thanks

 

[m_vishal_c]

hi MickG. please see below link and download that file. then you will come to know what i tried to explain you

https://www.dropbox.com/s/r0nnt4thtlyoin6/Copy of SubCon Invoice and PO Tracker.xlsm?dl=0

thanks

 

[MickG]

This is new Example file with new Code:-
https://app.box.com/s/gkj1i5ly0wkjojh69kg0s5ksbr9g8l8f

This is the new Code:-

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim Lst As Long, nLst As Long
Application.EnableEvents = False
If Not ActiveSheet.Name = "Master Invoice" Then
    
    If Not Intersect(Target, ActiveSheet.Range("G:H")) Is Nothing And Target.Row > 11 Then
With Sheets("Master Invoice")
    Lst = .Range("B" & Rows.Count).End(xlUp).Row
    nLst = IIf(Lst < 6, 5, Lst)
    
    If Application.CountA(Cells(Target.Row, "G").Resize(, 2)) = 2 Then
        .Range("B" & nLst + 1) = ActiveSheet.Name
        .Range("E" & nLst + 1).Value = Cells(Target.Row, "G").Value
        .Range("F" & nLst + 1).Value = Cells(Target.Row, "H").Value
    End If
End With
    End If
End If
Application.EnableEvents = True
End Sub

 

[MickG]

Here is your file returned with new code:-
https://app.box.com/s/o60ya7f8qcrdqj7r8dfank5755rrrnus

Regds Mick