+ Reply to Thread
Results 1 to 22 of 22

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

Hybrid View

AliGW VBA to Split Workbook: No... 03-02-2019, 05:40 AM
bakerman2 Re: VBA to Split Workbook: No... 03-02-2019, 05:49 AM
AliGW Re: VBA to Split Workbook: No... 03-02-2019, 05:57 AM
AliGW Re: VBA to Split Workbook: No... 03-02-2019, 05:53 AM
bakerman2 Re: VBA to Split Workbook: No... 03-02-2019, 06:01 AM
bakerman2 Re: VBA to Split Workbook: No... 03-02-2019, 05:57 AM
AliGW Re: VBA to Split Workbook: No... 03-02-2019, 06:08 AM
bakerman2 Re: VBA to Split Workbook: No... 03-02-2019, 06:43 AM
AliGW Re: VBA to Split Workbook: No... 03-02-2019, 08:13 AM
AliGW Re: VBA to Split Workbook: No... 03-02-2019, 10:01 AM
AliGW Re: VBA to Split Workbook: No... 03-02-2019, 09:46 AM
AliGW Re: VBA to Split Workbook: No... 03-02-2019, 10:29 AM
AliGW Re: VBA to Split Workbook: No... 03-02-2019, 02:27 PM
AliGW Re: VBA to Split Workbook: No... 03-02-2019, 01:28 PM
bakerman2 Re: VBA to Split Workbook: No... 03-03-2019, 03:17 AM
AliGW Re: VBA to Split Workbook: No... 03-03-2019, 04:50 AM
AliGW Re: VBA to Split Workbook: No... 03-03-2019, 04:43 AM
bakerman2 Re: VBA to Split Workbook: No... 03-03-2019, 05:25 AM
bakerman2 Re: VBA to Split Workbook: No... 03-03-2019, 05:28 AM
AliGW Re: VBA to Split Workbook: No... 03-03-2019, 05:30 AM
AliGW Re: VBA to Split Workbook: No... 03-03-2019, 05:29 AM
AliGW Re: VBA to Split Workbook: No... 03-03-2019, 05:49 AM
  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,486

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

    I am making progress.

    With the code below I can produce one or more sheets without sheets being deleted unnecessarily or extraneous sheets being left in the workbook. The only problem remaining is that everything below row 1 is copied from the first sheet in the user form list, not the sheet selected. Can anyone spot why the code is not looping through the sheets?

    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 = False
                With Sheets(Sheets.Count)
                'With Sheets(LstPrint.List(x))
                    .Unprotect
                    .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
                    .Protect
                End With
                With ActiveWorkbook
                    .Sheets(Sheets.Count).Protect
                    .Sheets(Sheets.Count).Name = LstPrint.List(x)
                    If .HasVBProject Then
                        FileFormatNum = 52
                    Else
                        FileFormatNum = 51
                    End If
                    .SaveAs FolderName & "\" & LstPrint.List(x), FileFormatNum
                    .Close False
                End With
                Application.DisplayAlerts = False
                Worksheets(LstPrint.List(x) & " (2)").Delete
                Application.DisplayAlerts = True
            End If
        Next
        'Application.DisplayAlerts = False
        'Sheets(Sheets.Count).Delete
        'Application.DisplayAlerts = True
        Application.ScreenUpdating = True
        Unload Me
        Application.Goto Sheets("1. Staff List & Subjects").Range("A1")
        Call Shell("explorer.exe " & FolderName, vbNormalFocus)
    End Sub
    Last edited by AliGW; 03-02-2019 at 01:33 PM.
    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.

+ Reply to Thread

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