Macro- Excel 2003

[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

 

[Dave Peterson]

First, I'm not sure what that second point is.

Second, it's difficult to know how things work since you work with the
selection, but this may do what you want.

Option Explicit
Sub testme01()

Dim FromWks As Worksheet
Dim ToWks As Worksheet

Dim HowManyVisibleRows As Long
Dim VisRng As Range
Dim DestCell As Range

Set FromWks = Worksheets("Commercial Register")
Set ToWks = Worksheets("Survey and Quote Quick View")

With FromWks
.UsedRange.EntireColumn.Hidden = False
If .FilterMode Then
.ShowAllData
End If

.UsedRange.AutoFilter Field:=27, Criteria1:="<6"
.UsedRange.AutoFilter Field:=13, Criteria1:="S&Q"

.Range("AC:AG,AA:AA,K:Y,G:G,D:E").EntireColumn.Hidden = True

With .AutoFilter.Range
'subtract 1 for the header
HowManyVisibleRows = .Columns(1).Cells _
.SpecialCells(xlCellTypeVisible).Count - 1

If HowManyVisibleRows = 0 Then
'only header row visible
'do nothing???
Else
Set VisRng = .Resize(.Rows.Count - 1, 28).Offset(1, 0) _
.Cells.SpecialCells(xlCellTypeVisible)

With ToWks
Set DestCell = .Range("e20")
End With

VisRng.Copy _
Destination:=DestCell

With DestCell.Resize(HowManyVisibleRows, 28)
With .Interior
.ColorIndex = 5
.Pattern = xlSolid
End With
.Font.ColorIndex = 2
End With
End If
End With
End With

End Sub

 

[OzzyJim]

This is awesome Dave......

Appreciate your help given it's hard to explain without seeing the database

Cheers
Jim

 

[Dave Peterson]

If it doesn't work the way you want, see if you can modify the code.

If you have trouble, then post back--but this time include a few more details
about how your data is laid out--where you're applying the autofilter--and even
how the destination cell's address is determined (if it's variable).