Copying sheets between Excel versions

[Shaft120]

Hi

This is probably an old one that somebody can tell me, but apologies as I've been unable to search the solution on past posts.

My macro is copying some worksheets from a source workbook which is .xlsm (and needs to remain so) and copying them into to a new workbook I am creating through the code.

However, I need the new workbook to be saved down as .xls 97 - 2003, as it is used by another system that doesn't recognise anything else.

I had no problems running this on my system which is 2007. But one of my users was testing it for me (also using 2007) and she is getting an error along the lines of "the source data has more rows than the new workbook.. etc."

Short of saving it down as .xlsx initially and then re-opening it to save down again as .xls and having to delete the .xlsx file, is there another more simple way I'm missing? And why does this seem to be inconsistent across systems on the same version of Excel?

    SSPath = WkbAll.Path
    WSIName = SSPath & "\Client Data " & FndNo & " " & CStr(Format(Mdt, "dd-mm-yy")) & ".xls"
    Workbooks.Add.SaveAs (WSIName), FileFormat:=56
    Set WkbWSI = ActiveWorkbook
 
 
    On Error Resume Next
 
    Set shtTest = WkbAll.Sheets("B")
    If Err = 0 Then
        WkbAll.Sheets("B").Copy After:=WkbWSI.Sheets(WkbWSI.Sheets.Count)
        WkbWSI.Sheets("B").Name = "Valuation B"
    Else
        WkbWSI.Sheets.Add
        ActiveSheet.Name = "Valuation B"
    End If
 
    Err.Clear
 
    On Error Goto 0

All clues greatly appreciated..!

 

[MrKowz]

Edit - disregard

 

[Shaft120]

Well, yes, but you can't save a bigger sheet as 97-2003 format anyway.

True, but it will remain as the full size until it is closed and re-opened, when it will be trimmed to 65k.

The problem wasn't that I had data to put in the extra rows, only that I couldn't copy the sheet to the new workbook.

I suppose I could have set a range around the actual data on the donor sheet and copied just that across, but it just felt like a work around.

 

[RoryA]

I suppose I could have set a range around the actual data on the donor sheet and copied just that across, but it just felt like a work around.

That was exactly what I was suggesting.