+ Reply to Thread
Results 1 to 9 of 9

VBA Create new workbook and copy paste data

  1. #1
    Registered User
    Join Date
    09-05-2014
    Location
    canada
    MS-Off Ver
    2010
    Posts
    5

    VBA Create new workbook and copy paste data

    I'm new to the forums and just started learning VBA. I was wondering if anyone could provide some assistance in creating a VBA macro to do the following:

    I have a set of data in one workbook which I'll call Workbook1 for instance.

    For example, if there is information on a PivotTable like this:

    I would like to create a macro (an inputbox maybe) to search for a region (A for example) and create a new workbook & save with the region's name (A.xls for example). The more complicated part would be to then copy the information to the new workbook from columns "office - total" and all the rows from office AA to total. I tried to figure this out searching through the forums but couldn't quite put everything together. Any help would be much appreciated!

    Thanks

    Edit: What i'm trying to aim at is to be able to input a region in the source workbook and automatically create a new workbook with the inputted region. The new workbook should then be automatically populated with information under the region from the source workbook.

    I've attached a test spreadsheet which I'm using to try and replicate the effect but with less complex data.
    Attached Files Attached Files
    Last edited by jayt91; 09-05-2014 at 11:34 AM.

  2. #2
    Forum Expert
    Join Date
    10-09-2012
    Location
    Dallas, Texas
    MS-Off Ver
    MO 2010 & 2013
    Posts
    3,049

    Re: VBA Create new workbook and copy paste data

    So can you post a sample workbook? A photo doesnt really do much for us.

    Also specifically mock up what you are trying to dol, start and finish.
    Please ensure you mark your thread as Solved once it is. Click here to see how.
    If a post helps, please don't forget to add to our reputation by clicking the star icon in the bottom left-hand corner of a post.

  3. #3
    Registered User
    Join Date
    09-05-2014
    Location
    canada
    MS-Off Ver
    2010
    Posts
    5

    Re: VBA Create new workbook and copy paste data

    Thanks for the reply, I updated with just a sample and a description of what I'm trying to achieve. Hopefully that makes it clearer.

  4. #4
    Registered User
    Join Date
    09-05-2014
    Location
    San Diego, CA
    MS-Off Ver
    Office 7
    Posts
    2

    Re: VBA Create new workbook and copy paste data

    I'm new to the forum, and I would first like to thank you all for any help you can give me. Here is my problem, which is similar and I don't know if a solution needs to involve a VBA program sequence or if it can be done in the workbook.

    I have two files, see attached, and would like to match names from rows in each spreadsheet. Where a match is found, I would like to take the ship date found in File B and insert it into File A. I've left in most of the extraneous information to give you a real-life example. Thanks for your help.

    File A.xlsxFile B.xlsx

  5. #5
    Registered User
    Join Date
    09-05-2014
    Location
    canada
    MS-Off Ver
    2010
    Posts
    5

    Re: VBA Create new workbook and copy paste data

    Edit: I noticed it was more appropriate to put this in the VBA subforum. Continued here:
    http://www.excelforum.com/excel-prog...ml#post3824647

    I posted what I have so far for my VBA script there.
    Last edited by jayt91; 09-05-2014 at 01:11 PM.

  6. #6
    Registered User
    Join Date
    09-05-2014
    Location
    San Diego, CA
    MS-Off Ver
    Office 7
    Posts
    2

    Re: VBA Create new workbook and copy paste data

    Thank you Jay!

  7. #7
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 insider Version 2505 Win 11
    Posts
    24,720

    Re: VBA Create new workbook and copy paste data

    Here is another solution in VBA

    Please Login or Register  to view this content.

    How to install your new code
    1. Copy the Excel VBA code
    2. Select the workbook in which you want to store the Excel VBA code
    3. Press Alt+F11 to open the Visual Basic Editor
    4. Choose Insert > Module
    5. Edit > Paste the macro into the module that appeared
    6. Close the VBEditor
    7. Save your workbook (Excel 2007+ select a macro-enabled file format, like *.xlsm)

    To run the Excel VBA code:
    1. Press Alt-F8 to open the macro list
    2. Select a macro in the list
    3. Click the Run button
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  8. #8
    Registered User
    Join Date
    09-05-2014
    Location
    canada
    MS-Off Ver
    2010
    Posts
    5

    Re: VBA Create new workbook and copy paste data

    The code works fairly well, thanks!

    Edit: Figured out my problems and will try to see if I can complete it myself.
    Last edited by jayt91; 09-05-2014 at 03:51 PM.

  9. #9
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 insider Version 2505 Win 11
    Posts
    24,720

    Re: VBA Create new workbook and copy paste data

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

+ 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. Replies: 6
    Last Post: 03-26-2014, 11:40 PM
  2. Replies: 6
    Last Post: 01-29-2013, 07:01 AM
  3. [SOLVED] create workbook and paste data and cut paste columns
    By farrukh in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 06-17-2012, 04:22 AM
  4. Copy data from workbook, create new workbook, paste data to new worbook?
    By mpkavanagh in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 02-16-2011, 06:39 PM
  5. Replies: 1
    Last Post: 10-17-2005, 04:05 AM

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