Solved: Copy Selected Data to New Worksheet

[jo15765]

In Excel I am importing a SQL Stored Procedure to sheet one and using vba to filter:

Selection.AutoFilter Field:=2, Criteria1:="EmployeeNames"
Selection.AutoFilter Field:=5, Criteria1:="StillActive"
Sheets.Add.Name = "Still Active Employee"


Now the predicament I have is that need to copy Column A from the SQL Stored procedure (Sheet1) to Column B starting in Row 4 going down, then copy (again Sheet1) Column D to Column G to Still Active Employee sheet. And also, any records from Sheet1 that are flagged as 'Yes' in Column C need to be highlighted in Yellow on Still Active.

I have been scratching my head for 3 days now trying to tackle this!! Guidance please?!

[Teeroy]

Hi jo15765,

Guidance as requested (rather than detail). Because you have autofiltered and only want the shown items you need to use something like

Range.specialcells(xlCellTypeVisible).copy destination:=sheets("Still Active Employee").Range For the highlight I'd copy Cols C-G (ie take the yes as well) in the same manner. In the copy loop through the rows with a range object and if there's a yes in the col then set
range.entirerow.interior.colorindex=6
If you want more coding detail let me know.

[jo15765]

Perfect, those snippets were the guiding lite I needed to accomplish what I was after. Thank you!

[Teeroy]

You are welcome.