1337 Excel [Help using VBA]
Posted: 29 Mar 2012, 12:48
G'day
I have been asked to break up an Excel workbook containing a number of worksheets such that each worksheet is transferred to its own workbook.
Now I could do this the usual junior admin. assistant way, create a slew of .xls files, right-click on each worksheet tab of the original workbook, and choose "Move", but I am lazy.
I found the following super advanced method, but don't know the first thing about Visual Basic for Applications.
How would I use the following:
from the VBHaters
Thank you
I have been asked to break up an Excel workbook containing a number of worksheets such that each worksheet is transferred to its own workbook.
Now I could do this the usual junior admin. assistant way, create a slew of .xls files, right-click on each worksheet tab of the original workbook, and choose "Move", but I am lazy.
I found the following super advanced method, but don't know the first thing about Visual Basic for Applications.
How would I use the following:
Code: Select all
Sub splittest()
Dim sht As Worksheet
Dim w As Worksheet
Application.ScreenUpdating = False
Application.DisplayAlerts = False
For Each sht In Worksheets
Set neww = Workbooks.Add
sht.Copy neww.Worksheets(1)
With neww
For Each w In Worksheets
If w.Name <> sht.Name Then
w.Delete
End If
Next w
End With
neww.SaveAs sht.Parent.Path & "\" & sht.Name
neww.Close
Next sht
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub
Thank you
