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

 

[mumps]

In which columns are the invoice no and invoice amount in all the sheets? In which column in the "Mater" sheet do you want to return the sheet name?

 

[MickG]

Try this:-
NB:- This code must go in the "ThisWorkBook" module.
NB:- Data in Sheets 2 to 5 assume to be entered in columns "A & B"

Private [COLOR=navy]Sub[/COLOR] Workbook_SheetChange(ByVal Sh [COLOR=navy]As[/COLOR] Object, ByVal Target [COLOR=navy]As[/COLOR] Range)
[COLOR=navy]Dim[/COLOR] Lst [COLOR=navy]As[/COLOR] [COLOR=navy]Long[/COLOR]
Application.EnableEvents = False
[COLOR=navy]If[/COLOR] Not ActiveSheet.Name = "Master Invoice" [COLOR=navy]Then[/COLOR]
    [COLOR=navy]If[/COLOR] Not Intersect(Target, ActiveSheet.Range("A:B")) [COLOR=navy]Is[/COLOR] Nothing [COLOR=navy]Then[/COLOR]
[COLOR=navy]With[/COLOR] Sheets("Master Invoice")
    Lst = .Range("A" & Rows.Count).End(xlUp).Row
    [COLOR=navy]If[/COLOR] Application.CountA(Cells(Target.Row, "A").Resize(, 2)) = 2 [COLOR=navy]Then[/COLOR]
        .Range("A" & Lst + 1) = ActiveSheet.Name
        .Range("B" & Lst + 1).Value = Cells(Target.Row, "A").Value
        .Range("c" & Lst + 1).Value = Cells(Target.Row, "B").Value
    [COLOR=navy]End[/COLOR] If
[COLOR=navy]End[/COLOR] With
    [COLOR=navy]End[/COLOR] If
[COLOR=navy]End[/COLOR] If
Application.EnableEvents = True
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]

Regards Mick

 

[m_vishal_c]

hi. Thanks for replying. its column J and H

 

[m_vishal_c]

hi MickG. Thank for replying. i will check this above code and update you. thanks

 

[m_vishal_c]

hi MickG. thanks for replying. i tried but its a bit confusion for me. is it possible you can explain me then it will be easy for me. Sorry for inconvenience
thanks

 

[MickG]

Try this to load the code:-
In any sheet in the workbook, Click "Alt+ F11" : Vbwindow shows
Click "Ctrl + R" to show "Project Window ,on left, If not showing.
Scan down project window until you find:- "ThisWorkbook"
Double click "ThisWorkbook" line to show new window on right of VbWindow.
Paste code in this window.
Close VbWindow.

To use Code:-
Enter data in any sheet(2 to 5) in columns "A & B"
This data should now be visible in columns "A & B" of sheet, "Master Invoce"

Good Luck

 

[m_vishal_c]

Try this to load the code:-
In any sheet in the workbook, Click "Alt+ F11" : Vbwindow shows
Click "Ctrl + R" to show "Project Window ,on left, If not showing.
Scan down project window until you find:- "ThisWorkbook"
Double click "ThisWorkbook" line to show new window on right of VbWindow.
Paste code in this window.
Close VbWindow.

To use Code:-
Enter data in any sheet(2 to 5) in columns "A & B"
This data should now be visible in columns "A & B" of sheet, "Master Invoce"

Good Luck

thanks. i did. i just want to know how this code works. and how to execute this code. I am just lost. sorry for asking . thanks

 

[MickG]

Is the code now working for you ????

 

[m_vishal_c]

Is the code now working for you ????

hi , that's why i asked you, how this code will be executed ? how to run this code