Need to edge out the competition for your dream job? Train for certifications today.
Dim swb As Workbook, wb As Workbook
Dim sws As Worksheet, dws As Worksheet
Dim lr As Long, i As Long
Dim FilePath As String, FileName As String
Application.ScreenUpdating = False
Set swb = ThisWorkbook 'Source Workbook
Set sws = swb.Sheets("Sheet1") 'Source Sheet
FilePath = swb.Path & "\"
lr = sws.Cells(Rows.Count, 1).End(xlUp).Row
For i = 2 To lr Step 10000
Set wb = Workbooks.Add
Set dws = wb.Sheets(1)
FileName = i & " - " & i + 10000 - 1
dws.Name = FileName
sws.Range("A" & i).Resize(10000, 2).Copy dws.Range("A2")
Application.DisplayAlerts = False
wb.SaveAs FilePath & FileName, 51
Application.ScreenUpdating = True
MsgBox "Task completed!", vbInformation
Open in new window
Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.
Have a better answer? Share it in a comment.
Please enter a first name
Please enter a last name
Must be at least 4 characters long.
Join and Comment
From novice to tech pro — start learning today.
Premium members can enroll in this course at no extra cost.