Help with worksheets

BAMU08

New Member
Joined
Jun 13, 2008
Messages
3
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
 

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

DonkeyOte

MrExcel MVP
Joined
Sep 6, 2002
Messages
9,123
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

New Member
Joined
Jun 13, 2008
Messages
3
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

Board Regular
Joined
Jul 27, 2007
Messages
212
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

New Member
Joined
Jun 13, 2008
Messages
3
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

Board Regular
Joined
Jul 27, 2007
Messages
212
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
 

Watch MrExcel Video

Forum statistics

Threads
1,130,110
Messages
5,640,147
Members
417,127
Latest member
shakilk

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top