Copying several worksheets data to a master worksheet

**hairy** · 07-04-2008, 01:47 AM

I'm a newby to macros and would appreciate any help to achieve the following
I may be going about this completly the wrong way, but here is what I want to achieve.
I've got several worksheets that are used for storing different suppliers products and prices.
These worksheets are used to input any new products & price changes from suppliers, usually done by copying and pasting the entire data in the
appropriate worksheet with the new list from the supplier.
Worksheets are named ACM ACA Avon Crawford Graphis Larson Visual
Only 2 columns A & B are being used, A being "Product" & B "Price".
Another worksheet is called "Master"
that has the combined data from all the other worksheets.
I use "Master" to search & maintain quantities and total stock prices etc.
I would like a macro that would copy only new Product entries or changed prices from all my Suppliers worksheets to my Master worksheet,
so it has to check to see if my "Master" worksheet already has the product entry first
if not then add this new row at this position
if exists already then check if the price has changed
if changed then replace price

Again any thoughts appreciated

**rylo** · 07-04-2008, 01:56 AM

Hi

Try this

Sub ccc()
  Dim OutSH As Worksheet
  Set OutSH = Sheets("Master")
  
  For i = 1 To Sheets.Count
    If Sheets(i).name <> "Master" Then
      With Sheets(i)
        For Each ce In .Range("A2:A" & .Cells(Rows.Count, 1).End(xlUp).Row)
          If WorksheetFunction.CountIf(OutSH.Range("A:A"), ce.Value) = 0 Then
            OutSH.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Resize(1, 2).Value = ce.Resize(1, 2).Value
          Else
            Set findit = OutSH.Range("A:A").Find(what:=ce.Value)
            findit.Offset(0, 1).Value = ce.Offset(0, 1).Value
          End If
        Next ce
      End With
    End If
  Next i
        
End Sub

rylo

**royUK** · 07-04-2008, 02:45 AM

hairy

Please take the time to read the Forum rules & use appropriate wording in your questions' Titles.

**hairy** · 07-04-2008, 02:50 AM

I will have a play with it

**hairy** · 07-04-2008, 03:19 AM

Another couple of thoughts if possible to be done easily

As the worksheets the data is coming from all have different backgroud fill colours can that also be copied into Master

If I have a new product added into a input worksheet, rather than always going to the bottom of the Master could it be positioned in the Master relative to which input worksheet it came from.

Thanks hairy

**rylo** · 07-04-2008, 04:39 PM

Hairy

1) Should be able to - do a copy / paste rather than transfer the values.
2) How would you expect it to know where it would sit in the master list? Is there something else in the data that would show where it came from?

Perhaps put up an example file so better show your details and how you would like it to appear.

rylo