R
Richard
Hi,
I have a database saved from SAP to Excel and want to perform following w/
the data:
1. Filter out all lines with 0,00 in 2 columns
2. Delete them
3. Turn off Autofilter, ie. show all the rest and sort acc. Date (format:
01.01.2009, but formatted as text)
4. Copy/paste to another xls sheet
This works perfectly if I do Autofilter manually, of course.
To save time I saved a MACRO but it doesn't filter anything, however there
are 0,00's as well as don't sort correctly, although answered YES to "Sort
anything that looks like a number as number" but sort as text (02.03.2009 <
03.01.2009)...
I read few threads about problems with autofilter as to filter dates but
never how to filter numbers correctly...
I would appreciate your help as I am absolutely new to VBA.
The current code:
Selection.AutoFilter Field:=10, Criteria1:="0,00"
Selection.AutoFilter Field:=12, Criteria1:="0,00"
Rows("2:800").Select
Selection.Delete Shift:=xlUp
Selection.AutoFilter
Range("A1:U130").Sort Key1:=Range("I1"), Order1:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom, _
DataOption1:=xlSortTextAsNumbers
Columns("A:Q").Select
Selection.Copy
Windows("!s711 PROD Planning Template.xls").Activate
ActiveWindow.WindowState = xlMaximized
Sheets("s721").Select
Range("A1").Select
ActiveSheet.Paste
I have a database saved from SAP to Excel and want to perform following w/
the data:
1. Filter out all lines with 0,00 in 2 columns
2. Delete them
3. Turn off Autofilter, ie. show all the rest and sort acc. Date (format:
01.01.2009, but formatted as text)
4. Copy/paste to another xls sheet
This works perfectly if I do Autofilter manually, of course.
To save time I saved a MACRO but it doesn't filter anything, however there
are 0,00's as well as don't sort correctly, although answered YES to "Sort
anything that looks like a number as number" but sort as text (02.03.2009 <
03.01.2009)...
I read few threads about problems with autofilter as to filter dates but
never how to filter numbers correctly...
I would appreciate your help as I am absolutely new to VBA.
The current code:
Selection.AutoFilter Field:=10, Criteria1:="0,00"
Selection.AutoFilter Field:=12, Criteria1:="0,00"
Rows("2:800").Select
Selection.Delete Shift:=xlUp
Selection.AutoFilter
Range("A1:U130").Sort Key1:=Range("I1"), Order1:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom, _
DataOption1:=xlSortTextAsNumbers
Columns("A:Q").Select
Selection.Copy
Windows("!s711 PROD Planning Template.xls").Activate
ActiveWindow.WindowState = xlMaximized
Sheets("s721").Select
Range("A1").Select
ActiveSheet.Paste