How do I edit my VBA to export a table exactly as it appears, but with values, into a new workbook?

[meb229]

I'm relatively new to VBA and while I can get a new workbook opened, I can't get the table to paste. I'm looking for it to paste exactly how it appears (starting in cell A1) and just can't get it there. How can I fix it?

Sub exporttable()    

Dim ws As Worksheet
      
Set ws = ActiveSheet With Workbooks("Test.xlsb")    

ws.ListObjects(1).Range.Copy    

Workbooks.Add    

ws.ListObjects(1).Range.Paste 

ActiveWorkbook.SaveAs "Test - " & Format(Date, "mmm-dd-yyyy") & ".xlsx"    

ActiveWorkbook.Sheets(1).Name = Format(Date, "mmm-dd-yyyy") 

End With 
End Sub

 

[rlv01]

Sub exporttable()
    Dim WB As Workbook
    Dim ws As Worksheet
    Dim MyTable As ListObject
    
    Set ws = ActiveSheet 
    Set MyTable = ws.ListObjects(1)
    
    Set WB = Workbooks.Add
    MyTable.Range.Copy WB.Worksheets(1).Range("A1")
    WB.Sheets(1).Name = Format(Date, "mmm-dd-yyyy")
    WB.SaveAs "Test - " & Format(Date, "mmm-dd-yyyy") & ".xlsx"
    WB.Close False
End Sub

 

[meb229]

Sub exporttable()
    Dim WB As Workbook
    Dim ws As Worksheet
    Dim MyTable As ListObject
   
    Set ws = ActiveSheet
    Set MyTable = ws.ListObjects(1)
   
    Set WB = Workbooks.Add
    MyTable.Range.Copy WB.Worksheets(1).Range("A1")
    WB.Sheets(1).Name = Format(Date, "mmm-dd-yyyy")
    WB.SaveAs "Test - " & Format(Date, "mmm-dd-yyyy") & ".xlsx"
    WB.Close False
End Sub

Thank you! How the heck do I get it pasted with the same column widths and with values?

 

[meb229]

Okay I'm actually almost there. I figured out the values and the formatting. How would I do the following:

1) Make the row height for every row except for row 1 "15" and
2) freeze panes at cell C3?

Sub exporttable()
    Dim WB As Workbook
    Dim ws As Worksheet
    Dim MyTable As ListObject
    
    Set ws = ActiveSheet
    Set MyTable = ws.ListObjects(1)
    
    Set WB = Workbooks.Add
    MyTable.Range.Copy
    With WB.Worksheets(1).Range("A1")
    .PasteSpecial xlPasteColumnWidths
    .PasteSpecial xlPasteValues, , False, False
    .PasteSpecial xlPasteFormats, , False, False
    .Cells(1).Select
    Application.CutCopyMode = False
    WB.Sheets(1).Name = Format(Date, "mmm-dd-yyyy")
    WB.SaveAs "Test - " & Format(Date, "mmm-dd-yyyy") & ".xlsx"

End With
End Sub

 

[rlv01]

Okay I'm actually almost there. I figured out the values and the formatting. How would I do the following:

1) Make the row height for every row except for row 1 "15" and
2) freeze panes at cell C3?

    ws.Range("C2").Select
    ActiveWindow.FreezePanes = True
    
    With MyTable
       .Range.Offset(1).Resize(.Range.Rows.Count - 1).RowHeight = 15
    End With

You can use the macro recorder for stuff like that. Record a macro to get the syntax, then inspect the macro code and modify your subroutine accordingly.

 

[meb229]

    ws.Range("C2").Select
    ActiveWindow.FreezePanes = True
   
    With MyTable
       .Range.Offset(1).Resize(.Range.Rows.Count - 1).RowHeight = 15
    End With

You can use the macro recorder for stuff like that. Record a macro to get the syntax, then inspect the macro code and modify your subroutine accordingly.

Perfect, thank you! Yep I managed to get everything figured out on my own; I just kept playing around and adding things and it all seems to have worked out. Thank you so much for your help!!