Copy filtered data into another workbook.

Hello everyone,

I'm trying to create a macro to extract filtered data into a new workbook.

I've searched on the forum and found this similar post : Filter, copy filtered data into another sheet, create a file of that sheet , but I failed to adapt it to my needs.

example.xlsx is my example file for my actual needs. Basically extract the data display to the screen after applying a filter on the sheet "repository".
The sheet "jeudi ..." is an example of what result I would like to have on a new file with the same name.

example v4.xls is the macro I found on the other topics that doing something very close. But as a beginner in macro, I'm really lost in the adaptation.

A little help would be great and truly appreciate !!!


Thanks by advance folks.

Hi,

I transformed your list into a table.

Try this code:

Option Explicit

Sub cpyFilteredData()
    Dim wb As Workbook
    Dim ws, ws1 As Worksheet
    Dim lo As ListObject
    Dim sRng, dRng As Range
    
    Set wb = ActiveWorkbook
    Set ws = wb.Worksheets("Repository")
    Set lo = ws.ListObjects("Table1")
    
    ' Adds a new sheet named "Jeudi"
    Worksheets.Add.Name = "Jeudi"
    Set ws1 = wb.ActiveSheet
    
    ' Copy data filtered
    Set sRng = lo.Range.SpecialCells(xlCellTypeVisible)
    Set dRng = ws1.[B2]
    sRng.Copy dRng
    
    ' Move sheet to new workbook
    ws1.Move
    
    ' Saves the new workbook
    With ActiveWorkbook
        .SaveAs Filename:="C:\NewFile.xlsx"
        .Close
    End With
End Sub

Regards,
Elio Fernandes

Thanks Elio,

How do I initiate the Table1 ?

I do have a debug error : "Subscript out of range" on the line "Set lo = ws.ListObjects("Table1")"

Any idea.


NB : Sorry for the delay for answering.

cre