Copy first filtered row

[sukyb1]

I want to copy cells from a the first filtered row.
My filtered row is in row 28, but i only want to copy the values of column E,F and G in row 28 and paste them in A1,2,3
this should happen using a worksheet change trigger.
Any help appreciated.

 

[venkat1926]

I am assuming you have already auto-filtered the data manually or by a macro

then you try this macro to copy the first non heading row columns E to G to cells A1 to A3 IN SHEET 2

Sub test()
Dim rfilt As Range, r As Range
Worksheets("sheet1").Activate
Set rfilt = ActiveSheet.AutoFilter.Range
Set rfilt = rfilt.Offset(1, 0).Resize(rfilt.Rows.Count - 1, rfilt.Columns.Count).SpecialCells(xlCellTypeVisible)
MsgBox rfilt.Address
Set r = rfilt.Cells(1, 1)
Range(Cells(r.Row, "E"), Cells(r.Row, "g")).Copy
Worksheets("sheet2").Range("A1").PasteSpecial Transpose:=True

End Sub

 

[sukyb1]

Hi...thanks for that it works on a blank workbook but i cant get it to work for my workbook.

I have sheet 1 with my datatable where the user filters....i am interested in taking columns E-I and pasting them in Sheet 2 Cells A5-A10

How would i change your code to do this? and the change needs to be automatically made when the worksheet changes as opposed to having to run a macro.

Thanks for your help...x

 

[sukyb1]

I am assuming you have already auto-filtered the data manually or by a macro

then you try this macro to copy the first non heading row columns E to G to cells A1 to A3 IN SHEET 2

Sub test()
Dim rfilt As Range, r As Range
Worksheets("sheet1").Activate
Set rfilt = ActiveSheet.AutoFilter.Range
Set rfilt = rfilt.Offset(1, 0).Resize(rfilt.Rows.Count - 1, rfilt.Columns.Count).SpecialCells(xlCellTypeVisible)
MsgBox rfilt.Address
Set r = rfilt.Cells(1, 1)
Range(Cells(r.Row, "E"), Cells(r.Row, "g")).Copy
Worksheets("sheet2").Range("A1").PasteSpecial Transpose:=True
 
End Sub

Hi...thanks for that it works on a blank workbook but i cant get it to work for my workbook.

I have sheet 1 with my datatable where the user filters....i am interested in taking columns E-I and pasting them in Sheet 2 Cells A5-A10

How would i change your code to do this? and the change needs to be automatically made when the worksheet changes as opposed to having to run a macro.

Thanks for your help...x

 

[sukyb1]

Hi...thanks for that it works on a blank workbook but i cant get it to work for my workbook.

I have sheet 1 with my datatable where the user filters....i am interested in taking columns E-I and pasting them in Sheet 2 Cells A5-A10

How would i change your code to do this? and the change needs to be automatically made when the worksheet changes as opposed to having to run a macro.

Thanks for your help...x

 

[venkat1926]

can you post a very small extract of your database and how you want it in sheet .2.

 

[kkfighter]

Hi Venkat,

I will propose you to use visible cells.

Instruction:
Select the cells that you want to copy.
On the Home tab, in the Editing group, click Find & Select, and then click Go To.
In the Go To dialog box, click Special
Under Select, click Visible cells only, and then click OK
On the Home tab, in the Clipboard group, click Copy
Select the upper-left cell of the paste area
On the Home tab, in the Clipboard group, click Paste

You can add it as ribbon (shortcut) for convenient usage.

Hope this helps.

Smile,
KK

 

[sukyb1]

have you got an email address i can send you an extract too?

 

[venkat1926]

I do not know whether the newsgroup may allow gi personal communications. you can post the small extract of 5 to 10 tows and about 5 columns. highlight and copy it in your post.(of course you can use mr.excel html if you are familiar with that. ). however my email address is below my post.