Copy data to another sheet depending on input of currency

[ianharper68]

So I have a main worksheet that stores all my invoices in 3 currencies (£, $ & €) However I need to also add the same data to 3 separate sheets based on each currency.

So can anyone let me know what the formula would be? I have a cell in column F that has a drop down list to choose the currency.

I know it should be simple but I just can't get it to work

 

[alansidman]

It would be real helpful if you posted a sample of your data using XL2BB in order to maintain formatting. Your sample should be representative of your actual data and should contain 8-12 records.

 

[ianharper68]

Here is just a small part, this sheet is called "Master" so where the Currency is I would want the line of data per currency copying automatically to the appropriate currency sheet i.e GBP Invoices, USD Invoices, EUR Invoices

PE0001 - EDCR Master Worksheet copy.xlsx
	A	B	C	D	E	F	G
1
2			CONSOL:	PE0001
3
5
6
7		NO	SUPPLIER	PO REFERENCE	INVOICE NUMBER	CURRENCY	INVOICE VALUE
8		1	AMOT	N23-0876	A2957	GBP £	3,467.80
9		2	DCL Warehousing	N23-0456	32567	USD $	24,789.50
10		3	MacLean Electrical	N24-0367	Inv09856	GBP £	12,967.34
11		4	LHR Marine Ltd	EDCR-M24-5058	I-87234	EUR €	18,957.00
12		5	NOV Rig Technologies	EDCR-M24-5006_2	25-0986	USD $	1,356.79
13		6	NOV Rig Technologies	M24-0508	25-0879	USD $	123,489.00
14		7	Craig International Ltd	M24-500	Inv250986	GBP £	4,789.00
15		8	MB Air Systems Ltd	N24-0448	21367	GBP £	2,378.90
16		9
17		10
18		11
19		12
MASTER SHEET

Cell Formulas
Range	Formula
D2	D2	=Dashboard!U3
B8:B19	B8	=ROW()-7

Cells with Data Validation
Cell	Allow	Criteria
C8:C19	List	=Lists!$F$21:$F$67
F8:F15	List	=Lists!$J$4:$J$7

 

[mumps]

Copy and paste this macro into the worksheet code module. Do the following: right click the tab name for your MASTER SHEET sheet and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. This macro is triggered by a change in column G. When you enter a value in column G and press the ENTER key, the data in that row will be automatically copied to the appropriate sheet. This means that you would enter the data in columns B to F first and the value in column G last.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.CountLarge > 1 Then Exit Sub
    If Intersect(Target, Range("G8", Range("G" & Rows.Count).End(xlUp))) Is Nothing Then Exit Sub
    Application.ScreenUpdating = False
    Dim ws As String
    ws = Split(Target.Offset(, -1), " ")(0)
    Range("B" & Target.Row).Resize(, 6).Copy Sheets(ws).Cells(Sheets(ws).Rows.Count, "A").End(xlUp).Offset(1)
    Application.ScreenUpdating = True
End Sub

 

[ianharper68]

Copy and paste this macro into the worksheet code module. Do the following: right click the tab name for your MASTER SHEET sheet and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. This macro is triggered by a change in column G. When you enter a value in column G and press the ENTER key, the data in that row will be automatically copied to the appropriate sheet. This means that you would enter the data in columns B to F first and the value in column G last.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.CountLarge > 1 Then Exit Sub
    If Intersect(Target, Range("G8", Range("G" & Rows.Count).End(xlUp))) Is Nothing Then Exit Sub
    Application.ScreenUpdating = False
    Dim ws As String
    ws = Split(Target.Offset(, -1), " ")(0)
    Range("B" & Target.Row).Resize(, 6).Copy Sheets(ws).Cells(Sheets(ws).Rows.Count, "A").End(xlUp).Offset(1)
    Application.ScreenUpdating = True
End Sub

I tried that and I am getting a runtime error on this line .... Range("B" & Target.Row).Resize(, 6).Copy Sheets(ws).Cells(Sheets(ws).Rows.Count, "A").End(xlUp).Offset(1)

 

[mumps]

Replace this code

ws=Split(Target.Offset(,-1), “ “)(0)

with this code

ws=Split(Target.Offset(,-1), “ “)(0) & “ “ & ”Invoices”

 

[ianharper68]

Replace this code

ws=Split(Target.Offset(,-1), “ “)(0)

with this code

ws=Split(Target.Offset(,-1), “ “)(0) & “ “ & ”Invoices”[/code

[/QUOTE]

Now I am getting this every time I try and add data into any cells on the master sheet

 

[mumps]

I tested the macro on the data you posted and it worked properly. Can you upload a copy of your file to a free file sharing website and post a link to the file?

 

[ianharper68]

I tested the macro on the data you posted and it worked properly. Can you upload a copy of your file to a free file sharing website and post a link to the file?

Dropbox

www.dropbox.com

 

[alansidman]

Your file in dropbox has no data to test. It is pretty much useless in its current form.