0

This macro is pretty close to exactly what I need: How to split spreadsheet into multiple spreadsheets with set number of rows?

It splits a long Excel file into smaller files based on number of rows.

HOWEVER, I need to modify it to save the files as .xls (Excel 97-2003) format.

What do I need to change?

Here's what I'm working with right now:

Sub Test()
  Dim wb As Workbook
  Dim ThisSheet As Worksheet
  Dim NumOfColumns As Integer
  Dim RangeToCopy As Range
  Dim RangeOfHeader As Range        'data (range) of header row
  Dim WorkbookCounter As Integer
  Dim RowsInFile                    'how many rows (incl. header) in new files?

  Application.ScreenUpdating = False

  'Initialize data
  Set ThisSheet = ThisWorkbook.ActiveSheet
  NumOfColumns = ThisSheet.UsedRange.Columns.Count
  WorkbookCounter = 1
  RowsInFile = 100                   'as your example, just 10 rows per file

  'Copy the data of the first row (header)
  Set RangeOfHeader = ThisSheet.Range(ThisSheet.Cells(1, 1), ThisSheet.Cells(1, NumOfColumns))

  For p = 2 To ThisSheet.UsedRange.Rows.Count Step RowsInFile - 1
    Set wb = Workbooks.Add

    'Paste the header row in new file
    RangeOfHeader.Copy wb.Sheets(1).Range("A1")

    'Paste the chunk of rows for this file
    Set RangeToCopy = ThisSheet.Range(ThisSheet.Cells(p, 1), ThisSheet.Cells(p + RowsInFile - 2, NumOfColumns))
    RangeToCopy.Copy wb.Sheets(1).Range("A2")

    'Save the new workbook, and close it
    wb.SaveAs ThisWorkbook.Path & "\splitoutput" & WorkbookCounter
    wb.Close

    'Increment file counter
    WorkbookCounter = WorkbookCounter + 1
  Next p

  Application.ScreenUpdating = True
  Set wb = Nothing
End Sub
BigBen
  • 46,229
  • 7
  • 24
  • 40
JWgoldenb
  • 1
  • The [`Workbook.SaveAs`](https://learn.microsoft.com/en-us/office/vba/api/excel.workbook.saveas) and [`XlFileFormat` enumeration](https://learn.microsoft.com/en-us/office/vba/api/excel.xlfileformat) documentation should give you what you need. – BigBen Mar 26 '19 at 00:38
  • I see where I can get variable for FileFormat in both vaue and number format. I'm having trouble getting them inserted into the macro without breaking it. – JWgoldenb Mar 26 '19 at 02:06
  • Actually, I think that worked. I just changed this: wb.SaveAs ThisWorkbook.Path & "\splitoutput" & WorkbookCounter to this: wb.SaveAs Filename:=ThisWorkbook.Path & "\splitoutput" & WorkbookCounter, _ FileFormat:=xlExcel8 and that did the trick. – JWgoldenb Mar 26 '19 at 02:17

1 Answers1

0

The Workbook.SaveAs and xlFileFormat enumeration documentation are helpful here.

Change 

wb.SaveAs ThisWorkbook.Path & "\splitoutput" & WorkbookCounter

to 

wb.SaveAs FileName:=ThisWorkbook.Path & "\splitoutput" & WorkbookCounter, FileFormat:=xlWorkbookNormal
BigBen
  • 46,229
  • 7
  • 24
  • 40