+ Reply to Thread
Results 1 to 3 of 3

Take all sheets beginning with the same 6 digit number in file and save as new file.

  1. #1
    Registered User
    Join Date
    05-16-2019
    Location
    Birmingham
    MS-Off Ver
    2013
    Posts
    1

    Post Take all sheets beginning with the same 6 digit number in file and save as new file.

    Hello,

    I have an excel file containing a large number of sheets, all of which are named a 6 digit reference number, when there is more than one sheet with the same number it will have brackets and a number in it. eg (2).

    For example the sheets will be called something like: 123456 (1), 123456 (2), 123456 (3), 987654 (1), 987654 (2), 987654 (3), 987654 (4). For these sheets I would like to create two files, one called "123456" (containing those sheets) and one called "987654" (containing those sheets).

    Ideally I would like this all to be done in the background and saved as marco-free workbooks, without having to be prompted each time on how to save them.

    For the save location it can be the same location as the file.

    Any help would be greatly appreciated, thanks in advance.

  2. #2
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: Take all sheets beginning with the same 6 digit number in file and save as new file.

    Hi James and welcome to the forum,

    Excel has a newer tool called Power Query that was built for this type of problem. See the link for an explanation:

    https://www.excelguru.ca/blog/2014/1...g-power-query/

    You will need a newer version of excel or download the Add-In.
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  3. #3
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Cool Hi ! Try this !


    Quote Originally Posted by Jamesstock1 View Post
    Any help would be greatly appreciated
    As a starter a one shot code not tested 'cause without any attachment
    so you must be very confident with your Excel / VBA skills to amend it if necessary :

    PHP Code: 
    Sub Demo1()
              
    Dim S$(), N&, T$(), V
            ReDim S
    (1 To Sheets.Count)
            For 
    1 To Sheets.Count:  S(N) = Sheets(N).Name:  Next
        With Application
               
    .DisplayAlerts False
               
    .ScreenUpdating False
            
    For Each V In [{"123456 (","987654 ("}]
                    
    Filter(SVTrue)
                If 
    UBound(T) > -1 Then
                    Sheets
    (T).Copy
                    ActiveWorkbook
    .SaveAs ThisWorkbook.Path & .PathSeparator VxlOpenXMLWorkbook
                    ActiveWorkbook
    .Close
                End 
    If
            
    Next
               
    .ScreenUpdating True
               
    .DisplayAlerts True
        End With
    End Sub 
    Do you like it ? So thanks to click on bottom left star icon « Add Reputation » !

+ 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. Replies: 1
    Last Post: 04-26-2018, 11:44 AM
  2. Vba to save a file to a folder with the file name plus a sequential number
    By Lsxtrkiller in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 08-12-2016, 04:07 PM
  3. Replies: 4
    Last Post: 03-20-2016, 04:27 PM
  4. Macro to save a file with a pre file name + week number from a cell
    By The.Dutchmaster in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 12-06-2012, 03:54 PM
  5. Replies: 20
    Last Post: 09-29-2012, 08:45 PM
  6. Count Number Of lines in text files and append values to beginning of file
    By motoxeryz125 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 04-27-2011, 11:14 PM
  7. Save workbook with multiple sheets as csv file and xlsx file
    By excel_vb in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-25-2010, 02:41 PM

Tags for this Thread

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