Results 1 to 2 of 2

Split Worksheets to Individual Workbooks but keep the ones with similar names, together

Threaded View

  1. #1
    Registered User
    Join Date
    08-19-2016
    Location
    London
    MS-Off Ver
    2013
    Posts
    1

    Split Worksheets to Individual Workbooks but keep the ones with similar names, together

    Hi
    I have an Excel Macro that splits each tab within the workbook to individual workbooks and saves it to a folder. The original workbook is an extract from a reporting system in xls format.

    For example, Original workbook contains tabs: London, Manchester, Sheffield, Scotland, etc.
    They get split into: London.xlsx, Manchester.xlsx, Sheffield.xlsx, scotland.xlsx, etc (totally 24 workbooks)

    I would like to modify this macro slightly, to do the following:

    In some scenarios, the reporting system produces more than 65,536 records per tab, in such cases, the data automatically transfers to the next tab within the original spreadsheet. i.e, London, London (1), London (2) etc depending on the amount of data. Therefore when I run the macro, I would like to keep these tabs within the one workbook, and continue to the next. Would that be possible?

    Also, my main workbook contains a FrontPage, which I am copying to each and every individual workbook. The macro that I use is as follows:


    Public Sub DE_Split()
    
    Application.DisplayAlerts = False
    
    
    Dim wbFeed As Workbook
    Dim wbFeedFileName As String
    Dim iRegion As Integer
    Dim sRegion As String
    Dim iSheets As Integer
    Dim sPath As String
    Dim sMessage As String
    
    'Sets the original workbook as an object which is referred to throughout the VBA
    Workbooks.Open Filename:="C:\Users\xxxx11\Desktop\Reg\CopyFrom\Regional Reports.xls"
    Set wbFeed = Application.Workbooks("Regional Reports.xls")
    
     iRegion = 3 'Sets the first Region to work on. The first Region will always start on the third sheet, as the first two sheets are cover sheets
     iSheets = (ActiveWorkbook.Worksheets.Count) ' count the total number of sheets (Regions). This gives the end point for the loop
     sPath = "C:\Users\xxxx11\Desktop\Reg\CopyFrom" 'Sets the filepath of the original file
     wbFeedFileName = Left(wbFeed.Name, (InStrRev(wbFeed.Name, ".", -1, vbTextCompare) - 1)) 'Sets the name of the original workbook
    
    Do While iRegion <= iSheets
    
    sRegion = Sheets(iRegion).Name
    
        Sheets(Array("FrontPage", sRegion)).Select
        Sheets(Array("FrontPage", sRegion)).Copy
        ActiveWorkbook.SaveAs Filename:=sPath & "\Pre Publishing Holding Folder\" & wbFeedFileName & "_" & sRegion & ".xlsx", _
            FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False, ConflictResolution:=Excel.XlSaveConflictResolution.xlLocalSessionChanges
        ActiveWorkbook.Close
    
    sMessage = sMessage & wbFeedFileName & "_" & sRegion & ".xlsx" & vbCrLf
    'MsgBox (sMessage)
    
        
    iRegion = iRegion + 1
    
    Loop
    
    End Sub

    Thanks
    Last edited by Delan; 08-21-2016 at 10:23 AM.

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Split worksheets into individual workbooks
    By trisoldee in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 12-05-2014, 06:32 PM
  2. How to split by filtered first column to create individual workbooks
    By itgeltugs in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 07-22-2014, 12:02 AM
  3. Replies: 2
    Last Post: 07-01-2014, 02:11 PM
  4. Split one workbook sheet2 and sheet3 into individual workbooks
    By sicr123 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-11-2011, 03:10 AM
  5. Replies: 1
    Last Post: 11-20-2005, 07:50 PM
  6. Replies: 0
    Last Post: 09-19-2005, 03:05 PM
  7. [SOLVED] Import data from two workbooks with similar names
    By John in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 09-06-2005, 09:05 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