Fetching data from another WS

Let me see if I have the idea ... you want to BUILD a mastersheet based on EXISTING sheets by category?

Is this a one-time deal? Or, do you also want is so that any time that a new part number appears on any of the category sheets, it would (more or less automatically) appear on the master sheet?

If all you want is a one time deal, it is going to be faster to do it manually, even if you have 200 sheets. All you have to do is:
Click in cell A1 of a category sheet, hold down Ctrl & Shift keys and press the End button. This selects all USED cells on the sheet, then copy and paste to the master sheet.

If you want it to update automatically, you will need a VBA macro that is triggered by a workbook change event, then ensures that there is a one-to-one matching of items in that category with the master sheet (either adding to or deleting from the master sheet as necessary). This is not too difficult. How much VBA do you know?

Thanks MS for your reply,

You wrote:If you want it to update automatically, you will need a VBA macro that is triggered by a workbook change event, then ensures that there is a one-to-one matching of items in that category with the master sheet (either adding to or deleting from the master sheet as necessary). This is not too difficult. How much VBA do you know?


The catagory is not important, I was just using that as an example.

Just need to match the part nbrs from the mastes sheet to get the current Cost/prices.

At times this maybe a lot of items 1000,s, so I guess a macro would be the way to go. Don't know much VBA.

Now I am confused.

Are you reading data from the master sheet to update prices in the category sheets? Or are people adding items to the cateogry sheet and you want to have some way of begin sure that the master sheet has all of the individual items listed on it? Or both?

To do the former, a simple looked up (I would use MATCH to find the match, then INDEX to do the look up, but some people prefer to do both with a single function named VLOOKUP). Use Insert >> Function and select the category of "Lookup & Reference" for help with any of the 3 functions I have mentioned.

Automating the latter is what would take some VBA. If you have never used VBA before, no time like right now to start. Turn on the macro recorder (Tools >> Macro >> Record New Macro), click OK to accept whatever name it suggest (probably "Macro1"). Click in a cell, then turn off the macro recorder (Tools >> Macro >> Stop Recording).

Use Tools >> Macro >> Macros to open the Macro dialog. Select the macro you just recorder and click "Edit". Whatever you find there, delete it and replace it with this.

Sub readSheets()
Dim ws As Worksheet
Dim r As Range

    For Each ws In ThisWorkbook.Worksheets
        For Each r In ws.UsedRange.Rows
            myChoice = MsgBox(r.Cells(1).Value, vbOKCancel, ws.Name)
            If myChoice = vbCancel Then Exit Sub
        Next r
    Next ws

End Sub

This is just a start. It will read to you every item in column A on every sheet until you hit Cancel. When you get bored with this, press the Cancel button.

Thanks MS,

I will give that a try - like you said " no time like the present"!

Jim