+ Reply to Thread
Results 1 to 10 of 10

Create new workbook & copy data over from existing workbook

  1. #1
    Forum Contributor
    Join Date
    03-21-2013
    Location
    USA
    MS-Off Ver
    365
    Posts
    163

    Create new workbook & copy data over from existing workbook

    I've been trying and failing miserably to get this to work as I'm only a beginner at VBA in excel. Hopefully somebody can help me out with this. Instead of pasting my jumbled mess of code, I think it might be easier for me to map out verbally what I'm trying to do in this instance.

    I have Workbook A that has a button in it. When I click that button, I would like it to create a new workbook (Workbook B) by using a template workbook that exists in another folder location, and prompts the user to save the new workbook in the same location as Workbook A with whatever name they choose.

    When this new Workbook is created/copied from the template workbook, I need it copy values from Workbook A into various cells in Workbook B.

    The cells that need to be copied over from Workbook A to Workbook B are as follows...

    C3 to D4
    C4 to D5
    C5 to D3
    E2 to G3
    E3 to G4
    E4 to G5
    E6 to K3
    F1 to K4
    C1 to K5

    I also need to copy the filename of Workbook A into cell A2 in Workbook B.

    Any help with this is much appreciated. Thank you in advance.

  2. #2
    Forum Expert
    Join Date
    06-12-2012
    Location
    Ridgefield Park, New Jersey
    MS-Off Ver
    Excel 2003,2007,2010
    Posts
    10,241

    Re: Create new workbook & copy data over from existing workbook

    Maybe:

    Please Login or Register  to view this content.

  3. #3
    Forum Contributor
    Join Date
    03-21-2013
    Location
    USA
    MS-Off Ver
    365
    Posts
    163

    Re: Create new workbook & copy data over from existing workbook

    That's so clean and looks like it going to work out exactly as I had been trying to do myself... but one thing... What if I wanted the "WorkbookA.xlsx" to reference the current "worksheet" name, instead of "workbookA". This file name is going to change regularly, and so will the worksheets that this button will be on. So basically, all workbook references and worksheet reference need to be referencing whatever the current name of those files/tabs are. Hopefully I'm explaining this in a way that makes sense. And thank you so much for responding so quickly.

  4. #4
    Forum Expert
    Join Date
    06-12-2012
    Location
    Ridgefield Park, New Jersey
    MS-Off Ver
    Excel 2003,2007,2010
    Posts
    10,241

    Re: Create new workbook & copy data over from existing workbook

    Maybe:

    Please Login or Register  to view this content.

  5. #5
    Forum Contributor
    Join Date
    03-21-2013
    Location
    USA
    MS-Off Ver
    365
    Posts
    163

    Re: Create new workbook & copy data over from existing workbook

    I think it's almost working but I keep getting an error on the line I shared below.

    Run-time Error '9':
    Subscript out of range.

    Please Login or Register  to view this content.
    What does that mean exactly?
    Last edited by xlyfe; 06-13-2016 at 03:42 PM.

  6. #6
    Forum Contributor
    Join Date
    03-21-2013
    Location
    USA
    MS-Off Ver
    365
    Posts
    163

    Re: Create new workbook & copy data over from existing workbook

    Quote Originally Posted by mikerodrigueziii View Post
    I think it's almost working but I keep getting an error on the line I shared below.

    Run-time Error '9':
    Subscript out of range.

    Please Login or Register  to view this content.
    What does that mean exactly?
    Nevermind, I changed that line to say

    Please Login or Register  to view this content.
    And the program is moving forward now.

    I'm now trying to figure out how to get it to copy only the cell values, and not the entire cell.

  7. #7
    Forum Expert
    Join Date
    06-12-2012
    Location
    Ridgefield Park, New Jersey
    MS-Off Ver
    Excel 2003,2007,2010
    Posts
    10,241

    Re: Create new workbook & copy data over from existing workbook

    It is referring to the Sheet name of the Template workbook when opened. If it is not Sheet1 then change it in the code.

  8. #8
    Forum Expert
    Join Date
    06-12-2012
    Location
    Ridgefield Park, New Jersey
    MS-Off Ver
    Excel 2003,2007,2010
    Posts
    10,241

    Re: Create new workbook & copy data over from existing workbook

    Maybe:

    Please Login or Register  to view this content.

  9. #9
    Forum Contributor
    Join Date
    03-21-2013
    Location
    USA
    MS-Off Ver
    365
    Posts
    163

    Re: Create new workbook & copy data over from existing workbook

    That freaking did it man.

    How did you make that seem so easy? I long for the day that I can just write this stuff on my own off the top of my head.

    I am definitely better at writing VBA than when I first started, but I'm nowhere near what some of you guys can do on here in such a quick amount of time.

    Thank you so much for showing me stuff that I can definitely reuse in the future.

    May your the rest of your day be half as pleasant as you've just made mine.

    Thank you, thank you, thank you!

  10. #10
    Forum Expert
    Join Date
    06-12-2012
    Location
    Ridgefield Park, New Jersey
    MS-Off Ver
    Excel 2003,2007,2010
    Posts
    10,241

    Re: Create new workbook & copy data over from existing workbook

    You're welcome. Glad to help out and thanks for the feedback and the reps.

+ 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. How can I copy data from one workbook and create another workbook?
    By anmol0611 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 12-08-2015, 11:54 PM
  2. How to copy a data from another Workbook and create a graph in different workbook?
    By somani123 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 04-02-2015, 12:22 PM
  3. Download Excel Workbook from IE copy and paste into existing Workbook
    By kboy1289 in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 12-16-2013, 03:13 PM
  4. lookup and create summary sheet from data in existing workbook using vba
    By John Bortoli in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-13-2011, 02:26 AM
  5. 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
  6. create new workbook from existing worksheet data
    By philiasfogg in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 02-17-2009, 11:58 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