Need a macro to Fill a sheet using data in another sheet

[Dilusha]

I Have Data(in Table) in Sheet 1 as follows

A	B	C	D	E	F
1	Date	Invoice_No	Supplier	Vat	Total
2	15/05/2015	TX1356	P	55.15	1452.25
3	15/05/2015	FZ78	Q	0	7852.26
4	16/05/2015	TX1485	P	47.25	2578.25
5	29/05/2015	FZ89	Q	12.36	525.00

<tbody>
</tbody>

In the same work book , there are 4 sheets named as "P" , "Q" , "vat" , "Total". In each sheet following format has created (similar table)( see eg1 & eg2).
I want one or two macros to run after entering data in to sheet1 in order to fill automatically the relevant sheets . i.e. copy of data relevant to "P" -suppler should go to sheet P as eg1 . copy of data in VAT column should go to sheet "VAT" as per eg2.

note . When i run the macro next time only the un-updated data should be updated in relevant sheets. i.e. When I enter data in to sheet 1 in next week & run the macro ,the data relevant to "P" Supplier should be copied to row number 4 in sheet P (old data should be remained same)

PLS help me.

eg 1.sheet "P"

A	B	C	D	E	F
1	DATE	Invoice_No	Supplier	RS
2	15/05/2015	TX1356	P	1452.25
3	16/05/2015	TX1485	p	2578.25
4
5
6

<tbody>
</tbody>

eg 2. sheet "VAT"

A	B	C	D	E	F
1	DATE	Invoice_No	Supplier	RS
2	15/05/2015	TX1356	P	55.15
3	15/05/2015	FZ78	Q	0
4	16/05/2015	TX1485	P	47.25
5	29/05/2015	FZ89	Q	12.36
6

<tbody>
</tbody>

 

[hiker95]

Dilusha,

1. What version of Excel, and, Windows are you using?

2. Are you using a PC or a Mac?

3. In worksheet Sheet1, can there be duplicate Invoice_No's in column C?

 

[Dilusha]

I use MS excel 2007 in my PC. O.S is windows 7 (ultimate). NO duplicate Invoice_No's in column C.

 

[hiker95]

Dilusha,

Here is a macro solution for you to consider.

You can change the raw data worksheet name in the macro.

Sample raw data worksheet:

Excel 2007
	A	B	C	D	E	F
1	1	Date	Invoice_No	Supplier	Vat	Total
2	2	15/05/2015	TX1356	P	55.15	1452.25
3	3	15/05/2015	FZ78	Q	0	7852.26
4	4	16/05/2015	TX1485	P	47.25	2578.25
5	5	29/05/2015	FZ89	Q	12.36	525.00
6
Sheet1

The other three worksheets, P, Q, and, VAT, contain the same titles in row 1:

Excel 2007
	A	B	C	D	E
1	1	DATE	Invoice_No	Supplier	RS
2
3
4
5
6
VAT

After the macro (for brevity) in worksheet VAT:

Excel 2007
	A	B	C	D	E
1	1	DATE	Invoice_No	Supplier	RS
2	2	15/05/2015	TX1356	P	55.15
3	3	15/05/2015	FZ78	Q	0
4	4	16/05/2015	TX1485	P	47.25
5	5	29/05/2015	FZ89	Q	12.36
6
VAT

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code
2. Open your NEW workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Sub CopyToSheets_P_Q_VAT()
' hiker95, 07/26/2015, ME870978
Dim w1 As Worksheet, wp As Worksheet, wq As Worksheet, wv As Worksheet
Dim c As Range, ip As Range, iq As Range
Dim nr As Long, nrv As Long
Application.ScreenUpdating = False
Set w1 = Sheets("Sheet1")   '<-- you can change the sheet name here
Set wp = Sheets("P")
Set wq = Sheets("Q")
Set wv = Sheets("VAT")
With w1
  For Each c In .Range("D2", .Range("D" & Rows.Count).End(xlUp))
    If c = "P" Then
      Set ip = wp.Columns(3).Find(c.Offset(, -1).Value, LookIn:=xlValues, LookAt:=xlWhole)
      If ip Is Nothing Then
        nr = wp.Cells(wp.Rows.Count, "C").End(xlUp).Row + 1
        wp.Cells(nr, 1) = nr
        wp.Cells(nr, 2) = c.Offset(, -2)
        wp.Cells(nr, 3) = c.Offset(, -1).Value
        wp.Cells(nr, 4) = c.Value
        wp.Cells(nr, 5) = c.Offset(, 2).Value
        nrv = wv.Cells(wv.Rows.Count, "C").End(xlUp).Row + 1
        wv.Cells(nrv, 1) = nrv
        wv.Cells(nrv, 2) = c.Offset(, -2)
        wv.Cells(nrv, 3) = c.Offset(, -1).Value
        wv.Cells(nrv, 4) = c.Value
        wv.Cells(nrv, 5) = c.Offset(, 1).Value
      End If
    ElseIf c = "Q" Then
      Set iq = wq.Columns(3).Find(c.Offset(, -1).Value, LookIn:=xlValues, LookAt:=xlWhole)
      If iq Is Nothing Then
        nr = wq.Cells(wq.Rows.Count, "C").End(xlUp).Row + 1
        wq.Cells(nr, 1) = nr
        wq.Cells(nr, 2) = c.Offset(, -2)
        wq.Cells(nr, 3) = c.Offset(, -1).Value
        wq.Cells(nr, 4) = c.Value
        wq.Cells(nr, 5) = c.Offset(, 2).Value
        nrv = wv.Cells(wv.Rows.Count, "C").End(xlUp).Row + 1
        wv.Cells(nrv, 1) = nrv
        wv.Cells(nrv, 2) = c.Offset(, -2)
        wv.Cells(nrv, 3) = c.Offset(, -1).Value
        wv.Cells(nrv, 4) = c.Value
        wv.Cells(nrv, 5) = c.Offset(, 1).Value
      End If
    End If
  Next c
End With
Application.ScreenUpdating = True
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm, and, answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

Then run the CopyToSheets_P_Q_VAT macro.

 

[Dilusha]

It is working , Thank u

 

[hiker95]

Dilusha,

Thanks for the feedback.

You are very welcome. Glad I could help.

And, come back anytime.