Forum Discussion
VBA - Copy only certain filtered columns without header to the last row of another sheet
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 column...
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.
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.
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
Thank you so much ..this works perfectly Subodh_Tiwari_sktneer
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.
