VBA to copy data into other sheets based on specific criteria’s

**humacdeep** · 01-26-2012, 09:49 AM

Hello,
I need your help in preparing a Dash Board. I have an excel file with data pulled from external source & have lot of dump in it. I am looking for specific data that need to be pulled from that dump and saved in another sheet of the same file (as in example file).

In the Received Sheet:
1. It should prompt for the date and after entering the date (21- Jan in example file) it should fetch the data of Team A to Team E and paste it in Received Sheet based on Column L (Insert_time).
2. If this sheet is not available it should create a new one, if the sheet is already available then it need to clear the old data and paste the new one.

In the Closed Sheet:
1. It should prompt for the date and after entering the date (21- Jan in example file) it should fetch the data of Team A to Team E and paste it in Closed Sheet based on Column N (Close_Time) & Column U (Status of only “Closed”, “Closure Pending”, “Verified Closed”)
2. If this sheet is not available it should create a new one, if the sheet is already available then it need to clear the old data and paste the new one.

In Open Sheet:
1. It should fetch the data of Team A to Team E based on Column U (Status of only “Open”, “New”, “pending”)

I have attached the sample spread sheet for your reference. Sorry if my request is not clear.

Thanks in advance for your help!!

Regards,
Humac

**arlu1201** · 01-27-2012, 05:26 AM

Your sample data shows Teams F, G & X along with Teams A to E. What should be done for these 3 teams?

**humacdeep** · 01-27-2012, 06:20 AM

Hi Arlette,

Thanks for your response. It should ignore all other teams apart from Team A to Team E. I may even have more teams in the orginal data but looking for only specific teams data in the result.

Regards,
Humac

**humacdeep** · 01-28-2012, 06:43 AM

Hi Arlette,

Did you get a chance to look into this?

Regards,
Humac

**arlu1201** · 01-28-2012, 06:59 AM

Sorry, i have another question - in your first post, you have mentioned that the code needs to prompt for the date twice, will the dates in both prompts be the same? Can the prompt be given only once in that case?

**humacdeep** · 01-28-2012, 07:02 AM

Yes, the date is same for both prompts & can the prompt can be given once.

**arlu1201** · 01-28-2012, 11:41 AM

While working on the code for your request, i landed upon another doubt - will the sheets Received, Closed and Open be blank when the data will be copied over? So i can just search for the date provided and then copy the contents over from sheet1 to the required sheet?

**humacdeep** · 01-28-2012, 02:04 PM

No, I will use the same template daily. So the code need to clear the existing data in Received, Closed and Open sheets & then copy the data.

Also all the Received, Closed and Open sheets should have the Heading from Row1 of the Sheet1.

**humacdeep** · 01-30-2012, 02:46 AM

Hello Arlette,

Any luck on this?

Regards,
Humac

**arlu1201** · 01-30-2012, 03:04 AM

Humac, last nite the forum was down for some time during the upgrade to the new server. I will try something now and post it.

**humacdeep** · 01-30-2012, 03:10 AM

No Prob... Thanks!!

**arlu1201** · 01-30-2012, 04:57 AM

Use this code - it takes care of all 3 requirements -

Please Login or Register  to view this content.

**humacdeep** · 01-30-2012, 05:08 AM

Thanks Arlette, I will test and let you know the result.

**humacdeep** · 01-30-2012, 05:19 PM

Hello Arlette,

I have tested the code in Orgianl data, it is creating all the required sheets but once I enter the date it is not copying data in Received & Closed Sheets. I have attached the sample sheet. please have a check.

I have entered 25-Jan as Date.

Regards,
Humac

**humacdeep** · 01-31-2012, 02:59 AM

Its working as desired in DashBoard.Xls (sample data) & not in DashBoard_1.xls (Orginal Data), the only difference is between two files is Table format. Does this code will not work if the data in Sheet1 is in Table format?

**arlu1201** · 01-31-2012, 07:09 AM

In Dashboard_1, you have marked the code for Closed and Open in green (commented it). This will prevent the code from running. Any specific reason why you have done so?

**humacdeep** · 01-31-2012, 07:14 AM

Hello Arlette,

I commented that to see what all are executing in 3 sheets, only Open sheet is populating the data. Even if I execute the entire program its only populating data in Open sheet.

Regards,
Humac

**arlu1201** · 01-31-2012, 07:43 AM

The date format that you gave me in the dummy file was dd-mmm but in this file, its with a timestamp as well. So i just had to change a few things and got it working. Use this code -

Please Login or Register  to view this content.

**humacdeep** · 01-31-2012, 08:08 AM

Thanks a LOT & I appriciate your help!! Its working gr8.

I got the point now, it populated data in Open sheet because it does not need date to validate the data

.

**arlu1201** · 01-31-2012, 08:10 AM

Yes exactly. Next time ensure that you give the data in the same format as your original file, it works faster.

**humacdeep** · 01-31-2012, 08:31 AM

Sorry about that. Actually I have added the same way in the sample sheet, but due to my excel format it has been changed to dd-mmm

. I make sure to double check it from now onwards.

**karo** · 01-31-2012, 08:58 AM

hi all,

i also have some problems when i want to copy data from other files.

I want to use Command button to open folder, which contains .csv files. When i'll choose one of the files the aplication should put all the data from the file to an excel sheet 1.

could you pls help me with this?

**humacdeep** · 01-31-2012, 09:03 AM

Karo,

Welcome to Forum!!!

As per the forum rules No. 2 you should not hijack others thread. Please submit a new thread and some one will get back to you.

Forum Rule 2: Don't post a question in the thread of another member -- start your own. If you feel it's particularly relevant, provide a link to the other thread.

Regards,
Humac

**karo** · 01-31-2012, 09:12 AM

sorry, i'm new here, it wont happen again

karo

VBA to copy data into other sheets based on specific criteria’s

LinkBack

Thread Tools

Rate This Thread

Display

VBA to copy data into other sheets based on specific criteria’s

Re: VBA to copy data into other sheets based on specific criteria’s

Re: VBA to copy data into other sheets based on specific criteria’s

Re: VBA to copy data into other sheets based on specific criteria’s

Re: VBA to copy data into other sheets based on specific criteria’s

Re: VBA to copy data into other sheets based on specific criteria’s

Re: VBA to copy data into other sheets based on specific criteria’s

Re: VBA to copy data into other sheets based on specific criteria’s

Re: VBA to copy data into other sheets based on specific criteria’s

Re: VBA to copy data into other sheets based on specific criteria’s

Re: VBA to copy data into other sheets based on specific criteria’s

Re: VBA to copy data into other sheets based on specific criteria’s

Re: VBA to copy data into other sheets based on specific criteria’s

Re: VBA to copy data into other sheets based on specific criteria’s

Re: VBA to copy data into other sheets based on specific criteria’s

Re: VBA to copy data into other sheets based on specific criteria’s

Re: VBA to copy data into other sheets based on specific criteria’s

Re: VBA to copy data into other sheets based on specific criteria’s

Re: VBA to copy data into other sheets based on specific criteria’s

Re: VBA to copy data into other sheets based on specific criteria’s

Re: VBA to copy data into other sheets based on specific criteria’s

Re: VBA to copy data into other sheets based on specific criteria’s

Re: VBA to copy data into other sheets based on specific criteria’s

Re: VBA to copy data into other sheets based on specific criteria’s

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions