1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User


  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

copy and paste rows to sheets based on multiple selections

Discussion in 'VBA Macros' started by Jason Frazee, Oct 11, 2017.

  1. Jason Frazee

    Jason Frazee New Member

    Hey guys I currently have an excel sheet where column J has a dropdown arrow with 42 selections.

    I have made 42 sheets matching those selections. I need a way to have excel copy and move the rows to their sheet based on the selection. For example everytime I select "AMI" that row i made the selection in is then copied to a sheet named "AMI"

    I have code that does it now but only for one selection. Any help will be appreciated. I would also like to add something to have the script move the selection over instantaneously. I know its a big task which is why I am in need of help

    Sub CopySchoolAMI()
    Dim c As Range
    Dim j As Integer
    Dim Source As Worksheet
    Dim Target As Worksheet

    Set Source = ActiveWorkbook.Worksheets("all p.o.")
    Set Target = ActiveWorkbook.Worksheets("AMI")

    j = 1
    For Each c In Source.Range("J1:J1000")
    If c = "AMI" Then
    Source.Rows(c.Row).Copy Target.Rows(j)
    j = j + 1
    End If
    Next c
    End Sub
  2. p45cal

    p45cal Well-Known Member

    Supply a workbook with the necessary minimum; it will stop us guessing wrongly.
  3. Jason Frazee

    Jason Frazee New Member

    I attached the workbook. Once you select a selection from column J dropdown it will add the row to its destined sheet however if I select the same selection twice it will not continually add rows to the sheets.

    Attached Files:

  4. p45cal

    p45cal Well-Known Member

    Since where the row is copied to depends on:
    .Cells(Rows.Count, "A").End(xlUp)
    there has to be something in column A when something is copied over.

Share This Page