Copy Excel Data With Source Formatting

Question

I recorded a macro to do this, and copied the macro code and adapted it to how I needed it. However, my issue is that the source formatting is not kept when I paste over to the new worksheet. What step did I miss? It must be something to do with the Selection.PasteSpecial right? Below is the non-working syntax

Selection.AutoFilter
ActiveSheet.ListObjects("db1.accdb").Range.AutoFilter Field:=1, Criteria1:="Pink"
For LastRow = 2 To Worksheets("Sheet2").Range("A65536").End(xlUp).Row
  Next LastRow
Range("A1", "M" & LastRow).Copy
Sheets.Add After:=ActiveSheet
Selection.PasteSpecial Paste:=xlPasteAllUsingSourceTheme, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Range("A1").Select
ActiveSheet.Name = "Pink"

Answer 1

NO need for Selection.PasteSpecial, normal Copy method is sufficient.

Sub copyTest()

    '/ Source             Destination
    '--------             -----------
    Sheet1.UsedRange.Copy Sheet2.Cells(1, 1)
    Application.CutCopyMode = False

End Sub

<< --This will work for your code.>>

Sub Test()

    Dim LastRow     As Long
    Dim rngCopy     As Range


    Selection.AutoFilter
    ActiveSheet.ListObjects("db1.accdb").AutoFilter Field:=1, Criteria1:="Pink"

    Set rngCopy = ActiveSheet.UsedRange

    '/ Get rid of headers
    Set rngCopy = rngCopy.Offset(1).Resize(rngCopy.Rows.Count - 1)


    Set rngCopy = rngCopy.SpecialCells(XlCellType.xlCellTypeVisible)



    ThisWorkbook.Worksheets.Add after:=ActiveSheet
    ActiveSheet.Name = "Pink"

    rngCopy.Copy ThisWorkbook.Worksheets("Pink").Cells(1, 1)
    Application.CutCopyMode = False

End Sub