Extract Data

[ashlad]

Guys,

My real world situation is to identify changes to budgets and for the owner to comment on why the item in their budget has changed

My excel world situation is I have 2 sheets
1. The budget
2. The extract of budget items that have a <>0 value

The Budget worksheet holds the data
Code Desc 2002 2003 Type
XXX XXX £XXX £XXX XXX

I need to look through this information and extract the above information to a new sheet if the figures between 2002 and 2003 are different.

This process should happen whenever the workbook is opened, new rows of data that meet the criteria should be added to the end of the list. If the numbers change then this should also change - although it would be good to highlight the change.

I know this can be done on a pivottable, but what I need is the data to be extracted to a new sheet and then some other information to be added next to the extracted data
I hope this helps,
Thanks in advance
AshLad

 

[ashlad]

Can anyone help with this problem?

 

[royUK]

Book1
	A	B	C	D	E	F
1	Code	Des	2002	2003	Type	Difference
2			1000	100		Var
3			1200	1000		Var
4			1000	1000
5
Sheet1

If you add a column and put in this formula as above you will select all rows where a variance occurs. Then use Autofilter to select just these rows and copy and paste to another sheet

 

[ashlad]

Thats fine. But I need the cut and paste bit to be automatic. I need a formulae or piece of code to extract the variance line items to a new sheet and keep these updated with changes on workbook open.

Thanks

Ashlad

 

[royUK]

Sub Extract_Data()

'Variables used by the macro
Dim FilterCriteria
Dim CurrentFileName As String
Dim NewFileName As String

'Get the current file's name
CurrentFileName = ActiveWorkbook.Name
'Select the first 6 columns and first 1000 rows
'(note you can change this to meet your requirements)
Range("A1:F1000").Select
'Apply Autofilter
Selection.AutoFilter
'Get the filter's criteria from the user
FilterCriteria = InputBox("Enter condition to be met")
'Filter the data based on the user's input
'NOTE - this filter is on column e (field:=1), to change
'to a different column you need to change the field number
Selection.AutoFilter field:=6, Criteria1:=FilterCriteria
'Select the visible cells (the filtered data)
Selection.SpecialCells(xlCellTypeVisible).Select
'Copy the cells
Selection.Copy
'Open a new file
Workbooks.Add Template:="Workbook"
'Get this file's name
NewFileName = ActiveWorkbook.Name
'Make sure you are in cell A1
Range("A1").Select
'Paste the copied cells
ActiveSheet.Paste
'Clear the clipboard contents
Application.CutCopyMode = False
'Go back to the original file
Workbooks(CurrentFileName).Activate
'Clear the autofilter
Selection.AutoFilter field:=1
'Take the Autofilter off
Selection.AutoFilter
'Go to A1
Range("A1").Select

End Sub

This should select your data and copy and paste to a new workbook, adapt as necessary
This message was edited by royUK on 2002-11-11 06:56
This message was edited by royUK on 2002-11-11 07:00