+ Reply to Thread
Results 1 to 4 of 4

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

  1. #1
    Registered User
    Join Date
    04-19-2019
    Location
    London
    MS-Off Ver
    ms 365
    Posts
    2

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

    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

  2. #2
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 2019; O365
    Posts
    18,989

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

    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.

  3. #3
    Registered User
    Join Date
    04-19-2019
    Location
    London
    MS-Off Ver
    ms 365
    Posts
    2

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

    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!
    Attached Files Attached Files

  4. #4
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    12,881

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

    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)
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Copy Data from one workbook into another based on criteria
    By hamidxa in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 09-13-2017, 12:53 PM
  2. Replies: 2
    Last Post: 04-13-2017, 08:25 AM
  3. Copy Data from workbook into another based on certain criteria
    By Rossko7226637 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 05-07-2015, 08:48 AM
  4. [SOLVED] Need code to copy data based on filter criteria to new workbook as save in a file location
    By msantucci in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 02-24-2015, 02:47 AM
  5. Importing and Exporting data from workbook to workbook in excel
    By dans123 in forum Excel Programming / VBA / Macros
    Replies: 13
    Last Post: 01-09-2012, 03:40 AM
  6. Exporting data based on criteria
    By reportmania in forum Excel General
    Replies: 1
    Last Post: 07-15-2010, 07:19 AM
  7. Copy Data to New Workbook based on Criteria
    By jimbean in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-05-2008, 03:06 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1