Copy sheet INCLUDING FORMULAS with Autofilter

[ohuffman7]

Trying to get my data to copy into a new sheet but i need the formulas to be copied as well. Any ideas?? Currently this just copies the cells' values.

Sub MoveData()
Sheet6.[Y1:Y1901].AutoFilter 1, "PASS"
Sheet6.[A1:Y1901].Copy Sheet2.[A1]
Sheet6.[A1].AutoFilter
End Sub

 

[Trebor76]

Hi ohuffman7,

Welcome to MrExcel!!

You will need to use PasteSpecial when copying the filtered (visible) range which cannot be done on a one liner like you have. See how this goes:

Option Explicit
Sub MoveData()

    Sheet6.[Y1:Y1901].AutoFilter 1, "PASS"
    Sheet6.[A1:Y1901].SpecialCells(xlCellTypeVisible).Copy
    Sheet2.[A1].PasteSpecial xlPasteFormulas
    Sheet6.[A1].AutoFilter

End Sub

Regards,

Robert

 

[igold]

This...

Sub MoveData()
    Sheet6.[Y1:Y1901].AutoFilter 1, "PASS"
    Sheet6.[A1:Y1901].Copy
    Sheet2.[A1].PasteSpecial xlPasteAll
    Sheet6.[A1].AutoFilter
End Sub

 

[ohuffman7]

Hi ohuffman7,

Welcome to MrExcel!!

You will need to use PasteSpecial when copying the filtered (visible) range which cannot be done on a one liner like you have. See how this goes:

Option Explicit
Sub MoveData()

    Sheet6.[Y1:Y1901].AutoFilter 1, "PASS"
    Sheet6.[A1:Y1901].SpecialCells(xlCellTypeVisible).Copy
    Sheet2.[A1].PasteSpecial xlPasteFormulas
    Sheet6.[A1].AutoFilter

End Sub

Regards,

Robert

Thank you Robert!!! This was so helpful.

 

[ohuffman7]

This...

Sub MoveData()
    Sheet6.[Y1:Y1901].AutoFilter 1, "PASS"
    Sheet6.[A1:Y1901].Copy
    Sheet2.[A1].PasteSpecial xlPasteAll
    Sheet6.[A1].AutoFilter
End Sub

Thank you so much!!

 

[igold]

You're welcome. We were happy to help.