Extract Data

ashlad

Board Regular
Joined
Aug 22, 2002
Messages
56
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
 

Some videos you may like

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

royUK

Well-known Member
Joined
Jul 24, 2002
Messages
1,606
Book1
ABCDEF
1CodeDes20022003TypeDifference
21000100Var
312001000Var
410001000 
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

Board Regular
Joined
Aug 22, 2002
Messages
56
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

Well-known Member
Joined
Jul 24, 2002
Messages
1,606
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,721
Messages
5,597,752
Members
414,171
Latest member
12Rev79

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