Copying to other worksheets

P

Putz

I have a master worksheet of names and in column D I have a dropdown of 12
names. These 12 names have a corresponding worksheet in this same workbook.
I would like to choose a name from the dropdown and then have the data from
column B & C copied to the corresponding worksheet to column B & C. So
something to the effect of IF D3=Name1, then copy B3 & C3 to Name1 worksheet.
Not sure how to handle this, if it's an IF statement, a LOOKUP or a MACRO.

The 2nd part of the equation is: While copying can Excel look at the
worksheet being copied to and paste the data in the next available row? So
if B3 & C3 have data already in them, paste to B4 and C4.

Thanks for any help anyone can offer.
 
D

Dave Peterson

Debra Dalgleish and Ron de Bruin have routines that split up data on a sheet to
multiple sheets based on the value of one of the cells.

It's not quite as automatic as you asked, but that may be a good thing. If you
choose the wrong name in error, and come back to fix it, it could be a royal
pain finding/fixing the data.

(Personally, I'd try to leave the data all in one spot--I'd try to use filters
and sorts to get different "views" of the data.)

But if you have to....

You may find something close at:

Debra's site:
http://www.contextures.com/excelfiles.html

Create New Sheets from Filtered List -- uses an Advanced Filter to create
separate sheet of orders for each sales rep visible in a filtered list; macro
automates the filter. AdvFilterRepFiltered.xls 35 kb

or

Update Sheets from Master -- uses an Advanced Filter to send data from
Master sheet to individual worksheets -- replaces old data with current.
AdvFilterCity.xls 55 kb

or maybe Ron de Bruin's easyfilter.
http://www.rondebruin.nl/easyfilter.htm

============
As for #2...

I'd do something like:

dim NextRow as Long
with worksheets("otherworksheetname")
NextRow = .cells(.rows.count,"B").end(xlup).row + 1
end with

'then copy something
worksheets("firstsheet").range("B1:C1").copy _
destination:=worksheets("otherworksheetname").cells(nextrow,"B")
 
Top