Solved Copy Row of Data automatically depending on cell value

November 7, 2016 at 07:55:13
Specs: Windows 7
Hi, I am looking to copy a row of data from a master sheet to a sub sheet based on the value in column A. this is for a sales organisation, so if cell a2 = "A" then copy whole row from master to Andrew's sheet. if cell a3 = "B" then copy to Bill's sheet and so on.

Thanks In advance


See More: Copy Row of Data automatically depending on cell value

Report •

#1
November 7, 2016 at 12:52:20
Is this a one time run to build the "sub-sheets" or is this an on-going task that you want to happen each time a "letter" is entered into Column A of the Master sheet?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
November 8, 2016 at 01:59:53
it would be a live document, so would be an ongoing task.

Thanks


Report •

#3
November 8, 2016 at 06:16:48
✔ Best Answer
I'll offer some basic code, but I don't think that it will fulfill your total needs.

As written, it will deal with the A and B entries and copy those rows, but I don't know what to do with your "and so on" comment. Obviously you could duplicate the "If-Then" sections for each sales-person, but that could get cumbersome if there is a lot of people.

In addition, this code will work for your existing Master data, but if you run it more than once, it will copy the same rows again. There are ways to deal with that, but I would need to know more about your process before I try to clean that up.

As far as the "ongoing task", I would need to know more about your process before I could automate the code. The rows could be copied as soon as the entry is made in Column A, but that would mean that the Column A entry would have to be the last entry you make, otherwise not all of the data would be present to copy.

Anyway, here is the basic code, but I'll need more information before I can offer anything more substantial.

Sub FirstRun()
Dim lastRw As Long, nxtRw As Long

'Determine last cell with data in Column A
  lastRw = Sheets("Master").Cells(Rows.Count, 1).End(xlUp).Row

'Loop through rows, Copy A to Andrew, B to Bill
   For rw = 1 To lastRw
     If Cells(rw, 1) = "A" Then
      nxtRw = Sheets("Andrew").Cells(Rows.Count, 1).End(xlUp).Row + 1
       Cells(rw, 1).EntireRow.Copy _
        Sheets("Andrew").Cells(nxtRw, 1)
     End If
     
     If Cells(rw, 1) = "B" Then
      nxtRw = Sheets("Bill").Cells(Rows.Count, 1).End(xlUp).Row + 1
       Cells(rw, 1).EntireRow.Copy _
        Sheets("Bill").Cells(nxtRw, 1)
      End If
   Next
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •
Related Solutions


Ask Question