0

I have a master sheet that I have the row distributing to different sheets with specific labels.

enter image description here

I want to have each row distribute to the appropriate tab (from the Master) dependent on the information in column B.

I was using code to distribute the columns but had no way of deleting/updating columns from the master if they changed. I then added in an ID# which would be unique to each task.

I tried changing the code to read from column B but I end up with new sheets numbered from 1-31. The code worked before I added in the ID# column and had the line: Set rng = ActiveSheet.Range(ActiveSheet.Range("B3"), _ read A3.

My code:

Sub ProcessRows()

Dim rng As Range, cell As Range
Set rng = ActiveSheet.Range(ActiveSheet.Range("B3"), _
                 ActiveSheet.Cells(Rows.Count, 1).End(xlUp))

For Each cell In rng.Cells
    cell.EntireRow.Copy CopyTo(cell)
Next cell
End Sub

'Return a range object to which a row should be copied
'  Range returned is determined by the value in "rng"
Function CopyTo(rng As Range) As Range

Dim s As Excel.Worksheet, sName As String

sName = Trim(rng.Value) 'just in case...

On Error Resume Next               'ignore any error
Set s = ThisWorkbook.Sheets(sName) 'see if we can grab the sheet
On Error GoTo 0                    'stop ignoring errors

If s Is Nothing Then    'sheet didn't exist: create it
    Set s = ThisWorkbook.Sheets.Add( _
      after:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count))
    s.Name = sName
    rng.Parent.Rows(1).Copy s.Range("a1") 'copy headers
End If                  'needed a new sheet
'return the first empty cell in column 1
Set CopyTo = s.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)
End Function

The problem I'm having is that without a unique identifier I don't know how to get the macro to decipher if a line has changed and update it and I end up with duplicates. We add to this workbook all year so having the macro update the individual sheets is a big time saver.

To sum up I need a macro that will:

  1. Distribute each Master Sheet row to each appropriately labeled worksheet.
  2. I need each unique ID# to update that row if anything has changed.
  3. I also need to make sure that no duplicates exist on the sheets.

If there is a way to have the macro automatically run on closing the document that would be a bonus but manually running the macro is something I can live with.


Duplicate ROWS and information not updating

1 Answer 1

0

Modify this code

Set rng = ActiveSheet.Range(ActiveSheet.Range("B3"), _
             ActiveSheet.Cells(Rows.Count, 1).End(xlUp)) 

because it is reading the first column, to this:

Set rng = ActiveSheet.Range(ActiveSheet.Range("B3"), _
             ActiveSheet.Cells(Rows.Count, 2).End(xlUp))
5
  • Thanks, that totally worked! I also need to have my Master sheet update any information that changes without adding in the same line (ID#). I'm trying to research how to do this but so far am not having any luck. I either end up with 4 lines with the same ID# and different info or I have several lines with the same info and doesn't show any changes :(
    – Michelle
    Commented Apr 7, 2016 at 15:46
  • You mean just replace the information for the same ID
    – user555689
    Commented Apr 7, 2016 at 15:55
  • Yes. Right now every time I run the macro it ads the same lines into the corresponding sheet. I want to stop that from happening and just update the information. Thanks for answering back Sam :)
    – Michelle
    Commented Apr 7, 2016 at 15:58
  • I've added an image of the problem to my original post. It's at the bottom in a link :)
    – Michelle
    Commented Apr 7, 2016 at 16:06
  • You have to find the id for the transaction you want updating then replace the row in the corresponding sheet
    – user555689
    Commented Apr 7, 2016 at 16:23

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .