Tom,
I found this bit of code, but I'm getting an Error 91.
I tried Dim rng As Range, but still nogo.
Any suggestion?

"Tom Ogilvy" wrote:

Set rng = ActiveSheet.Autofilter.Range
if rng.Columns(1).SpecialCells(xlVisible).Count 1 then
rng.offset(1,0).Resize(rng.rows.count-1).Copy _
Destination:= Worksheets(2).Range("A1")
Else
msgbox "No visible data"
End If

--
Regards,
Tom Ogilvy

"JH" wrote in message
...
Hello,

I'm using autofilter in my sheet. The sheet is filtred by many criteria
and
has a header. Criteria are changed in loop.
After filtering I'd like to copy filtred data copy to another sheet(the
same
sheet for all entries) but without the header.
I don't know how to recognize where data start. Some rows are alaways
hiden
and "first" row under the header may sometimes be 16th row sometimes 5th
row
etc.
And I don't want the header to copy together with filtred data
thanks in advance

JH

What line do you get that error?

If it's the "Set rng = ActiveSheet.Autofilter.Range" line, then make sure that
the activesheet has Data|Filter|autofilter applied before you run the macro.

If it's not that line, post back with more info.

David wrote:

Tom,
I found this bit of code, but I'm getting an Error 91.
I tried Dim rng As Range, but still nogo.
Any suggestion?

"Tom Ogilvy" wrote:

Set rng = ActiveSheet.Autofilter.Range
if rng.Columns(1).SpecialCells(xlVisible).Count 1 then
rng.offset(1,0).Resize(rng.rows.count-1).Copy _
Destination:= Worksheets(2).Range("A1")
Else
msgbox "No visible data"
End If

--
Regards,
Tom Ogilvy

"JH" wrote in message
...
Hello,

I'm using autofilter in my sheet. The sheet is filtred by many criteria
and
has a header. Criteria are changed in loop.
After filtering I'd like to copy filtred data copy to another sheet(the
same
sheet for all entries) but without the header.
I don't know how to recognize where data start. Some rows are alaways
hiden
and "first" row under the header may sometimes be 16th row sometimes 5th
row
etc.
And I don't want the header to copy together with filtred data
thanks in advance

JH






--

Dave Peterson

I see...no, the AutoFilter is NOT turned on...I'm using code to apply the
filter. Guess this won't work then.
Thanks anyway Dave!!

"Dave Peterson" wrote:

What line do you get that error?

If it's the "Set rng = ActiveSheet.Autofilter.Range" line, then make sure that
the activesheet has Data|Filter|autofilter applied before you run the macro.

If it's not that line, post back with more info.

David wrote:

Tom,
I found this bit of code, but I'm getting an Error 91.
I tried Dim rng As Range, but still nogo.
Any suggestion?

"Tom Ogilvy" wrote:

Set rng = ActiveSheet.Autofilter.Range
if rng.Columns(1).SpecialCells(xlVisible).Count 1 then
rng.offset(1,0).Resize(rng.rows.count-1).Copy _
Destination:= Worksheets(2).Range("A1")
Else
msgbox "No visible data"
End If

--
Regards,
Tom Ogilvy

"JH" wrote in message
...
Hello,

I'm using autofilter in my sheet. The sheet is filtred by many criteria
and
has a header. Criteria are changed in loop.
After filtering I'd like to copy filtred data copy to another sheet(the
same
sheet for all entries) but without the header.
I don't know how to recognize where data start. Some rows are alaways
hiden
and "first" row under the header may sometimes be 16th row sometimes 5th
row
etc.
And I don't want the header to copy together with filtred data
thanks in advance

JH






--

Dave Peterson

You can record a macro when you apply the filter and filter the way you want.

Then just add Tom's code to the bottom. It may even work!

David wrote:

I see...no, the AutoFilter is NOT turned on...I'm using code to apply the
filter. Guess this won't work then.
Thanks anyway Dave!!

"Dave Peterson" wrote:

What line do you get that error?

If it's the "Set rng = ActiveSheet.Autofilter.Range" line, then make sure that
the activesheet has Data|Filter|autofilter applied before you run the macro.

If it's not that line, post back with more info.

David wrote:

Tom,
I found this bit of code, but I'm getting an Error 91.
I tried Dim rng As Range, but still nogo.
Any suggestion?

"Tom Ogilvy" wrote:

Set rng = ActiveSheet.Autofilter.Range
if rng.Columns(1).SpecialCells(xlVisible).Count 1 then
rng.offset(1,0).Resize(rng.rows.count-1).Copy _
Destination:= Worksheets(2).Range("A1")
Else
msgbox "No visible data"
End If

--
Regards,
Tom Ogilvy

"JH" wrote in message
...
Hello,

I'm using autofilter in my sheet. The sheet is filtred by many criteria
and
has a header. Criteria are changed in loop.
After filtering I'd like to copy filtred data copy to another sheet(the
same
sheet for all entries) but without the header.
I don't know how to recognize where data start. Some rows are alaways
hiden
and "first" row under the header may sometimes be 16th row sometimes 5th
row
etc.
And I don't want the header to copy together with filtred data
thanks in advance

JH






--

Dave Peterson


--

Dave Peterson

I recorded it, got one line which I put at the front...no go. Here's what I
have:

Sub MoveData()
Dim rng As Range

