Macro to copy records to new worksheet

[boldcode]

Hello,

In my active worksheet I have certain records that I want to copy and paste to a new worksheet within the same workbook (new worksheet should come after all the worksheets in the workbook) that meet the certain criteria and depend on Columns “A” and “L” only. Cells “A1” and “L1” have row titles. The data begins in row “A2” and all the way through the last cell in Column “P”. The data in Column A contains random “ID numbers” and the data in Column L only contains Zeros (0) or Ones (1). The data set can have thousands of records.

I want the VBA code to copy those records to the new worksheet whose ID number in Column A has only ZEROS (0) in Column L. If a certain ID number in Column A has ZEROS (0) and ONES (1) collectively, then do not copy these records. I provided a visual of what I am looking for as an attachment to this post (Book1.xlsx).

[tigeravatar]

boldcode,

Give this a try:

Please Login or Register  to view this content.

[watersev]

hi, boldcode, please check attachment, run code "test"

[boldcode]

tigeravatar,

Your code works great! Is there a way to name the new worksheet that is being created to whatever I want within the code?

[boldcode]

watersev,

I encounter an error with your code if the cells in column P are blank. Column P could have data, but it doesn't necessarily have to contain data.

[tigeravatar]

Updated code. Find the line ws.Name = "Whatever you want" and edit the "Whatever you want" to whatever you want

Please Login or Register  to view this content.

[boldcode]

tigeravatar,

This definitely gets the job done. Thank for your time, I appreciate it.

[boldcode]

tigeravatar,

I thought I was out of the mud, but not yet. Once your code executes and creates the report how can the vba code return to the source file because I have more code after your code that needs to execute. In other words, I have code that runs before your code and after your code. Your code should create the new worksheet,but return to the previous worksheet to finish executing the rest of the code on the original worksheet and not the new worksheet.

thanks

[tigeravatar]

Updated code:

Please Login or Register  to view this content.

[boldcode]

tigeravatar,

It does work Awesome. I created some formatting code that I wanted to throw into your code just to autofit the columns:

Here is my code I just don't know where to place it within your code:

Please Login or Register  to view this content.

[watersev]

watersev,

I encounter an error with your code if the cells in column P are blank. Column P could have data, but it doesn't necessarily have to contain data.

The data begins in row “A2” and all the way through the last cell in Column “P

The code was written under your conditions

[boldcode]

watersev,

I understand, it was my mistake not yours at all. I appreciate your help.

[tigeravatar]

Updated code to include the .AutoFit

Please Login or Register  to view this content.

[boldcode]

tigeravatar,

Thanks again!!!

[boldcode]

Hi tigeravatar,

A while back you helped with this post, I was wondering if you could me again in modifying your awesome code.

Here is your code along with some code that I added to it:

Please Login or Register  to view this content.

Just a brief explanation of what this code above does since I added a little bit of my own code since that last time you helped me:

This code currently copies and paste specific records that meet certain criteria dependent on Columns A and L into a new worksheet named “T report”. Additionally, after it copies/paste those records into the new worksheet, it deletes those records that were copied from the active worksheet (original dataset that you started with).

I now want to modify the code to copy only those records (when I mean copy, I mean the entire row e.g., A2 to R2…the cells in column R could be blank or contain data…code should copy regardless of blanks in column R) to the new worksheet whose cells are “blanks” in Column L. I have provided you with before and after worksheets in the "T Exple" workbook to showcase what the new worksheet (T Report) should look like.

Thanks,

BC

[tigeravatar]

BC,

It's been awhile, had to review the thread to remember what was going on
It looks like you just want the rows with a blank T ID. If that is correct, this should work for you:

Please Login or Register  to view this content.

[boldcode]

tigeravatar,

It partially works, but I think I probably explained one thing wrong (I apologize). Your modified code does copy the correct records over to the new worksheet "T Report", but I still need those records that were copied over to be deleted from the original data set. I am providing you with another workbook (R Exple) to show you visually what I mean. As part of your new code I still want to incorporate the delete records code that I had before, but I have not been able to do it successfully just yet. I don't want to create a separate worksheet for the original data set I just want to delete those copied records from it. Here is the code again before you modified it just for reference.

This code deletes the copied records from the active sheet or in other words the original dataset

Please Login or Register  to view this content.

Thanks,

BC

[tigeravatar]

Updated code:

Please Login or Register  to view this content.

[boldcode]

tigeravatar,

It works like a charm! Thank you again.

[boldcode]

tigeravatar,

I appreciate your help thus far, I was wondering if you can help modify your
code just a bit more. Your code is sandwiched between some code that I
wrote which is the way I want it to be.

I tested your code below and it produces the T Report if it finds records
that meet the specified criteria as written in the code (which is awesome by the way).
If it doesn't find records that meet the specified criteria it does not produce the
T Report (which is the way it's suppose to work).

I am fine with the code not producing the T Report if it doesn't find any records
that meet the criteria, but I don't want the code to exit the sub because I have code
that comes after yours and I want the code that I created that comes after yours to
continue doing whatever it needs to do.

I hope this makes sense.

Please Login or Register  to view this content.

Thanks,

BC

[tigeravatar]

Updated code, give this a try:

Please Login or Register  to view this content.

[boldcode]

tigeravatar,

I tested the code and it works awesome. I was wondering if you could help me do the same with the following original code you once helped me with. The code below works exactly the I want it to, but again I don't want it to exit the sub because I have some code that comes after it. I know the variables might have the same names as the last code you just recently helped me with, but don't worry about it; I am running this code in a completely different workbook. I appreciate your help as I am slowing getting the hang of VBA coding.

Please Login or Register  to view this content.

Thanks,

BC

[tigeravatar]

Give this a try:

Please Login or Register  to view this content.

[boldcode]

tigeravatar,

almost works...for some reason it still produces the RAD report with row titles, but with blank records. It shouldn't produce the report unless certain records meet the criteria.

[tigeravatar]

hmm.. try updating the 'Set rngVis = ' line to be this instead:

Please Login or Register  to view this content.

[boldcode]

tigeravatar,

That fix it! Thanks again for all your help, I appreciate it.

Awesome job,

BC

[tigeravatar]

You're very welcome, and thank you for the feedback

[boldcode]

tigeravatar,

I thought I was out of the mud, but after running additional tests on your code below it seems to partially work. When the code produces the RAD report with records in it, the row titles are gone...I don't know why.

Please Login or Register  to view this content.

I would like the code to work for both scenarios and by both scenarios I mean:

1. If there are records that meet the criteria then the RAD report is produced with its row titles
2. If records do not meet the criteria the the RAD report is not produced (I know you fixed this because I know longer get a report with row titles showing blank records.

Here is the full code in case you need it with your latest changes to it:

Please Login or Register  to view this content.

Thank for your help,

BC

[tigeravatar]

Updated the rngVis.EntireRow.Copy line:

Please Login or Register  to view this content.

[boldcode]

tigeravatar,

that did it, thank you! I don't know how you do it, but one thing is for sure you know VBA really well.

thanks again,

BC