0

I have multiple sheets, the main sheet Input has my raw data, I have another sheet with my filtered data. I want to create a new sheet for each cell value in Sheet2 column A and have it populate the sheet with the rows matching my cell value in Sheet1.

My code below creates the sheets with the cell values but only populates the first sheet.

Sub CreateSheetForValue()

    Dim d As Range
    Dim c As Range
    Dim j As Integer
    Dim Source As Worksheet
    Dim Target As Worksheet
    Dim Condition As Worksheet

    Set Source = ActiveWorkbook.Worksheets("Sheet1")
    Set Condition = ActiveWorkbook.Worksheets("Sheet2")


    j = 2    'This will start copying data to Target sheet at row 2


      For Each d In Condition.Range("A2:A21") 'specifiy condition

      'create wroksheet for each value in condition

      Set Target = Sheets.Add(after:=ActiveSheet)
       'ActiveSheet.Name = d.Value

      Target.Name = d.Value


        Sheets("Input").Range("A1:G1").Copy Target.Range("A1")



            For Each c In Source.Range("E2:E1893")

                If Target.Name = d.Value And c.Value = d.Value Then

                    Source.Rows(c.Row).Copy Target.Rows(j)
                    j = j + 1

               End If

            Next c
        Next d
End Sub
FunThomas
  • 23,043
  • 3
  • 18
  • 34
PGNewbie
  • 15
  • 6
  • Side note: I'd find the last row on each sheet instead of using `j` and `j = j + 1`... as you'll need to reset `j` for each separate `Target`. – BigBen Feb 04 '20 at 15:17
  • j is the counter for each new row that will be added to the new sheets and is set to start on the second row so that my headings are not written over – PGNewbie Feb 04 '20 at 15:38
  • Right but the value of `j` changes for *all* sheets whenever a row gets added to any sheet... that's problematic. e.g. if you copy a row to Sheet A, `j` will be `3` when you copy a row to Sheet B or Sheet C or Sheet D, even though it should only be `2` for those sheets. – BigBen Feb 04 '20 at 15:39
  • ok, I understand. I'm not sure how to set it. Sorry new to vba – PGNewbie Feb 04 '20 at 15:42
  • See [this question](https://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-excel-with-vba). You want to work with `End(xlUp)`. – BigBen Feb 04 '20 at 15:43
  • Thank you so much, I just rest the counter and its working perfectly – PGNewbie Feb 04 '20 at 16:07

0 Answers0