Help with worksheets

[BAMU08]

Hi all,

This is probably something really simple but I dont have the excel knowledge to do it so I would be grateful of any help.

I have got a worksheet which is a masterdata sheet and has a list of many transactions with each transaction containing much information (value, currency, date, reference etc...) I would like to have a formula which take all the EURO items in this worksheet and place them on another worksheet in the spreadsheet. Then another one to take all the USD, GBP, AUD etc.. and place them in the relevant worksheets im setting up.

Is this possible and if so could someone help me with this.

Thanks in advance for the help and sorry if my rambling message is not clear

 

[DonkeyOte]

if file size is not an issue could you not use multiple pivot tables based on your master data and set filter on each to appropriate currency ?

how are the individual currency sheets to be utilised - ie what is their purpose ?

 

[BAMU08]

File Size is not an issue.

It is to seperate the currenices as we need to have details of the total values of each ccy then we will put a mid rate to convert each of the currencies to our base currency. Also need to have details of the number of transactions in each currency as well.

Im not the best with excel and I tried to have a look around the excel help section but have had no luck. The person who I would turn to for excel help is off for the next 2 weeks and im just trying to keep going while they are away.

Thanks for any help

 

[Dougie1]

You could just use Autofilter. Highlight the column = Currency, go to the Data menu, select Autofilter. This will put a filter on your Currency column. Now when you click the arrow - you can just select the currency you want to see on the sheet then copy and paste this data to a new sheet.

Hope this helps.

D

 

[BAMU08]

Hi Dougie

That would work im looking to make this a rolling template. Each month we can copy details of these transactions to excel and from that I would like it to sperate to the different currencies.

Dont suppose you have any ideas on how to do this.

Cheers for the help so far guys

 

[Dougie1]

Well............. Most of my Excel solutions are very much contrived (not like the real experts on here) but - below is some really useful code that I was previously provided by halface.

Fistly though MAKE SURE THAT YOU CREATE A NEW COPY OF YOUR WORKBOOK TO TRY THIS ON.

If you copy and paste this into a new module (Alt & F11 will take you to the VB editor - from here go to the Insert menu and select Module) within the workbook that you are working with. Now if you make sure that your currency column is in column A (if it is not - just copy and paste it into column A, and then sort by column A.

Now in your workbook go to the Tools menu - Select Macro - Macros then run the macro.

This will create a new worksheet for each individual value that is in column A. You can then select all the worksheets at once and delete the column A (where you copied and pasted the currency column to). Give it a try and see what you think.


Private Function SheetExists(sname) As Boolean
'Returns 'True' if the sheet exists in the workbook
Dim x As Object
On Error Resume Next
Set x = ActiveWorkbook.Sheets(sname)
If Err = 0 Then SheetExists = True _
Else SheetExists = False
End Function

Sub UniqValSplitOut()
Dim LstRw As Long, _
UniqValRng As Range, _
UniqValNum As Range, _
ThsSht As String, _
Sht As Worksheet, _
ShtExists As Boolean

Application.ScreenUpdating = False
ActiveSheet.AutoFilterMode = False

'Determine the active sheet's name
ThsSht = ActiveSheet.Name

'Determine the last row of data
With Sheets(ThsSht)
LstRw = .Cells(Rows.Count, "A").End(xlUp).Row

'Set the range of unique values
Set UniqValRng = Range(.Cells(1, "A"), .Cells(LstRw, "A"))

'Loop through each unique value in the range
For Each UniqValNum In UniqValRng

'Skip any blank cells
If IsEmpty(UniqValNum) Then GoTo SkipIt

'Skip duplicate unique value numbers
If UniqValNum.Row <> 1 Then
If UniqValNum.Value = UniqValNum.Offset(-1).Value Then GoTo SkipIt
End If

'If the sheet doesn't exist then create one & name it
If Not SheetExists(UniqValNum.Value) Then
Sheets.Add After:=Sheets(Sheets.Count)
ActiveSheet.Name = UniqValNum.Value
End If

'Filter & copy the data to it's own sheet (Paste it 1 blank row beneath any existing data)
UniqValRng.AutoFilter Field:=1, Criteria1:=UniqValNum
UniqValRng.SpecialCells(xlCellTypeVisible).EntireRow.Copy _
Sheets(UniqValNum.Value).Cells(Rows.Count, "A").End(xlUp)(1)
Columns("A:A").Select
Selection.Delete Shift:=xlToLeft
Columns("A:IV").Select
Selection.Columns.AutoFit
Range("A1").Select
'Turn off the autofilter
.AutoFilterMode = False

SkipIt:

'Continue the loop
Next UniqValNum
End With

'Go back to the original active sheet
Sheets(ThsSht).Select
Application.ScreenUpdating = True

End Sub