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

Question

Hi All,&nbsp;Could somebody please help.&nbsp;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&nbsp;&nbsp;I have attached the worksheet for more clarity.&nbsp;Many thanks in advance&nbsp;&nbsp;

subodh_tiwari_sktneer · Answer

nathsm&nbsp;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" &amp; lr).SpecialCells(xlCellTypeVisible).Cells.Count &gt; 1 Then
        wsData.Range("F2:H" &amp; lr).SpecialCells(xlCellTypeVisible).Copy wsDest.Range("A" &amp; Rows.Count).End(3)(2)
        wsDest.UsedRange.Borders.ColorIndex = xlNone
        wsDest.Select
    End If
    .AutoFilter field:=8
End With
Application.ScreenUpdating = True
End Sub&nbsp;Click the button called "Copy Filtered Data" on Sheet1 in the attached to run the code.&nbsp;&nbsp;

nathsm · Answer

Thank you so much ..this works perfectly Subodh_Tiwari_sktneer&nbsp;

subodh_tiwari_sktneer · Answer

nathsm&nbsp;You're welcome! Glad it worked as desired.&nbsp;Please take a minute to accept the post with proposed solution as a Best Response/Answer to mark your question as Solved.

carl_stephens · Answer

Subodh_Tiwari_sktneer&nbsp;&nbsp;Hello Subodh,&nbsp;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?&nbsp;Thank you for your time.

subodh_tiwari_sktneer · Answer

Carl_Stephens&nbsp;Ideally you should have opened a New Question.But to give you an idea, you may try something like this...&nbsp;SourceRng.Copy  'Copy the Source Range
DestRng.PasteSpecial xlPasteValues  'Paste copied range as Values

Forum Discussion

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

5 Replies

Resources