How to extract data from table in other worksheet
 

Hi,

I have one table with autofilter, and all I need is a button to copy the
first line of autofilter result in other worksheet, to be able to print data
I wish out.

problem is if i make macro with record, it will copy only the line I
selected, and autofilter keeps the same line numbers, so it will allways
copy the same I selected, and not the one I filtered.

pls....help.

with thanks

How to extract data from table in other worksheet
 

One way:

Option Explicit
Sub testme()
Dim RngToCopy As Range
Dim DestCell As Range

With Worksheets("sheet1")
With .AutoFilter.Range
If .Columns(1).Cells.SpecialCells(xlCellTypeVisible). Count = 1 Then
MsgBox "only the headers are visible"
Exit Sub
End If
'resize to ignore the header
'and come down one row (with the .offset())
Set RngToCopy = .Resize(.Rows.Count - 1).Offset(1, 0) _

..Cells.SpecialCells(xlCellTypeVisible).Rows(1)
End With
End With

With Worksheets("sheet2")
Set DestCell = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0)
End With

RngToCopy.Copy _
Destination:=DestCell

End Sub

Crazy wrote:

Hi,

I have one table with autofilter, and all I need is a button to copy the
first line of autofilter result in other worksheet, to be able to print data
I wish out.

problem is if i make macro with record, it will copy only the line I
selected, and autofilter keeps the same line numbers, so it will allways
copy the same I selected, and not the one I filtered.

pls....help.

with thanks

--

Dave Peterson

How to extract data from table in other worksheet
 

Watch out for wrapping text...

The code didn't change, but the formatting did.

Option Explicit
Sub testme()
Dim RngToCopy As Range
Dim DestCell As Range

With Worksheets("sheet1")
With .AutoFilter.Range
If .Columns(1).Cells.SpecialCells(xlCellTypeVisible). Count = 1 Then
MsgBox "only the headers are visible"
Exit Sub
End If
'resize to avoid the header
'and come down one row
Set RngToCopy = .Resize(.Rows.Count - 1).Offset(1, 0) _
.Cells.SpecialCells(xlCellTypeVisible).Rows(1)
End With
End With

With Worksheets("sheet2")
Set DestCell = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0)
End With

RngToCopy.Copy _
Destination:=DestCell

End Sub


Dave Peterson wrote:
<<snipped
Crazy wrote:

Hi,

I have one table with autofilter, and all I need is a button to copy the
first line of autofilter result in other worksheet, to be able to print data
I wish out.

problem is if i make macro with record, it will copy only the line I
selected, and autofilter keeps the same line numbers, so it will allways
copy the same I selected, and not the one I filtered.

pls....help.

with thanks

--

Dave Peterson

--

Dave Peterson

How to extract data from table in other worksheet
 

Hi Dave,

What to say than: THANKS, it is great...



"Dave Peterson" wrote in message
...
Watch out for wrapping text...

The code didn't change, but the formatting did.

Option Explicit
Sub testme()
Dim RngToCopy As Range
Dim DestCell As Range

With Worksheets("sheet1")
With .AutoFilter.Range
If .Columns(1).Cells.SpecialCells(xlCellTypeVisible). Count = 1
Then
MsgBox "only the headers are visible"
Exit Sub
End If
'resize to avoid the header
'and come down one row
Set RngToCopy = .Resize(.Rows.Count - 1).Offset(1, 0) _

.Cells.SpecialCells(xlCellTypeVisible).Rows(1)
End With
End With

With Worksheets("sheet2")
Set DestCell = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0)
End With

RngToCopy.Copy _
Destination:=DestCell

End Sub


Dave Peterson wrote:
<<snipped
Crazy wrote:

Hi,

I have one table with autofilter, and all I need is a button to copy
the
first line of autofilter result in other worksheet, to be able to print
data
I wish out.

problem is if i make macro with record, it will copy only the line I
selected, and autofilter keeps the same line numbers, so it will
allways
copy the same I selected, and not the one I filtered.

pls....help.

with thanks

--

Dave Peterson

--

Dave Peterson