Best way to input data into multiple sheets in Excel?

Basically I need to be able to have a few tables for sales of specific categories e.g. pc and laptop. I also need to have a main table that displays all sales.

For input i'm using input boxes and a dropdown to select which category (pc or laptop), this input is then just put in to one large table along with the sale information. I'm just wondering what the best method for having this data put in to a separate worksheet just for that category is? I'm at a bit of a loose end where to start. I thought about possibly using an IF statement in the VB where if the input is e.g. PC then also insert into the worksheet PC but this seems like quite an inefficient way of doing this.

Your ideas are greatly appreciated.

If you are unsure where to place your VB code, it's probably best to add the code not behind one of the sheets, but in a separate module. On the other hand, you will need an entry point your code which is called at a specific event. That might be a worksheet event (for example, the change event) or simply a button or menu the user has to activate when he wants the main table to be updated. Call your module code from there.

If you could provide us with a specific example, we could probably give you a better answer.

Using the worksheet change event: add code like this to each of your input sheets:

Private Sub Worksheet_Change(ByVal Target As Range)
    UpdateMainSheet(ActiveSheet)
    ' or UpdateMainSheet(Target)
End Sub

UpdateMainSheet should be a public Sub in a separate module which copies the input data from the given sheet to your main sheet (should also delete data in the main sheet which has been deleted on the given sheet).

Is it a "Must" to have the same record stored in 2 sheets?

If it is just to see only PC's or Laptops at a time, you can always use a filter.

What if the user updates a record after entering, and what if the user does the edit not in the Master sheet, but in the Clone_Laptop sheet? This will bring all sorts of confusions unless you update both ways, i.e.

for each and every change in any of the sheets (Master, Clone_PC, Clone_Laptop) you must copy the whole record to the other sheet to keep sheets in sync, meaning you have to

• find the record in the target sheet by a unique key (do you have such ??)
• if found, replace current target row by source row
• if not found, copy source row to first empty target row

As such a routine - if written properly - may act both ways, you would definitely place it in a seperate module and call it from the Worksheet_Change() event procedure from each sheet, passing parameters of source row (Target.EntireRow) and destination sheet.

Hint: Pay attention to a proper abstraction of your problem. Say - if you create one extra table containing a list of categories together with the name of the sheet, you can freely determine which category should go to which sheet, you can give 2 or more categories to the same sheet, etc. and you have a good list for Validation of the categories in the main sheet. Finally, for each new sheet you might create later on for new categories there will be 1 line of code to add (calling the Sync routine with "MasterSheet" as the target parameter)