+ Reply to Thread
Results 1 to 4 of 4

Split Data as per common Name in Workbook with many worksheets

  1. #1
    Registered User
    Join Date
    02-02-2018
    Location
    Hyderabad
    MS-Off Ver
    2007
    Posts
    11

    Split Data as per common Name in Workbook with many worksheets

    Hi Friends,
    Need help on splitting of Workbook which has 15 worksheets each worksheet has common name/Common code can i split as per the common name/common Code into new workbook with the common name as file name. I have tried by a macro which is shared below but when I run the code it is split into file name & when checked the split file except the Common name of the file everything is coming & also the heading is not coming. Could you please correct the code or provide with a new code also sharing files for which I have done.
    Thanks in advance.

    Sub SplitWorkbookByBranch()
    Dim ws As Worksheet
    Dim branchNames As Collection
    Dim branchName As Variant
    Dim rng As Range
    Dim newWorkbook As Workbook
    Dim originalWorkbook As Workbook
    Dim branchDict As Object
    Dim cell As Range
    Dim newSheet As Worksheet
    Dim visibleRange As Range

    ' Initialize variables
    Set originalWorkbook = ThisWorkbook
    Set branchNames = New Collection
    Set branchDict = CreateObject("Scripting.Dictionary")

    ' Collect unique branch names from the first worksheet (column C)
    With originalWorkbook.Worksheets(1)
    Set rng = .Range("C2:C" & .Cells(.Rows.Count, "C").End(xlUp).Row)
    On Error Resume Next
    For Each cell In rng
    branchName = cell.Value
    If Not branchDict.exists(branchName) Then
    branchNames.Add branchName
    branchDict.Add branchName, 1
    End If
    Next cell
    On Error GoTo 0
    End With

    ' Loop through each branch name to create a new workbook
    For Each branchName In branchNames
    Set newWorkbook = Workbooks.Add

    ' Loop through each worksheet to copy relevant data for the branch
    For Each ws In originalWorkbook.Worksheets
    ws.Copy After:=newWorkbook.Sheets(newWorkbook.Sheets.Count)
    Set newSheet = newWorkbook.Sheets(newWorkbook.Sheets.Count)

    With newSheet
    ' Apply filter based on branch name in column C
    .UsedRange.AutoFilter Field:=3, Criteria1:=branchName

    ' Check if there are any visible rows after filtering
    On Error Resume Next
    Set visibleRange = .UsedRange.SpecialCells(xlCellTypeVisible)
    On Error GoTo 0

    ' If there is data, copy and assign it directly to the new workbook
    If Not visibleRange Is Nothing Then
    .Cells.Clear ' Clear all data before copying filtered rows
    visibleRange.Copy
    .Range("A1").Resize(visibleRange.Rows.Count, visibleRange.Columns.Count).Value = visibleRange.Value
    Application.CutCopyMode = False
    End If

    ' Remove the filter
    If .AutoFilterMode Then
    .ShowAllData
    End If
    End With
    Next ws

    ' Remove default empty worksheet (Sheet1) from the new workbook
    Application.DisplayAlerts = False
    newWorkbook.Sheets(1).Delete
    Application.DisplayAlerts = True

    ' Save the new workbook with the branch name
    newWorkbook.SaveAs originalWorkbook.Path & "" & branchName & ".xlsx"
    newWorkbook.Close False
    Next branchName

    MsgBox "Workbooks have been created for each branch.", vbInformation
    End Sub
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor MikeVol's Avatar
    Join Date
    12-30-2020
    Location
    Odessa / Ukraine
    MS-Off Ver
    MSO Prof Plus 2021 x64 (En)
    Posts
    567

    Re: Split Data as per common Name in Workbook with many worksheets

    Hi @vsgmpudi. In your code, comment out this line:
    Please Login or Register  to view this content.
    The new code block should look like this with the line commented out:
    Please Login or Register  to view this content.
    I will not attach files, there is nothing to attach. Your code after this modification works as you wished. Good luck.
    NOTE: As the original poster/owner, only you can mark your thread as SOLVED (Thread Tools above Post #1).
    You can say "Thanks" in your thread to everyone who offered to help you.
    You can also reward them by clicking * "Add Reputation" under their username on the left.
    With Regards, MikeVol.

  3. #3
    Forum Expert dangelor's Avatar
    Join Date
    09-06-2011
    Location
    Indiana, USA
    MS-Off Ver
    MS365 V.2406
    Posts
    2,317

    Re: Split Data as per common Name in Workbook with many worksheets

    Possibly...
    Please Login or Register  to view this content.

  4. #4
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 insider Version 2507 Win 11
    Posts
    24,921

    Re: Split Data as per common Name in Workbook with many worksheets

    Administrative Note:

    Welcome to the forum.

    We would very much like to help you with your query, however we recommend that you include code tags around your code.

    Please take a moment to add the tags. Posting code between [code]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/code] tags makes your code much easier to read and copy for testing, and it also maintains VBA formatting.

    Please see Forum Guideline #2 about code tags and adjust accordingly. Click on Edit to open your post, then highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here.
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

+ 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. Split Workbook in New Workbooks and Split Workbook into Worksheets
    By gallen6945 in forum Excel Programming / VBA / Macros
    Replies: 16
    Last Post: 04-30-2024, 10:17 AM
  2. counting common data and further split with pre-determined ratio by country with a ID code
    By shivanand2011 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 05-29-2019, 12:44 PM
  3. Split data into Workbook and Worksheets in it based on column criteria
    By jaredli in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 02-23-2019, 04:28 AM
  4. [SOLVED] Split data into Workbook and Worksheets in it based on column creiteria
    By jaredli in forum Excel Programming / VBA / Macros
    Replies: 13
    Last Post: 12-08-2018, 06:57 AM
  5. Excel 2003 Macro - Split data in two worksheets and place in a individual workbook
    By Isabelle Whyte in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-29-2014, 04:30 AM
  6. Split data by Criteria into separate worksheets on one workbook
    By TrebleC in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-05-2013, 08:39 AM
  7. [SOLVED] Split data into different worksheets in a new workbook.
    By fdotlix in forum Excel Programming / VBA / Macros
    Replies: 13
    Last Post: 07-31-2012, 07:01 PM

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