correct macro VBA code

• Status: Closed
• Prize: $10
• Entries Received: 4
• Winner: soery4

Winner

Contest Brief

I have an excel VBA code that takes data files with many records then splits the one large file up into multiple files with (X) amount of records each. Currently, the code makes all the files that are created when this macro is run to be in .xls format. I need the files it creates to be in .csv format.

Please update the code accordingly so when it runs it makes all the files in .csv format

First entry that uploads the working code will be awarded

code_________________________________

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 = 2000 'as your example, just 1000 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 & "\file " & WorkbookCounter
wb.Close

'Increment file counter
WorkbookCounter = WorkbookCounter + 1
Next p

Application.ScreenUpdating = True
Set wb = Nothing
End Sub

Recommended Skills

• Data Processing
• Excel
• Software Architecture
• Visual Basic
• Visual Basic for Apps