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]

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

Sorry yes I uploaded the blank file .. My Bad ... I have now added some data

 

[mumps]

First of all, change the sheet name "MASTER SHEET EURO" to "MASTER SHEET EUR" and then try this macro in the worksheet code module:

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(, 6).Copy .Range("C" & lRow)
    End With
    Application.ScreenUpdating = True
End Sub

 

[ianharper68]

First of all, change the sheet name "MASTER SHEET EURO" to "MASTER SHEET EUR" and then try this macro in the worksheet code module:

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(, 6).Copy .Range("C" & lRow)
    End With
    Application.ScreenUpdating = True
End Sub

Hi Mumps,

Thats works for the data from cells in columns C to G, however I need to the whole line to be copied over from B through AI

Also for some reason the first line of data on row 8 is copying over into both GBP and USD?

 

[mumps]

This macro will copy to column AI.

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(, 33).Copy .Range("C" & lRow)
    End With
    Application.ScreenUpdating = True
End Sub

Also for some reason the first line of data on row 8 is copying over into both GBP and USD?

I could not reproduce this problem. The macro worked properly for me on the file you posted? Are you using the macro on a different file?

 

[ianharper68]

This macro will copy to column AI.

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(, 33).Copy .Range("C" & lRow)
    End With
    Application.ScreenUpdating = True
End Sub

I could not reproduce this problem. The macro worked properly for me on the file you posted? Are you using the macro on a different file?

Yeah funny because I can't get it to repeat either now?

I am only using the same test sheet I shared so I really do no know what I am doing wrong as the only data that copies over is the first 5 cells in columns C to G and not B to AL?

 

[mumps]

I can't say why you are getting those results. For me, the macro is copying all the cells from C:AI as you requested.

 

[ianharper68]

I can't say why you are getting those results. For me, the macro is copying all the cells from C:AI as you requested.

Can you share the version you copied with the macro and drop it in to the shared folder

 

[mumps]

Click here to download your file.

 

[ianharper68]

Click here to download your file.

Hi Mumps,

Sorry I am seeing the 3 lines ofdata on the Master Sheet but I can't see the respective data copied over on the currency sheets

 

[mumps]

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