Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 5 of 5

Thread: Excel VBA Code to add data to new sheet based upon value in cell

  1. #1
    Join Date
    3rd February 2011
    Posts
    3

    Excel VBA Code to add data to new sheet based upon value in cell

    I need to copy all data from sheet to new sheet based upon value in cell.

    Please see attached excel file.

    I have to generate different sheets is same workbook for values in Column A.

    each new sheet should contain all data from existing sheet & should have name of value in column A.

    Number of similar values in Column A can be increase or decrease.

    Please can you suggest how can excel macro do this ?

    thanks in advance.
    Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    20th February 2006
    Location
    London, UK
    Posts
    2,977

    Re: Excel VBA Code to add data to new sheet based upon value in cell

    Try this:
    VB:
    Sub Macro2() 
         
        Dim rCell As Range, ws As Worksheet 
         
        Application.DisplayAlerts = False 
         
        With Sheets("open pr report") 
            Sheets.Add().Name = "Temp" 
            .Range("A1", .Range("A" & Rows.Count).End(xlUp)).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Sheets("Temp").Range("A1"), Unique:=True 
            For Each rCell In Sheets("Temp").Range("A2", Sheets("Temp").Range("A" & Rows.Count).End(xlUp)) 
                .Range("A1").AutoFilter field:=1, Criteria1:=rCell 
                Set ws = Worksheets.Add(After:=Worksheets(Worksheets.Count)) 
                ws.Name = rCell 
                .AutoFilter.Range.Copy ws.Range("A1") 
            Next rCell 
            Sheets("Temp").Delete 
            .AutoFilterMode = False 
        End With 
         
        Application.DisplayAlerts = True 
         
    End Sub 
    
    

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    3rd February 2011
    Posts
    3

    Re: Excel VBA Code to add data to new sheet based upon value in cell

    Thanks !!!!

    1 more little help..

    Can we add name of Sheet (Like open pr report) in new sheets generated ... like open pr report - FSTC

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    20th February 2006
    Location
    London, UK
    Posts
    2,977

    Re: Excel VBA Code to add data to new sheet based upon value in cell

    Yes, change this line
    VB:
    ws.Name = rCell 
    
    
    to
    VB:
    ws.Name = "open pr report - " & rCell 
    
    

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    3rd February 2011
    Posts
    3

    Re: Excel VBA Code to add data to new sheet based upon value in cell

    Once again thanks stephen, excel macro is just amazing !!!!

    now i am having more than 100 sheets in my workbook.

    all these sheets are created based upon my 5 different reports sheets, out of which one is Open pr report.

    i have 2 issue now:

    1. every time new sheet is created at the end of all sheets, i want to create it right hand side of report sheet from which it has created. this will keep my report & different sheets created from it together.

    2. New sheets formatting / Columns width is different than the sheet from which these are created. can we copy the formatting from summary report to all sheets created from it.

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

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

Possible Answers

  1. Replies: 1
    Last Post: December 14th, 2010, 20:30
  2. Copy data from other sheet based on cell value
    By lau05116 in forum EXCEL HELP
    Replies: 1
    Last Post: November 23rd, 2010, 23:23
  3. Replies: 15
    Last Post: October 18th, 2006, 10:06
  4. Replies: 3
    Last Post: March 24th, 2006, 18:20

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
  •  
porno