vba for exporting data based on criteria and save as new xls workbook

**jjwp** · 04-19-2019, 03:08 PM

Hi all,

I'm new to vba and trying hard to learn the coding but struggling.

I would like to export data and the corresponding rows based on a value in another cell and then save this as a new workbook.

The criteria would be "yes" and then copy that entire row. In this exercise there would be several rows with the criteria "yes".

The new workbook would then be used in a mail merge function. IF there is a code I can use to automatically create the mail merged pdf that would be great and any insight would be greatly appreciated.

I hope I've given enough information and if not, please let me know!

Thanks in advance

**alansidman** · 04-19-2019, 05:24 PM

Attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

**jjwp** · 04-20-2019, 03:42 AM

Hello!

Thanks a lot for replying.

I've attached two files:
1) Register
2) Merge

the former is a redacted version of my workbook, which in its current form contains two tabs; overview and register.

On the overview, i will have the merge command button which will look into the register and check down the Merge column, if a cell returns "yes", then all the rows to the
left will be copied up to and including column A.

The rows matching the criteria will then be saved as a separate file titled "merge" (attached as "merge"). The data that has been copied from the register should not be deleted
as it is the main data source for my project. The merge sheet in the destination file does not need to be saved for future use, so subsequent merge actions can replace the former merge sheet
in that file.

In the VBA code, if you could write notes which would help me understand and therefore help me learn the code, that would be much appreciated however, no need
if this going to be time consuming for you.

thank you very much in advance!

**MrShorty** · 04-20-2019, 03:10 PM

Your description seems to me very similar to a filter to a different location operation. You might be able to get Advanced Filter or maybe something like power pivot/power BI to do this without VBA.

If I were to program this in VBA, I would probably use one of those filter tools from within VBA. There's sample code at the end of the Range object help file that uses Advanced Filter to output a filtered list to another worksheet in a workbook. I might start with that code and adapt it to your specific requirements: https://docs.microsoft.com/en-us/off....Range(object)