copy and paste row if column c contains certain info

Hi,
I am working on a project but seem to be going nowhere with it, andbody that could help please let me know.

I have a worksheet. and i would like to copy and paste all rows in that worksheet where Column C contains data between CA0010000 to CA0019999. Column C also contains numbers that look like that eg. CA00500322, stuff like that, this will go on seperate worksheets.
So i would like to have excel automatically transfer a row that meets that criteria (Column c contains CA0010000 to CA0019999) to a seperate worksheet in the same workbook.

i was hoping this will be like a real time process. transfers as we type it in or as we copy and paste data in the "masterlog". column c in the masterlog is where the source would be and another worksheet named CA0010000 would be where i would like the whole row to be transfered to.

any help will be highly appreciated

Please attach a sample workbook so we can help you better.

THANKS FOR YOUR HELP
i am trying to auto transfer all range between ca0010000-ca0019999 to "ALAMEDA" which is sheet 2.
and as you will see in the example, all CA0380000 or anywhere between CA0380000 to CA0389999 shoould transfer to the SAN FRANCISCO tab.
I am hoping that the whole row will transfer as it is typed in or as get copied and pasted from somewhere to the "MASTER" sheet.

thanks again

Another question. Once the data is copied today for example, if the user wants to copy the data again tomorrow, wont it result in duplicates? Is there a way to identify which records have already been copied v/s those that havent?

everything from the master log transfers as they are pasted, and also stays on the master log. the next day more is pasted right on the master(next available line).
the master keeps getting entries for a month.

I understand this correctly. However, my question is....if today the user copies from the master to the "Alameda" tab and again tomorrow there is more data to be added, should the new data be appended to the next available line in the ALAMEDA tab or should everything be copied again?
Is there a way to identify which records have already been copied from the master v/s those that havent?

all the users will only be able to access the master log only.
all other tabs like Alameda will be locked. what they paste in master log should automatically go to the corresponding tabs. the next or whenever something else is pasted on master log Excel should detect which tab it should go to and then paste it on the next available line in that tab (worksheet)
did i answer your question?

Yes .. but is it ok if we have a tick mark or something in the master tab to identify that the row has been copied?

i think i understand you, yes you can have a tick mark on each row somewhere.
or color code the rows that has been copied. but i wanted Excel to automatically detect the once that have been copied already. the tick mark you talking about is that something i have to go and manually put it in each row.

No if we go with the tick mark, excel will put it for you once the copy is done and it will remain in the master tab. The next day, when the macro attempts to copy the data over, it will check for the tick mark and skip that row.

that should work them
thanks again

Will try and have something ready for you by today.

thanks for all the help

First of all, in column D of your Master tab, i have formatted that entire column to Webdings. The code will insert a tick mark in this column every time a row is copied. Try this code and let me know.

Please Login or Register  to view this content.

i copied and pasted the code under the View Code in the master tab and hit run but its giving me an error.
is that what i was supposed to do.
it was selecting the first row and that was it.

ca we put a code like if column "c" is >= CA0010000 and <= CA0019999
then paste those row to Alameda Tab

then i will have to put those tabs for all the rest of the tabs, example if column "c" is >= CA0380000 and <= CA0389999
then paste those row to San Francisco Tab.

reason I as is there is 52 tabs after the master which are different names just like San Francisco and Alameda.

You should put the code in the module. To do that, open the excel file, press Alt+F11, right click on Microsoft Excel Objects and insert ->Module. Then copy the code over.

You had specified 2 conditions, namely, >= CA0010000 and <= CA0019999 (which i have done the same way) and > CA0019999. If you do have more conditions, please provide all of them and i can get them coded for you. I have only specified 2 right now as you had provided me.

sorry about the confusion. that was actually just a sample. there are 52 tabs other than the master log. basically 52 different places example, Alameda, San Francisco, and more. I assumed I will be able to take one code put it in multiple times in the same module and i will just change the CA codes and Tab name to be transfered in. since there were 52 places or different tabs that is why I did not give all to you.

Ok i guess we can simplify this easily. What we can do is use some blank columns in your master tab where in you can put the various conditions and the sheet where it has to go to. The code can loop through all these conditions and perform the necessary actions.

Will this work for you?

yes, that sounds good. the only column that matters is, the master tab is column C. Conditions will change only in column C.
Like CA0010000-CA0019999, CA0020000-CA0029999, and so on. This is just an example.
If you show me a sample code that will transfer say, CA0010000-CA0019999 over to Tab named Alameda, i will try to modify that code as needed and try to do it where the rest goes to respective tabs when conditions change in Column C. (might need some help but I can try)

In the Master tab, i have put in some values in columns AA to AC. You can fill these up with your various conditions and see if it works. I have attached the worksheet here.

Try this code and let me know if you face any issues.

Please Login or Register  to view this content.

Did the code above work for you?

No it didint. But I dont think I am putting the code at the right place. I right click on master tab then click "view code". Then paste it. Then I hit run. But it doesn't work. I wanted it to work as new entries are made where I don't have to run a macro every time.