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

 

[ianharper68]

This is what I get when I enter value of 6789 in cell G8 of the MASTER SHEET. As you can see, it is copying all the data. However, it is also copying all the formulas from the MASTSER SHEET. This may give you unwanted results. You could copy the values only. Also have a look at the possible effects of any linked files.

NO

SUPPLIER

PO REFERENCE

INVOICE NUMBER

CURRENCY

INVOICE VALUE

EXA Qty

HS Codes

EX1

COLLIE

L

W

H

CBM

LDM

NTW

GRW

Crate

Pallet

Carton

Jiffy

ADR

POD

COC

Our Collection Order Ref:

Label Number

Date Goods In

Date Goods out

Free Days

Chargeable Days

Domestic Collection Charges

FSC %

Domestic Handling In Charges

Domestic Storage Charges

JBS Fabrication Ltd

123

456

GBP £

6,789.00

1

5

1

120

100

85

1.02

0.49

125.00

125.00

1

No

PE0001/01

1

15-11-24

#REF!

3

#REF!

35.00

5.25

8.75

25.20

Ah ok ... so is there a way of only copying the data up to and including column AB (Date Goods IN)? That way the formulas in the last columns AC to AL won't be affected.

 

[mumps]

Try:

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, lRow As Long
    ws = "MASTER SHEET" & " " & Split(Target.Offset(, -1), " ")(0)
    With Sheets(ws)
        lRow = .Range("C7", .Range("C" & .Rows.Count).End(xlUp)).Find(What:="", lookat:=xlWhole).Row
        Range("C" & Target.Row).Resize(, 26).Copy .Range("C" & lRow)
    End With
    Application.ScreenUpdating = True
End Sub

 

[ianharper68]

Hi Mumps,

Thanks for all your help but this isn't working it only copies data up to the invoice amount column ... all data beyond that column is not copied over .... as you can see there is additional data after the 456,987.00

But when I go to the USD sheet it is not there ....

I think I will just go back to the copy paste option