VBA SCRIPT to copy information to different worksheets based on a criteria

**Excel_Padwan** · 01-18-2014, 08:58 PM

Hi Experts,

I'm working on a VBA script for reporting purposes; at one point I managed to create a script that would copy data from one sheet to another with one criteria; but when I got two criteria involved i couldn't manage to get the script to work properly.

As per the attached excel document;

Report Manager Data - contains total jobs submitted
Google Data - Contains issues that occurred after the job was submitted

The Aim: To have the user worksheets populated with the jobs that the USER has submitted along with the issues that occurred after there jobs were submitted.

IF Column X = "Event 6: QA Finished" And Column Y Contains USER 1 then Copy active row cells Y, AA and AE to worksheet User 1 (Cells A2-C2) and a loop for all of User 1's jobs then do the next (example User 2 to worksheet 2) till all the users information is in their specific sheets.

The next step would be to go through the WorkSheets (Users1 till last User)
and check the following Criteria:

IF B2 from WorkSheet User 1 is present in column J in Google Data and C2 is present in Column X ( IF both criteria match) Copy active row to A4:X4 and loop till all issues are listed.

Then go to worksheet 2 User 2 and follow the same criteria.

Would it be possible for someone to help me out with this ? I would highly appreciate it

**AB33** · 01-18-2014, 11:04 PM

I am not sure what does this line mean? "Copy active row to A4:X4 and loop till all issues are listed".
I assumed if there is a match you want to copy A-X from sheet Google Data into that row which has a match, starting from column D as you have already got data in columns A-C.

Please note, I have to manually change the names as they do not match the data in column Y. You must enter the names of the sheet from column Y, so that they match. If they do not match, you will get a weird and wrong result.
It is bed time in this part of the world and will catch up tomorrow.

**Excel_Padwan** · 01-19-2014, 08:30 AM

Hi AB33;

thanks for your help; it was basically exactly what I needed; but I noticed when I added it to the Final Document; it wasn't copying theGOOGLE DATA across.

I also noticed after running the script it had information from the Google Data in regards to a job that wasn't done by the user. (Refer to attached)

Is it possible to have a button to run this script?

Would you please be able to have a look into the attached and provide some guidance. Thanks in advance.

Mismatch.PNG

**AB33** · 01-19-2014, 10:23 AM

How many sheet do you have in the workbook?
Initially, I thought I could use the length of the sheet names, as all of them are 2 words, but there is one exception.
If the worksheet names are not consistent (2 letters), I will change the code to exclude all sheet names except the relevant column Y names.

The code button is on sheet Report Manager Data. You need to click it to run.

**Excel_Padwan** · 01-20-2014, 04:43 AM

Hi AB33,

I really appreciate your help.

For the names of the users; at the moment I've kept them as initials but for the final report they will be full names.
The total number of worksheets would depend on the number of users that are in the criteria. (this can vary, there is no set number)

1) At the moment when you run the script it is only listing one defect number per project id (Google Data) would it be possible to list all the defects for each project done by that user.

Example : User YA did project "ayca-h8qsj" AND "TTFN"
there are 2 defects within the google data (6759 and 6762) however at the moment the script is only bringing 6762. Most projects have multiple defects and need to be listed.

Also would it be possible to:

2) have the end result formatted automatically ? Basically a bit more spaced out and presented (Example attached)Please note all cells are auto fit to format and column O is text wrapped. (Refer to WorkSheet LK in the attached)

Thanks so much for helping me out on this.

Thanks in advance,

**Excel_Padwan** · 01-22-2014, 08:17 AM

Hi Guys,

Would it be possible to get some help on the above ?

Your help will be highly appreciated,

Thanks in advance.

**Excel_Padwan** · 01-26-2014, 07:09 PM

Hi Guys,

would it be possible for someone to help me out with the below ?

Please (refer above for the attachment)

Thanks in advance

Originally Posted by Excel_Padwan

Hi,

I really appreciate your help.

For the names of the users; at the moment I've kept them as initials but for the final report they will be full names.
The total number of worksheets would depend on the number of users that are in the criteria. (this can vary, there is no set number)

1) At the moment when you run the script it is only listing one defect number per project id (Google Data) would it be possible to list all the defects for each project done by that user.

Example : User YA did project "ayca-h8qsj" AND "TTFN"
there are 2 defects within the google data (6759 and 6762) however at the moment the script is only bringing 6762. Most projects have multiple defects and need to be listed.

Also would it be possible to:

2) have the end result formatted automatically ? Basically a bit more spaced out and presented (Example attached)Please note all cells are auto fit to format and column O is text wrapped. (Refer to WorkSheet LK in the attached)

Thanks so much for helping me out on this.

Thanks in advance,

**Excel_Padwan** · 01-29-2014, 06:57 AM

Hi Guys,

Would anyone be able to help me out with the above ? I would really appreciate it

**Excel_Padwan** · 02-01-2014, 01:31 AM

Hi Guys,

Would anyone be able to help me out with the above ? I would really appreciate it

VBA SCRIPT to copy information to different worksheets based on a criteria

LinkBack

Thread Tools

Rate This Thread

Display

VBA SCRIPT to copy information to different worksheets based on a criteria

Re: VBA SCRIPT to copy information to different worksheets based on a criteria

Re: VBA SCRIPT to copy information to different worksheets based on a criteria

Re: VBA SCRIPT to copy information to different worksheets based on a criteria

Re: VBA SCRIPT to copy information to different worksheets based on a criteria

Re: VBA SCRIPT to copy information to different worksheets based on a criteria

Re: VBA SCRIPT to copy information to different worksheets based on a criteria

Re: VBA SCRIPT to copy information to different worksheets based on a criteria

Re: VBA SCRIPT to copy information to different worksheets based on a criteria

Thread Information

Users Browsing this Thread

Similar Threads

Unique Copy and Paste Macro - Multiple Worksheets Based on IF (AND) criteria

copy rows from multiple worksheets to a new condition based on criteria

[SOLVED] Copy data across multiple worksheets (based on three criteria)

Pull information from mult. worksheets into 1 based on certain criteria.

Copy information from one cell to many cells based on certain criteria

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions