VBA - Copy only certain filtered columns without header to the last row of another sheet

[nathsm]

Hi All,

Could somebody please help.

I am trying to find a VBA code that would allow to do the below:

- Filter column H to keep only Completed lines in Sheet1

- Copy filtered rows for columns F to H without Header

- Paste data to last row of Sheet2 

I have attached the worksheet for more clarity.

Many thanks in advance 

[#2]

@nathsm 

Please give this a try...

Sub CoypFilteredData()
Dim wsData      As Worksheet
Dim wsDest      As Worksheet
Dim lr          As Long

Application.ScreenUpdating = False

Set wsData = Worksheets("Sheet1")
Set wsDest = Worksheets("Sheet2")

lr = wsData.Cells(Rows.Count, "F").End(xlUp).Row

If wsData.FilterMode Then wsData.ShowAllData

With wsData.Rows(1)
    .AutoFilter field:=8, Criteria1:="Completed"
    If wsData.Range("H1:H" & lr).SpecialCells(xlCellTypeVisible).Cells.Count > 1 Then
        wsData.Range("F2:H" & lr).SpecialCells(xlCellTypeVisible).Copy wsDest.Range("A" & Rows.Count).End(3)(2)
        wsDest.UsedRange.Borders.ColorIndex = xlNone
        wsDest.Select
    End If
    .AutoFilter field:=8
End With
Application.ScreenUpdating = True
End Sub

Click the button called "Copy Filtered Data" on Sheet1 in the attached to run the code.

[#3]

Thank you so much ..this works perfectly @Subodh_Tiwari_sktneer 

[#4]

@nathsm 

You're welcome! Glad it worked as desired.

Please take a minute to accept the post with proposed solution as a Best Response/Answer to mark your question as Solved.

[#5]

@Subodh_Tiwari_sktneer 

Hello Subodh,

Thank you for this code, it worked for me great. I have one question on this, what would the code be if I wanted to copy the selected cells and paste special values instead of just paste?

Thank you for your time.

[#6]

@Carl_Stephens 

Ideally you should have opened a New Question.

But to give you an idea, you may try something like this...

SourceRng.Copy  'Copy the Source Range
DestRng.PasteSpecial xlPasteValues  'Paste copied range as Values