macro to fill data on different worksheet

**Ceeseven** · 04-27-2009, 05:53 PM

Hi,

If i was using a userform, I might be able to tackle this one on my own.

What I need , is a macro to take data entered from "worksheet1" and fill in next row on a separate worksheet based upon salesman selected from cell C20 in "worksheet1" .

For the sample, I have just 3 saleman in a dropdown list and have labeled worksheets accordingly. If i could get help with say just using info from cells C6,c7 and e6 from "worksheet1" and put it where it goes.

c6 last name is row b on data sheet
c7 first name is row c
e6 trade year is row o

I should be able to work out the rest.

Thanks for looking and/or trying. Sample attached (2003)

**rylo** · 04-27-2009, 06:26 PM

Hi

Here's a start. It does assume that there will always be Customer SDA# for every entry.

Note you have a heap of formulas on sheet CV starting in row 2. Remove those before running the macro or the data will not be placed until row 2001.

Please Login or Register  to view this content.

rylo

**Ceeseven** · 04-28-2009, 01:02 PM

Thank you for the reply, RYLO.

Those formulas in the rows assign the "sda number". can the macro be modified to generate a number instead?

The perfect number would include date and salesperson.

sda# cv09042801
cv salesperson
09 year
0428 date
01 first customer that day

and again THANK YOU!

**rylo** · 04-28-2009, 06:19 PM

Hi

I'd got the other way and have the number generated in Worksheet1!C5. That way the macro will just have to copy it over.

To show you what I mean try on Worksheet1
C36: =C20&TEXT(NOW(),"yy")&TEXT(NOW(),"mm")&TEXT(NOW(),"dd")
C37: =TEXT(COUNTIF(INDIRECT(C20&"!A:A"),C36&"*")+1,"00")
C5: =C36&C37

Now when each day changes, and you change the Salesperson, the number will be automatically generated.

HTH

rylo