correct macro VBA code
- Statusi: Closed
- Çmimi: $10
- Kandidaturat e marra: 4
- Fituesi: soery4
Përmbledhja e konkursit
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
Aftësi të rekomanduara
Vlerёsimi i punёdhёnёsit
“Awesome freelancer! Good quick work solved my problem !”
revamp365, United States.
Punimet mё tё mira nga ky konkurs
-
UKConsultant United Kingdom
-
ifazzz Bangladesh
Këndi publik i sqarimeve
Si të fillosh me konkurset
-
Posto konkursin Shpejt dhe thjeshtë
-
Merr shumë propozime Nga e gjithë bota
-
Zgjidh kandidaturën më të mirë Shkarko dokumentet - E thjeshtë!