O
OzzyJim
Hi All,
Have a complex database shared amongst users. Am using a macro to extract
data and to display on a seperate page. My macro below starts with ensuring
that all columns are unhidden and all data is shown. Runs in current format
no problems however have noticed the following
1) If the database starts in the open condition (no filters or hidden
columns) then the macro will fail.
2) Would like to show only 10 results but if I limit line numbers in the
range it omits anything past the last line entered, where I may have data
10's of lines apart
Any ideas?
Cheers
Jim
Sheets("Commercial Register").Select
Cells.Select
Selection.EntireColumn.Hidden = False
Range("E4").Select
ActiveSheet.ShowAllData
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 10
ActiveWindow.ScrollColumn = 11
ActiveWindow.ScrollColumn = 12
ActiveWindow.ScrollColumn = 13
ActiveWindow.ScrollColumn = 14
Selection.AutoFilter Field:=27, Criteria1:="<6", Operator:=xlAnd
Selection.AutoFilter Field:=13, Criteria1:="S&Q"
Range("AC:AG,AA:AA,K:Y,G:G,D:E").Select
Range("E1").Activate
Selection.EntireColumn.Hidden = True
Range("B14:AB100").Select
Selection.Copy
Sheets("Survey and Quote Quick View").Select
Range("E20").Select
ActiveSheet.Paste
With Selection.Interior
.ColorIndex = 5
.Pattern = xlSolid
End With
Selection.Font.ColorIndex = 2
Have a complex database shared amongst users. Am using a macro to extract
data and to display on a seperate page. My macro below starts with ensuring
that all columns are unhidden and all data is shown. Runs in current format
no problems however have noticed the following
1) If the database starts in the open condition (no filters or hidden
columns) then the macro will fail.
2) Would like to show only 10 results but if I limit line numbers in the
range it omits anything past the last line entered, where I may have data
10's of lines apart
Any ideas?
Cheers
Jim
Sheets("Commercial Register").Select
Cells.Select
Selection.EntireColumn.Hidden = False
Range("E4").Select
ActiveSheet.ShowAllData
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 10
ActiveWindow.ScrollColumn = 11
ActiveWindow.ScrollColumn = 12
ActiveWindow.ScrollColumn = 13
ActiveWindow.ScrollColumn = 14
Selection.AutoFilter Field:=27, Criteria1:="<6", Operator:=xlAnd
Selection.AutoFilter Field:=13, Criteria1:="S&Q"
Range("AC:AG,AA:AA,K:Y,G:G,D:E").Select
Range("E1").Activate
Selection.EntireColumn.Hidden = True
Range("B14:AB100").Select
Selection.Copy
Sheets("Survey and Quote Quick View").Select
Range("E20").Select
ActiveSheet.Paste
With Selection.Interior
.ColorIndex = 5
.Pattern = xlSolid
End With
Selection.Font.ColorIndex = 2