Code working but on wrong sheet

[pauldaddyadams]

Hi,

The code below works however it copies it to the wrong sheet (the sheet you are viewing when the macro is ran).

I think this is the part i need to amend:

CopyToRange:=Range("A1"), Unique:=True

Is there any way I can select the sheet I want to copy it to e.g.
=Range(Sheets("macro"."A1")

This is the code in full

Sub Macro1()

' Macro1 Macro

    Sheets("May Sales.xls").Columns("D:D").AdvancedFilter Action:=xlFilterCopy, _
        CopyToRange:=Range("A1"), Unique:=True
    Sheets("macro").Range("A2:A" & Sheets("Macro").Cells(Rows.Count, "A").End(xlUp).Row).Copy
    ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
    Sheets("Overview").Range("I4").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
End Sub

[royUK]

You need to add the sheet name before that part

[pauldaddyadams]

How do I do that, I tried this but it didnt work (sheet name is Macro)

CopyToRange:=Sheets("Macro").Range("A1"), Unique:=True

[Alf]

You could try:

CopyToRange:=Range("Macro!A1"), Unique:=True

Alf

[pauldaddyadams]

Unfortunatly that didnt work

[Colin Legg]

Hi Paul,

Copying to a different sheet with the advanced filter is one of the rare occasions when you have to use the Worksheet.Select method. That is, you must ensure that the target sheet is the active sheet before you call Range.AdvancedFilter.

Something like this (untested):

Sub Macro1()

    ' Macro1 Macro
    
    Worksheets("Macro").Select    

    Worksheets("May Sales.xls").Columns("D:D").AdvancedFilter _
                        Action:=xlFilterCopy, _
                        CopyToRange:=Worksheets("Macro").Range("A1"), _
                        Unique:=True
                        
    Worksheets("macro").Range("A2:A" & Worksheets("Macro").Cells(Worksheets("macro").Rows.Count, "A").End(xlUp).Row).Copy
    ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
    Worksheets("Overview").Range("I4").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=True

End Sub

[pauldaddyadams]

Excellent - that worked!! Thank you!

I wish i never ever used advanced filter lol