correct macro VBA code
- Estado: Closed
- Premio: $10
- Propuestas recibidas: 4
- Ganador: soery4
Resumen del concurso
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
Habilidades recomendadas
Comentarios del empleador
“Awesome freelancer! Good quick work solved my problem !”
revamp365, United States.
Principales propuestas de este concurso
-
UKConsultant United Kingdom
-
ifazzz Bangladesh
Tablero de aclaración pública
Cómo comenzar con los concursos
-
Publica tu concurso Fácil y rápido
-
Consigue toneladas de propuestas De todo el mundo
-
Elige la mejor propuesta ¡Descarga fácilmente los archivos!