Copy AutoFiltered result to another worksheet

[ExcelHound]

I’m looking for assistant with the following macro – this works well but I would like to achieve some small tweaks.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
I’m looking for a macro to select to contents of Column ‘A’ on Sheet1 where Sheet1 has an autofilter – Column D is AutoFiltered to only select entries with a ‘Yes’ value. <o></o>
Sheet1 has a Header starting at row A5. Sheet1 has multiple columns of data included in the AutoFilter but I only want to select the filtered result of column A (minus the header) then paste it onto Sheet2 at A5 (the next free cell).
<o></o>
Is this possible? Many thanks in advance.<o></o>
<o> </o>
The macro works fine but copies all columns, I just want to copy column A.<o></o>
Sub CopyFilter()<o></o>
Dim rng As Range<o></o>
Dim rng2 As Range<o></o>
<o> </o>
With ActiveSheet.AutoFilter.Range<o></o>
On Error Resume Next<o></o>
Set rng2 = .Offset(1, 0).Resize(.Rows.Count - 1, 1) _<o></o>
.SpecialCells(xlCellTypeVisible)<o></o>
On Error GoTo 0<o></o>
<o> </o>
End With<o></o>
<o> </o>
If rng2 Is Nothing Then<o></o>
MsgBox "No data to copy"<o></o>
<o> </o>
Else<o></o>
Worksheets("Sheet2").Cells.Clear<o></o>
Set rng = ActiveSheet.AutoFilter.Range<o></o>
rng.Offset(1, 0).Resize(rng.Rows.Count - 1).Copy _<o></o>
Destination:=Worksheets("Sheet2").Range("A1")<o></o>

 

[ajm]

hi,

i notice you got the code from the contextures website. have you tried using the macro recorder? it will give you the basics of what you want. after recording it, change the specified ranges to make them dynamic and off you go. let me know if i can try and help further. ajm

 

[ExcelHound]

Many thanks for the reply; I did try using the recorder but I cant seem to get it to be dynamic with the select - it just selects the cell range where the values are.

That maco enclosed in the original post works a charm but selects all columns of data then pastes into onto sheet2, is it a qucik fix to have it only copy columnA entries.

I also wanted to try and combine it with the following to dynamically paste the results at the end of the existing entries in column A.

Cells(Rows.Count, "A").End(xlUp).Offset(1).Select


many thanks

 

[ajm]

weird that autofilter range bit. i believe that by using that in the code it makes your selection become the entire autofilter range, irrespective of which column you specify. i have a messy macro that should do the trick, given what we have discussed so far.

Sub Macro1()
 Sheets("Sheet1").Activate
   Range("A6").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.SpecialCells(xlCellTypeVisible).Copy
    Sheets("Sheet2").Select
    lastrow = Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Row
    Range("A" & lastrow + 1).Select
    ActiveSheet.Paste
End Sub

assumptions: the autofiltered range will always be on "Sheet1" (if not change wherever you see "Sheet1" to your sheet name)

also, i assumed that you already had something in column A on Sheet 2, given the way you referred to the next free cell.

hope this can be of some help. I am still a hack when it comes to code but get a kick out of using it, all the same.

 

[ExcelHound]

Monty work!!! works like a charm, many thanks for your help

 

[ajm]

mate, no problems. helping others on this board is the best way to repay the time and kindness of those that have helped me in the past. have a great week. ajm