VBA to copy filtered data to a different sheet

[alisoncleverly]

Hi everyone,

Can you please suggest how I can copy filtered data from one sheet to another?

I have 2 sheets: Opportunity and Report. In sheet Opportunity, I'd like to filter for non-empty cells in Range X:AE where Column K is also "Opps". Then for those filtered rows, I'd like to copy data from range A:K, X:AE, U:W to sheet Report (with that specific column order)

I googled and found quite many threads and spent the last 3 hours trying out those but nothing has worked so far. Can anyone please advise? Thank you!

 

[PritishS]

Hi,

Assuming you have done the filter. After filter, you can use this code to copy filtered data from one sheet to another sheet.
You can modify ranges and sheet name as per your requirement.

Sub Copy_Data_alisoncleverly()

Dim rngDB As Range, rng As Range
Dim n As Integer
Set rngDB = ThisWorkbook.Worksheets("Opportunity").UsedRange.SpecialCells(xlCellTypeVisible)
n = rngDB.SpecialCells(xlCellTypeLastCell).Row

Sheets("Opportunity").Range("A2:K" & n).Copy
Sheets("Report").Range("A2").PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
Application.CutCopyMode = False

Sheets("Opportunity").Range("X2:AE" & n).Copy
Sheets("Report").Range("X2").PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
Application.CutCopyMode = False

Sheets("Opportunity").Range("U2:W" & n).Copy
Sheets("Report").Range("U2").PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
Application.CutCopyMode = False

End Sub

Regards,
PritishS

 

[mumps]

It would be easier to help if you could use the XL2BB add-in (icon in the menu) to post a screen shot of your data. Do all the cells in X:AE need to contain data?

copy data from range A:K, X:AE, U:W to sheet Report (with that specific column order)

Do you want to paste the ranges beside each other in the Report sheet?