Autofilter and choosing filtered columns

H

Hardy

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(xlCellTypeVisible).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
 
T

Tom Ogilvy

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.
 
Top