Hi,

Have Sheet1 and have written code to filter data, but slightly stuck o
the copying thereof. I only want to copy to Sheet2 selected columns o
filtered data (10 of over 30 cols).

The code I am using is not brilliant, should do the job, but fails fo
reason I cannot quite fathom.

Worksheets("Sheet1").Activate
Range("A1").Activate
ActiveCell.Offset(1, iIssuerPos - 1).Activate
ActiveCell.Range(Selection
Selection.End(xlDown)).SpecialCells(xlCellTypeVisi ble).Select
Selection.Copy
WorkSheets("Sheet2").Activate
ActiveSheet.Paste

iIssuerPos is the col no. of the data I want to copy (4 in this case).
The second line take me to the correct column
The next line chooses the filtered data from the column 3 to the right
This error is always the column number -1 (in this case 4-1=3).

Thanks in advanc

--
Message posted from http://www.ExcelForum.com

Dim rng as Range, rng1 as Range
set rng = ActiveSheet.Autofilter.Range
msgbox "iIssuerPos = " & iIssuerPos
set rng1 = Intersect(Activesheet.Columns(iIssuerPos),rng)
rng1.copy
Sheets("Sheet2").Paste

by default, only the visible cells of an autofilter are copied.

--
Regards,
Tom Ogilvy


"Hardy " wrote in message
...
Hi,

Have Sheet1 and have written code to filter data, but slightly stuck on
the copying thereof. I only want to copy to Sheet2 selected columns of
filtered data (10 of over 30 cols).

The code I am using is not brilliant, should do the job, but fails for
reason I cannot quite fathom.

Worksheets("Sheet1").Activate
Range("A1").Activate
ActiveCell.Offset(1, iIssuerPos - 1).Activate
ActiveCell.Range(Selection,
Selection.End(xlDown)).SpecialCells(xlCellTypeVisi ble).Select
Selection.Copy
WorkSheets("Sheet2").Activate
ActiveSheet.Paste

iIssuerPos is the col no. of the data I want to copy (4 in this case).
The second line take me to the correct column
The next line chooses the filtered data from the column 3 to the right.
This error is always the column number -1 (in this case 4-1=3).

Thanks in advance


---
Message posted from http://www.ExcelForum.com/