Create a new workbook and copy filtered data into it

Question

I have an Excel file, Competition database v1.0. From time to time the data will be updated.

I would like to filter each category and create a new workbook and paste the filtered data into it.

I managed to create a new workbook but it didn't copy the data.

I tried this.

Sub Createnewbook()

    Dim wb As Workbook
    Set wb = Workbooks.Add

    Range("C2", Range("C2").End(xlDown).End(xlToRight)).Copy
    wdApp.Selection.PasteExcelTable True, False, False

End Sub

Then I tried this:

Sub Createnewbook()

    Dim wb As Workbook
    Set wb = Workbooks.Add      

    ' I got error from this line
    Workbooks("Competition Database V1.0.xlsm").Worksheets("Competitor Info").Select

    Worksheets("Competitor Info").Range("C2", Range("C2").End(xlDown).End(xlToRight)).Copy
    wb.Worksheets("Sheet1").Range("A1").PasteExcelTable True, False, False                 

End Sub

Please include which error you get and in which line you get it. Therefore [edit] your question. Additionally you might benefit from reading [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). — Pᴇʜ, Jul 03 '19 at 06:46
`Range("C2").End(xlDown).End(xlToRight)` -> Range from active sheet of active book. Maybe not the one you think about. — Vincent G, Jul 03 '19 at 06:56

score 0 · Answer 1 · answered Aug 09 '19 at 23:41

This may serve as a starting point:

Option Explicit

Public Sub Createnewbook()
    Dim wb As Workbook: Set wb = Workbooks.Add
    With ThisWorkbook.Worksheets("Sheet1")
        .Range("C2", .Range("C2").End(xlDown).End(xlToRight)).Copy
    End With
    wb.Worksheets("Sheet1").Cells(1, 1).PasteSpecial xlPasteAll
    wb.Worksheets("Sheet1").Cells(1, 1).PasteSpecial xlPasteColumnWidths
End Sub

Replace Sheet1 with the actual name of your worksheet (in ThisWorkbook: look at the original workbook and see what the name of the sheet is, in the new workbook: determine what the default name for the first sheet of a new workbook is).

Create a new workbook and copy filtered data into it

1 Answers1