1337 Excel [Help using VBA]

Any software related topics go in here.
Post Reply
User avatar
StarPhoenix
B.Soc.Sci, M.SocSci [UPCF]
Posts: 17634
Joined: 11 Dec 2003, 02:00
Processor: Core i5 3470
Motherboard: Gigabyte G1 Sniper Z77
Graphics card: nVidia GeForce GTX 1060
Memory: 8Gb DDR3 1600
Location: East London
Contact:

1337 Excel [Help using VBA]

Post by StarPhoenix »

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:

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
:run4hills: from the VBHaters

Thank you
"Humankind cannot bear very much reality." T.S. Elliot
RuadRauFlessa
Registered User
Posts: 20576
Joined: 19 Sep 2003, 02:00
Location: Bloodbank

Re: 1337 Excel [Help using VBA]

Post by RuadRauFlessa »

If you want to be 1337 do it the way you know how... you will get better results by actually just sitting and droning through it than wasting hours trying to make that script work for your situation.
:rock: :rock: :rock: :rock: :rock: :rock: :rock: :rock: :rock: :rock:
Spoiler (show)
Intel Core i7-2600k @ 3.4GHz
Corsair Vengence 2x4GB DDR3 2000MHz
Thermaltake Toughpower 850W
ASUS nVidia GTX560 1GB
CoolerMaster HAF 932
User avatar
Tribble
Registered User
Posts: 88465
Joined: 08 Feb 2007, 02:00
Processor: Intel Core i7-4770K CPU@3.50GHz
Motherboard: ACPI x64-based PC
Graphics card: GeForce GTX 780 Ti
Memory: 16GB
Location: Not here
Contact:

Re: 1337 Excel [Help using VBA]

Post by Tribble »

I am no expert and am thinking in C# and XAML at the moment so not sure if this will help:

Code: Select all

Sub test()
ActiveWorkbook.ActiveSheet.Move
End Sub
Image
User avatar
StarPhoenix
B.Soc.Sci, M.SocSci [UPCF]
Posts: 17634
Joined: 11 Dec 2003, 02:00
Processor: Core i5 3470
Motherboard: Gigabyte G1 Sniper Z77
Graphics card: nVidia GeForce GTX 1060
Memory: 8Gb DDR3 1600
Location: East London
Contact:

Re: 1337 Excel [Help using VBA]

Post by StarPhoenix »

Thank you :-)
"Humankind cannot bear very much reality." T.S. Elliot
User avatar
Tribble
Registered User
Posts: 88465
Joined: 08 Feb 2007, 02:00
Processor: Intel Core i7-4770K CPU@3.50GHz
Motherboard: ACPI x64-based PC
Graphics card: GeForce GTX 780 Ti
Memory: 16GB
Location: Not here
Contact:

Re: 1337 Excel [Help using VBA]

Post by Tribble »

Pleasure - you know how to run it hey?
Image
Post Reply