Dec 05 2019 03:19 AM
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
Dec 05 2019 04:10 AM
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.
Dec 05 2019 04:29 AM
Thank you so much ..this works perfectly @Subodh_Tiwari_sktneer
Dec 05 2019 04:54 AM
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.
Sep 09 2020 09:53 AM
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.
Sep 09 2020 12:03 PM
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