Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Extract Client Refs from pivot table into another worksheet | Excel Worksheet Functions | |||
Extract data from a table | Excel Worksheet Functions | |||
Extract Data From A Table | Excel Discussion (Misc queries) | |||
HOW TO EXTRACT (or lookup) DATA FROM A PIVOT TABLE | New Users to Excel | |||
Formula to Extract Data from a Table | Excel Worksheet Functions |