Updating multiple worksheets from a master

[rache]

I am new to VBA and have been trying to write something that will update multiple worksheets from a master based upon the name of the cell in column C, with limited success.

I would like to copy each entire row over to a worksheet of the same name as the cell in column C for any particular row. However, I do not want to create new worksheets everytime, rather copy from the master into an already made worksheet of the same name as the category given in column c. TO top things off, I would like to be able to simply "update" the workbook, so that additions can be made to the master on an ongoing basis and then added to the corresponding worksheet, rather than everytime copying all existing data in the master to corresponding worksheets. I hope I have been clear, I would appreciate any help and am happy to answer further questions!

Thanks!

 

[jbeaucaire]

Each time you run the macro, the values in Column C are used to parse data to sheets of the same name. Easy enough.

Explain more what you mean by "update". Do you mean:

1) Copy the rows from master to the named sheet at the BOTTOM of the existing data on that sheet.

2) Find the current example of each "row" of data in the named sheet and copy from Master to "replace" the existing row, AND add to the bottom of existing data is this row is new?

If you meant #2, you will have to explain more (or show us) how it is determined a row on the Master matches as existing row on the named sheet.

 

[rache]

New rows are added continuously to the master on a weekly basis. I want to be able to update the workbook, so that when new rows are added to the master they are then also added to the appropriate worksheet of the same name. I was thinking something like flagging data points in the master that have already been copied into the appropriate worksheet so that everytime the macro is run, every single row in the master is not copied over again.

Something like an If, then, else statement that could recognize the flag and skip all flagged rows in the master, and run the copy function on only unflagged rows in the master.

I hope that was clear!
Thanks for the reply!

 

[jbeaucaire]

Let's say you're column that has the value that matches other sheet names is column A.
Let's say the column that keeps track of xfered items is column Z, if that cell is blank, it's new.

Something like this:

Option Explicit

Sub TransferNew()
Dim LR As Long, sName As String
Dim NewRNG As Range, cel As Range

With Sheets("Master")   'put the name of the master sheet here
    LR = .Range("A" & .Rows.Count).End(xlUp).Row
    On Error Resume Next
    Set NewRNG = .Range("Z1:Z" & LR).SpecialCells(xlBlanks)
    If NewRNG Is Nothing Then
        MsgBox "No new items"
        Exit Sub
    End If
    
    For Each cel In NewRNG
        sName = .Range("A" & cel.Row).Value
        .Range("A" & cel.Row, "Y" & cel.Row).Copy _
           Sheets(sName).Range("A" & Sheets(sName).Rows.Count).End(xlUp).Offset(1)
        cel = "done"
    Next cel
End With

End Sub

 

[rache]

Thanks so much!!! That is exactly what I was looking for!