Results 1 to 22 of 22

VBA to Split Workbook: No Longer Working with Multi-Selection on User Form

Threaded View

  1. #1
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (both in England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2503 (Windows 11 Home 24H2 64-bit)
    Posts
    90,480

    VBA to Split Workbook: No Longer Working with Multi-Selection on User Form

    Having solved the values issue in my previous thread, I now have a new problem.

    Previous thread: https://www.excelforum.com/excel-pro...to-values.html

    The code I have used to work as I wished: I was able to CTRL select one or more sheets in the user form at the beginning and these would be copied and split out as separate files. Now, if I multi-select, it only works for the first sheet, after which I get copies of the last tab in the workbook instead of the other sheets I've selected. The subsequent sheets are copied, but when it comes to splitting out, it doesn't happen.


    Only this line of code executes correctly on the subsequent sheets:

    .Range("H1").Value = LstPrint.List(x)
    What do I need to tweak to get it to loop back to the splitting out section for each sheet? I am not able to upload a workbook at the moment as I am at work.

    My code is here:

    Private Sub UserForm_Initialize()
    Dim sh As Worksheet
    With LstPrint
        .Clear
        For Each sh In Worksheets
            If sh.Name <> "1. Staff List & Subjects" And sh.Name <> "2. Staff Allocation Checklist" And sh.Name <> "3. Roles & Responsibilities" And sh.Name <> "Proforma" And sh.Name <> "Rooms" Then .AddItem sh.Name
        Next
    End With
    End Sub
    
    Private Sub CmdPrint_Click()
        Dim DateString As String
        Dim FolderName As String
        Dim x As Long
        Dim FileFormatNum As Long
    
        Application.ScreenUpdating = False
        DateString = Format(Now, "dd mmmm yyyy")
        FolderName = "C:\Users\Alison\OneDrive - Royal Hospital School\Documents\Timetable WIP\2019-20\Staffing Grids for HoDs" & " " & DateString
        For x = 0 To LstPrint.ListCount - 1
            If LstPrint.Selected(x) = True Then
                Application.CopyObjectsWithCells = False
                With Sheets(LstPrint.List(x))
                    If .ProtectContents = True Then
                        .Unprotect
                        .Copy , Sheets(Sheets.Count)
                        .Protect
                    Else
                        .Copy Sheets(Sheets.Count)
                    End If
                End With
                Application.CopyObjectsWithCells = True
                With Sheets(Sheets.Count)
                    .Range("H1").Value = LstPrint.List(x)
                    .Range("A1").Value = .Range("A1").Value
                    .Range("B2:U4").Value = .Range("B2:U4").Value
                    .Range("E94:U104").Value = .Range("E94:U104").Value
                    .Copy
                End With
                With ActiveWorkbook
                    .Sheets(1).Protect
                    .Sheets(1).Name = LstPrint.List(x)
                    If .HasVBProject Then
                        FileFormatNum = 52
                    Else
                        FileFormatNum = 51
                    End If
                    .SaveAs FolderName & "\" & LstPrint.List(x), FileFormatNum
                    .Close False
                End With
            End If
        Next
        Application.DisplayAlerts = False
        Sheets(Sheets.Count).Delete
        Application.DisplayAlerts = True
        Call Shell("explorer.exe " & FolderName, vbNormalFocus)
        Application.ScreenUpdating = True
        Unload Me
        Application.Goto Sheets("1. Staff List & Subjects").Range("A1")
    End Sub
    Last edited by AliGW; 03-02-2019 at 05:45 AM.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help. It's a universal courtesy.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    NB:
    as a Moderator, I never accept friendship requests.
    Forum Rules (updated August 2023): please read them here.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] VBA to Split Workbook: No Longer Converting Formula Results to Values
    By AliGW in forum Excel Programming / VBA / Macros
    Replies: 20
    Last Post: 03-02-2019, 05:46 AM
  2. [SOLVED] Split macro no longer working
    By PandoraGirl in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 02-07-2019, 05:33 PM
  3. [SOLVED] Multi Column List Box (14) in user form not working
    By Pradip2 in forum Excel Programming / VBA / Macros
    Replies: 21
    Last Post: 02-06-2019, 03:12 AM
  4. How user Form can be accessed by multi user in online?
    By bala04msw in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-20-2018, 09:47 PM
  5. Copied working macro to new workbook, but no longer working
    By twisted31 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-23-2017, 06:17 PM
  6. User added buttons no longer working
    By capt_tony in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 04-11-2013, 11:42 AM
  7. How do I get value of a multi-selection form listbox?
    By foxguy in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 07-15-2010, 03:57 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1