Application.Run "'Payroll Combo.xls'!ApplyFilter" '<<< New Line
Set rng = ActiveSheet.AutoFilter.Range
If rng.Columns(1).SpecialCells(xlVisible).Count 1 Then
rng.Offset(1, 0).Resize(rng.Rows.Count - 1).Copy _
Destination:=Worksheets("sheet1").Range("A2")
Else
MsgBox "No visible data"
End If

End Sub

I'm trying some new code to try and do the same thing, but not getting any
results, although it seems to be working. Maybe this is a better alternative,
if I can get it to work:
Sub CopyData()

Dim lRow As Long 'Last Row
Dim nRow As Long 'Next Row to copy to
Dim cnt As Long

lRow = Sheets("All_Jobs").Range("A" & Sheets("All_Jobs").Rows.Count).End(xlUp)
With Sheets("All_Jobs")
For cnt = 7 To lRow '<<<Data starts on Row 7
If .Range("A" & cnt) = ("FilterCriteria") Then '<<FilterCriteria is the
named range of two cells with a start date - end date, without the headers. I
THINK THE PROBLEM IS HERE!!
nRow = Sheets("sheet1").Range("A" & _
Sheets("sheet1").Rows.Count).End(xlUp).Offset(1, 0).Row
.Range("A" & cnt).Copy Sheets("sheet1").Range("A" & nRow + 1) '<< I
need to start the paste on Row 2. Row 1 has headers.
End If
Next
End With


End Sub

Try recording it as you select the range
then Data|Filter|autofilter.

I have no idea what ApplyFilter in payroll combo.xls actually does.

And this line is gonna cause trouble


David wrote:

I recorded it, got one line which I put at the front...no go. Here's what I
have:

Sub MoveData()
Dim rng As Range

Application.Run "'Payroll Combo.xls'!ApplyFilter" '<<< New Line
Set rng = ActiveSheet.AutoFilter.Range
If rng.Columns(1).SpecialCells(xlVisible).Count 1 Then
rng.Offset(1, 0).Resize(rng.Rows.Count - 1).Copy _
Destination:=Worksheets("sheet1").Range("A2")
Else
MsgBox "No visible data"
End If

End Sub

I'm trying some new code to try and do the same thing, but not getting any
results, although it seems to be working. Maybe this is a better alternative,
if I can get it to work:
Sub CopyData()

Dim lRow As Long 'Last Row
Dim nRow As Long 'Next Row to copy to
Dim cnt As Long

lRow = Sheets("All_Jobs").Range("A" & Sheets("All_Jobs").Rows.Count).End(xlUp)
With Sheets("All_Jobs")
For cnt = 7 To lRow '<<<Data starts on Row 7
If .Range("A" & cnt) = ("FilterCriteria") Then '<<FilterCriteria is the
named range of two cells with a start date - end date, without the headers. I
THINK THE PROBLEM IS HERE!!
nRow = Sheets("sheet1").Range("A" & _
Sheets("sheet1").Rows.Count).End(xlUp).Offset(1, 0).Row
.Range("A" & cnt).Copy Sheets("sheet1").Range("A" & nRow + 1) '<< I
need to start the paste on Row 2. Row 1 has headers.
End If
Next
End With

End Sub

--

Dave Peterson

Try recording it as you select the range
then Data|Filter|autofilter.

I have no idea what ApplyFilter in payroll combo.xls actually does.

And this line is gonna cause trouble

If .Range("A" & cnt) = ("FilterCriteria")

If filterCriteria is a 2 celled range, comparing one cell to those two cells
won't work.

maybe...

if .range("a" & cnt).value _
= worksheets("somesheetname").range("FilterCriteria" ).cells(1).value then
....



David wrote:

I recorded it, got one line which I put at the front...no go. Here's what I
have:

Sub MoveData()
Dim rng As Range

Application.Run "'Payroll Combo.xls'!ApplyFilter" '<<< New Line
Set rng = ActiveSheet.AutoFilter.Range
If rng.Columns(1).SpecialCells(xlVisible).Count 1 Then
rng.Offset(1, 0).Resize(rng.Rows.Count - 1).Copy _
Destination:=Worksheets("sheet1").Range("A2")
Else
MsgBox "No visible data"
End If

End Sub

I'm trying some new code to try and do the same thing, but not getting any
results, although it seems to be working. Maybe this is a better alternative,
if I can get it to work:
Sub CopyData()

Dim lRow As Long 'Last Row
Dim nRow As Long 'Next Row to copy to
Dim cnt As Long

lRow = Sheets("All_Jobs").Range("A" & Sheets("All_Jobs").Rows.Count).End(xlUp)
With Sheets("All_Jobs")
For cnt = 7 To lRow '<<<Data starts on Row 7
If .Range("A" & cnt) = ("FilterCriteria") Then '<<FilterCriteria is the
named range of two cells with a start date - end date, without the headers. I
THINK THE PROBLEM IS HERE!!
nRow = Sheets("sheet1").Range("A" & _
Sheets("sheet1").Rows.Count).End(xlUp).Offset(1, 0).Row
.Range("A" & cnt).Copy Sheets("sheet1").Range("A" & nRow + 1) '<< I
need to start the paste on Row 2. Row 1 has headers.
End If
Next
End With

End Sub

--

Dave Peterson