VBA - copy column widths and formats from one WS to new WB

[dlmoore99]

I'm trying to copy rows from one worksheet to a new worksheet while keeping the formatting (column widths etc..) of the active worksheet. I'm getting a "Run-time error '438': Object doesn't support this property type or method". How can I fix this code to copy the active sheet formats to the new sheet. Thank you.

Private Sub cmdCSV_Click()
    Dim lastrow As Long, i As Long, erow As Long
    Dim sheetdate As Date, startdate As Date, enddate As Date
    Dim wb As Workbook
    Dim ws As Worksheet
    
    Set ws = ThisWorkbook.Worksheets("TGT")
    Set wb = Workbooks.Add
        
        ws.Cells.Copy
       [COLOR=#FF0000] With wb.Cells 'this is where the error occurs[/COLOR]
            .PasteSpecial Paste:=xlPasteColumnWidths
            .PasteSpecial Paste:=xlPasteFormats
            Application.CutCopyMode = False
        End With
   
    startdate = Me.DTPicker1.Value
    enddate = Me.DTPicker2.Value
    lastrow = ws.UsedRange.Rows.Count
    For i = 2 To lastrow
        sheetdate = ws.Cells(3, 2).Value
        If sheetdate >= startdate And sheetdate <= enddate Then
        ws.Range(ws.Cells(i, 1), ws.Cells(i, 15)).Copy Destination:=wb.Worksheets("sheet1").Cells(Rows.Count, 1).End(xlUp).Offset(1)
        End If
    Next i
End Sub

 

[dlmoore99]

Finally, that works Fluff. Thank you an million and many kudo's for sticking with me. I'm not even going to ask how to do the same thing, but make is a pdf. Here is the final code in case it can help someone else:

Private Sub cmdCSV_Click()
    Dim lastrow As Long, i As Long, erow As Long
    Dim sheetdate As Date, startdate As Date, enddate As Date
    Dim wb As Workbook
    Dim ws As Worksheet
    
    Set ws = ThisWorkbook.Worksheets("TGT")
    Set wb = Workbooks.Add
        
    ws.Range("A2:O2").Copy wb.Worksheets("sheet1").Cells(Rows.Count, 1).End(xlUp).Offset(1)
    
        ws.Cells.Copy
        With wb.Sheets(1).Cells
            .PasteSpecial Paste:=xlPasteColumnWidths
            .PasteSpecial Paste:=xlPasteFormats
            Application.CutCopyMode = False
        End With
   
    startdate = Me.DTPicker1.Value
    enddate = Me.DTPicker2.Value
    lastrow = ws.UsedRange.Rows.Count
    For i = 3 To lastrow
        sheetdate = ws.Cells(i, 2).Value
        If sheetdate >= startdate And sheetdate <= enddate Then
            ws.Range(ws.Cells(i, 1), ws.Cells(i, 15)).Copy Destination:=wb.Worksheets("sheet1").Cells(Rows.Count, 1).End(xlUp).Offset(1)
        End If
   Next i
    wb.SaveAs ThisWorkbook.Path & "\Target Log " & Format(enddate, "mm-dd-yyyy") & ".xlsm", 52
End Sub

Fluff, Thank you again.

 

[Fluff]

Glad to help & thanks for the feedback