Announcement

Collapse
No announcement yet.

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

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • 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

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

    Try this:
    Code:
    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

    Comment


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

      Comment


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

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

        Comment


        • #5


          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.

          Comment

          Working...
          X