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 "
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