+ Reply to Thread
Results 1 to 6 of 6

split data based on three columns to multiple sheets and rename sheets based on headers

  1. #1
    Forum Contributor
    Join Date
    04-11-2021
    Location
    Tripoli
    MS-Off Ver
    Excel 2016 ,windows 10 64bit
    Posts
    345

    split data based on three columns to multiple sheets and rename sheets based on headers

    hi
    I have data in sheet MAIN the result should be after it so it should split data based on COL B,C,D and the sheets names should be based on headers when create the new sheets after split them . every time increase data in sheet MAIN then should updating in the created sheets . so the result in sheets IMPORT,EXPORT ,RETURNS
    sorry if it's poor detailes , but I think the result after sheet main will be clear . if it's not clear please inform me
    Attached Files Attached Files

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

    Cool Hi ! Try this !


    According to your attachment this is the EZ advanced filter beginner way but for good enough readers only !

    Once sheets are created with their names according to the MAIN sheet headers
    with just the row #1 filled with only the necessary headers (same as MAIN sheet, no typo allowed)
    then the sheet is updated when it is activated just pasting this event code to the ThisWorkbook module :

    PHP Code: 
    Private Sub Workbook_SheetActivate(ByVal Sh As Object)
            
    Dim V
        With Sheets
    ("MAIN")
            
    Application.Match(Sh.Name, .UsedRange.Rows(1), 0):  If IsError(VThen Exit Sub
            Sh
    .UsedRange.Offset(1).Clear
           
    .[Z2].Formula "=" & .Cells(2V).Address(0) & ">0"
           
    .[A1].CurrentRegion.AdvancedFilter 2, .[Z1:Z2], Sh.[A1].CurrentRegion.Rows(1)
           .[
    Z2].Clear
        End With
    End Sub 
    ► Do you like it ? ► So thanks to click on bottom left star icon « Add Reputation » !

  3. #3
    Forum Expert nankw83's Avatar
    Join Date
    08-31-2015
    Location
    Kuwait
    MS-Off Ver
    365
    Posts
    1,709

    Re: split data based on three columns to multiple sheets and rename sheets based on heade

    Hi KalilMe,

    Try below code, if the sheet doesn't exist, the code will create one otherwise it will just update it based on any change you make in columns E, F or G in "MAIN" sheet. Right click the "MAIN" sheet tab then click on View Code then insert the below code
    Please Login or Register  to view this content.
    If I was able to help, you can thank me by clicking the * Add Reputation under my user name

  4. #4
    Registered User
    Join Date
    05-12-2021
    Location
    Europe
    MS-Off Ver
    365
    Posts
    45

    Re: split data based on three columns to multiple sheets and rename sheets based on heade

    Based on what i understood , this should work:
    Please Login or Register  to view this content.
    I also attached your file with the macro
    Attached Files Attached Files
    "Good Morning, Good Evening and Good Night"

  5. #5
    Forum Contributor
    Join Date
    04-11-2021
    Location
    Tripoli
    MS-Off Ver
    Excel 2016 ,windows 10 64bit
    Posts
    345

    Re: split data based on three columns to multiple sheets and rename sheets based on heade

    @Marc L & nankw83 your codes works perfectly
    thanks so much

  6. #6
    Forum Contributor
    Join Date
    04-11-2021
    Location
    Tripoli
    MS-Off Ver
    Excel 2016 ,windows 10 64bit
    Posts
    345

    Re: split data based on three columns to multiple sheets and rename sheets based on heade

    @iustin thanks for your trying but some sheets RETURNS ,EXPORT have some data are missed

+ 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] split data across multiple sheets based on month
    By Alaa-A in forum Excel Programming / VBA / Macros
    Replies: 20
    Last Post: 07-10-2021, 06:54 AM
  2. [SOLVED] split data from sheet to three sheets for many names based on two columns
    By leap out in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 02-14-2021, 10:02 AM
  3. [SOLVED] Split one excel sheet into multiple sheets based on column value but only selected columns
    By nr6281 in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 10-01-2019, 05:25 AM
  4. [SOLVED] Need VBA code to match the value and split the data in the multiple sheets based on column
    By Giri.hb in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 09-04-2016, 09:06 AM
  5. Need Macro code to split data split in 7 sheets based on variable rows in column A
    By Alija_21 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 12-31-2015, 08:03 AM
  6. [SOLVED] Split excel data into multiple sheets based on Employee ID
    By Preeti1309 in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 04-21-2014, 02:33 AM
  7. [SOLVED] How to Rename Multiple sheets using right function based on a cell
    By choleb in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 10-09-2013, 01:40 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