Autofilter and copy specific column without heading

[CD2017]

Hello,

Here is my code to filter Column E, and copy the Data in Column I.

At the moment, I'm copying the header aswell. Can you tell me how I can copy the data in Column I without the heading?

Code:

Sub Financials_Pop_Emails()

With Sheets("mySS Investor Details").Range("A1")

.AutoFilter _
Field:=5, _
Criteria1:=Array("Financials", "Ptnr Cap Stmt"), _
Operator:=xlFilterValues

End With

Sheets("mySS Investor Details").Columns("I").SpecialCells(xlCellTypeVisible).Copy

End Sub


Any help appreciated - thanks

 

[MrIfOnly]

Try:

Sheets("mySS Investor Details").Columns("I").Offset(1,0).SpecialCells(xlCellTypeVisible).Copy

Regards,

CJ

 

[CD2017]

Hi CJ,

Thanks for quick response. I'm getting run-time error '1004':

 

[MrIfOnly]

Ok, try this slightly different method:

With Sheets("mySS Investor Details")
        With Intersect(.UsedRange, .Columns("I"))
            Set rngVis = .Offset(1, 0).SpecialCells(xlCellTypeVisible)
        End With
End With

rngVis.Copy

Let me know if it still doesn't work.

Regards,

CJ

 

[CD2017]

Works - thank you!

Ok, try this slightly different method:

With Sheets("mySS Investor Details")
        With Intersect(.UsedRange, .Columns("I"))
            Set rngVis = .Offset(1, 0).SpecialCells(xlCellTypeVisible)
        End With
End With

rngVis.Copy

Let me know if it still doesn't work.

Regards,

CJ

 

[MrIfOnly]

You're very welcome.

CJ

 

[CD2017]

Hi CJ,

One more question on this. Is it possible to concatenate the rngVis with ";" before it's copied?[

The value in rngVis is an email address but I need the semi colon added to the end of it!

Thanks

QUOTE=MrIfOnly;4811029]You're very welcome.

CJ[/QUOTE]

 

[MrIfOnly]

No, because rngVis is actually a range (and should be dimmed as such, BTW). What you could do, after pasting the range to its destination, is loop thru that range and add the semicolon. If this is something you would like help on, please post the code showing where the column of data is being pasted.

Regards,

CJ