+ Reply to Thread
Results 1 to 5 of 5

Need vba to split data into multiple worksheets based on column of P & save wb with FY 16

Hybrid View

johnmacpro Need vba to split data into... 04-29-2016, 11:10 AM
AlphaFrog Re: Need vba to split data... 04-29-2016, 12:20 PM
johnmacpro Re: Need vba to split data... 05-01-2016, 02:56 PM
AlphaFrog Re: Need vba to split data... 05-01-2016, 03:54 PM
johnmacpro Re: Need vba to split data... 05-02-2016, 05:47 AM
  1. #1
    Forum Contributor
    Join Date
    02-28-2016
    Location
    australia
    MS-Off Ver
    2019
    Posts
    194

    Need vba to split data into multiple worksheets based on column of P & save wb with FY 16

    Dear experts

    I have sample data in which i require on column P:P values get split as new workbook in target folder as xlsb.

    like values get unique TE001,TE002,TE003...etc. i want split every individual work with name of sheet(FY 16) and workbook save FY 16 TE001 Rpt,FY 16 TE002 Rpt....etc

    find the attachment
    Attached Files Attached Files

  2. #2
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,659

    Re: Need vba to split data into multiple worksheets based on column of P & save wb with FY

    Sub PUDO_Split()
        Dim rngUniques As Range, cell As Range
        Application.ScreenUpdating = False
        If ActiveSheet.FilterMode Then ActiveSheet.ShowAllData
        With ActiveSheet.Range("A1").CurrentRegion
            .Columns("P").AdvancedFilter xlFilterInPlace, Unique:=True
            Set rngUniques = Range("P2:P" & .Rows.Count).SpecialCells(xlCellTypeVisible)
            If ActiveSheet.FilterMode Then ActiveSheet.ShowAllData
            For Each cell In rngUniques
                .AutoFilter Field:=16, Criteria1:=cell.Value
                Workbooks.Add xlWBATWorksheet
                Sheets(1).Name = "FY " & Format(Date, "yy")
                .Copy Range("A1")
                ActiveWorkbook.SaveAs ThisWorkbook.Path & "\FY" & Format(Date, " yy ") & cell.Value & " Rpt.xlsb", FileFormat:=50
                ActiveWorkbook.Close False
            Next cell
            .Parent.AutoFilterMode = False
        End With
        Application.ScreenUpdating = True
        MsgBox rngUniques.Count & " files saved. ", , "Split Reports Complete"
    End Sub
    Surround your VBA code with CODE tags e.g.;
    [CODE]your VBA code here[/CODE]
    The # button in the forum editor will apply CODE tags around your selected text.

  3. #3
    Forum Contributor
    Join Date
    02-28-2016
    Location
    australia
    MS-Off Ver
    2019
    Posts
    194

    Re: Need vba to split data into multiple worksheets based on column of P & save wb with FY

    Hi sir I getting error @path i want save files to this path C:\Users\Admin\Desktop\VBA\F\New folder
    Attached Images Attached Images

  4. #4
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,659

    Re: Need vba to split data into multiple worksheets based on column of P & save wb with FY

    ActiveWorkbook.SaveAs "C:\Users\Admin\Desktop\VBA\F\New folder\FY" & Format(Date, " yy ") & cell.Value & " Rpt.xlsb", FileFormat:=50

  5. #5
    Forum Contributor
    Join Date
    02-28-2016
    Location
    australia
    MS-Off Ver
    2019
    Posts
    194

    Re: Need vba to split data into multiple worksheets based on column of P & save wb with FY

    Thanks Expert Amazing Piece of work!!Solved

+ 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: 0
    Last Post: 09-03-2014, 04:38 PM
  2. Replies: 9
    Last Post: 08-25-2014, 04:52 PM
  3. [SOLVED] How to split worksheet into multiple worksheets based on column content
    By Laurelmzitney in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 06-03-2014, 10:57 AM
  4. Split data into multiple worksheets based upon a column in master worksheet
    By cep3927 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-07-2014, 10:38 AM
  5. Split Date into Multiple Worksheets based on Data
    By keithm_007 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-12-2014, 12:00 PM
  6. How to split worksheet into multiple worksheets based on column content
    By jjexcels in forum Excel Programming / VBA / Macros
    Replies: 24
    Last Post: 11-05-2013, 11:42 AM
  7. [SOLVED] How to split worksheet into multiple worksheets based on column content
    By johnboytweed in forum Excel General
    Replies: 12
    Last Post: 10-16-2013, 12:01 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