Copy Worksheet from .xlsx Workbook to .xls Workbook

[mackc557]

I know that a normal worksheet.copy from a .xlsx workbook to a .xls workbook causes an error. Is there anyway to convert the sheet as it's being copied or crop the sheet (I only need about $A$1:$J$50)? I know I can create a new sheet, rename it, paste the data, and lock it; however I'd like to see if the conversion is possible.

 

[rlv01]

Not when you copy. But you can do it when you save.

Sub TestCopy()
    Dim WB As Workbook
    Dim WS As Worksheet
    Dim FilePath As String
    
    Set WS = ActiveSheet
    
    FilePath = CurDir & "\Test.xls"
    
    WS.Copy
    Set WB = ActiveWorkbook
    Debug.Print WB.FileFormat
    
    Application.DisplayAlerts = False
    WB.SaveAs Filename:=FilePath, FileFormat:=xlExcel8
    Application.DisplayAlerts = True
    
    Debug.Print WB.FileFormat
    WB.Close False
End Sub

 

[mackc557]

Not when you copy. But you can do it when you save.

Sub TestCopy()
    Dim WB As Workbook
    Dim WS As Worksheet
    Dim FilePath As String
   
    Set WS = ActiveSheet
   
    FilePath = CurDir & "\Test.xls"
   
    WS.Copy
    Set WB = ActiveWorkbook
    Debug.Print WB.FileFormat
   
    Application.DisplayAlerts = False
    WB.SaveAs Filename:=FilePath, FileFormat:=xlExcel8
    Application.DisplayAlerts = True
   
    Debug.Print WB.FileFormat
    WB.Close False
End Sub

Thank you for this. I did go about just copying the range to a new sheet.

The issue was I had some workbooks already saved in the .xls format, but I was trying to programmatically copy a worksheet from a workbook already saved in .xlsx format.

Final solution; just copy the range to a new sheet.