Automating data import and separation

[Guest]

I have a workbook that provides a sales summary. On a regular basis I need
to import the new data and format it correctly (I have this programmed in
already). Once the data is ready I need a macro that will automatically take
each account and copy it's new sales data to another sheet depending on
geographic area. There may be 5-15 sheets that need to be looked at to find
the correct place to paste the data.

The full sales data sheet looks like (sheet name is "Raw Sales Data"):
Data start in row 3
Column A has account number (there will be approximately 100 accounts)
Columns E-H each have a numerical value. These are the values that need to
be copied to the other sheets with the matching account number.

Geographical area sheets have their respective accounts starting in cell
BA101. Formating and layout is the same on both sheets for this data. There
will be a variable number of geographica sheets with inconsistent names.
Each account will only be associated with one geographical sheet.

I would like to automate this so that when the macro is run, each account's
sales data is copied (Columns E-H) from "Raw Sales Data", the account is then
found on a different sheet and the sales data is pasted over the previous
month's figures (Columns BE-BH).

I hope this is clear. Does anyone have any ideas on how I can set up a
macro to search several sheets for a specific account number and paste the
correct information.

Thanks in advace,
BradK

 

[STEVE BELL]

If there are only 100 accounts - you can build a table on a hidden sheet.
First column = account #
Second column = sheet name (geographic area)

Than loop through "Raw Sales Data". Find the lookup value of sheet name
and use it in code to determine where to paste

generic code:
Sheets("Sheet1").Range("A12:E12").Copy _
Destination:= Sheets("Sheet2").Range("A35")

Sheets("Raw Sales Data").rng.copy _
Destination:=Sheets(wsh).pasterange

 

[Guest]

This would work except I am looking for something with a little more
versatility. The problem is that this will need to work for several
different people all with different number of accounts and different number
and name of geographical sheets. Each one could be custom set up as you
showed below, but that would require much more ongoing work.
Thanks,
BradK

 

[STEVE BELL]

Try this, adjust to your needs...
(note that there is no selecting being done.)
(you may want to add a check for itm not found...)

dim ws as worksheet, wsn as string, itm as string, lrw as long, rw as long

lrw = Sheets("Raw Sales Data").Cells(Rows.COUNT, "A").End(xlUp)

For each cel in Sheets("Raw Sales Data").Range(cells(1,1),cells(lrw,1))
itm = Sheets("Raw Sales Data").cel
For each ws in Thisworkbook.Worksheets
If worksheetfunction.Countif(ws.Columns(1),itm) = 1 then
wsn = ws.name
rw = worksheetfunction.Match(itm,Sheets(wsn).Columns(1),0)
Sheets("Raw Sales
Data").Range(cells(cel.row,1),cells(cel.row,5).Copy _
Destination:= Sheets(wsn).Cells(rw,1)
end if
Next
Next