mercredi 1 avril 2015

Excel Macro to split every 5000 rows with header into multiple new sheets

I was trying to use macro to split my spreadsheet into multiple new files for every 5000 rows and keep the header. I tried this code, but got error message "enter image description here


Code:



Sub Macro1()
Dim inputFile As String, inputWb As Workbook
Dim lastRow As Long, row As Long, n As Long
Dim newCSV As Workbook

inputFile = GetOpenFilename

Set inputWb = Workbooks.Open(inputFile)

With inputWb.Worksheets(1)
lastRow = .Cells(Rows.Count, "A").End(xlUp).row

Set newCSV = Workbooks.Add

n = 0
For row = 2 To lastRow Step 5000
n = n + 1
.Rows(1).EntireRow.Copy newCSV.Worksheets(1).Range("A1")
.Rows(row & ":" & row + 5000 - 1).EntireRow.Copy newCSV.Worksheets(1).Range("A2")

'Save in same folder as input workbook with .xlsx replaced by (n).csv
newCSV.SaveAs Filename:=Replace(inputWb.FullName, ".xlsx", "(" & n & ").csv"), FileFormat:=xlCSV, CreateBackup:=False
Next
End With

newCSV.Close saveChanges:=False
inputWb.Close saveChanges:=False

End Sub


Error highlighted from here according to "Debug":



Set inputWb = Workbooks.Open(inputFile)


Thanks guys!


Aucun commentaire:

Enregistrer un commentaire