+ Reply to Thread
Results 1 to 5 of 5

new workbook (WB) from template sheet, name new WB from cell val, then write data from cel

  1. #1
    Registered User
    Join Date
    07-09-2012
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    3

    new workbook (WB) from template sheet, name new WB from cell val, then write data from cel

    Hi,

    I have a workbook (wb) named "PM Master.xlsx". The wb contains two sheets: "PM Rollup" and "PM Template".
    "PM Rollup" contains over a hundred rows and each row is a roll-up of a single project.
    "PM Template" is a template sheet that has been formatted for a user to enter their detailed information for a single project.

    The goal is to push out a uniquely named Project Entry file, from the "PM Template" to each project manager, and have it populated with three of the cells already populated with the correct data.

    I am new to VBA, but here are the specifics on what I would like to do with VBA:

    Run script from "PM Master.xlsx"
    VBA script prompts for ID # (Note: this is the unique identifier I assigned to each row in the "PM Master.xlsx" file).
    VBA makes a copy of the "PM Template" sheet and renames it as a new workbook using a name created by concatenating the cell values of the following columns of the row indicated from the ID entered:
    ----- "PM", "ID#", "Project Name"
    VBA then copies the cell values of the indicated columns in that row and pastes them into specific cell locations of the newly created workbook, as follows
    ____ Value in "PM" gets pasted to cell C8 of the new file
    ____ Value in "ID#" gets pasted to cell C5 of the new file
    ____ Value in "Project Name" gets pasted to cell C6 of the new file.
    VBA saves and closes the newly created file.

    Note: It would be nice to run this as a batch as well so that I can knock out all the file creations at once. However, the critical factor is how to add a file after the batch has run; after all I don't want to overwrite existing files when I only need to create a single file. I guess the ideal would be to have both options available (Batch or Single file based on ID#). However, if I had to choose one, I would choose the single file creation version.

    I searched and tried to solve this from existing knowledge captured in previous postings but there are just to many moving parts in this for a VBA novice like myself to figure this out.

    Thank you in advance!
    Last edited by stannard; 07-11-2012 at 08:19 PM.

  2. #2
    Forum Expert Tinbendr's Avatar
    Join Date
    06-26-2012
    Location
    USA
    MS-Off Ver
    Office 2010
    Posts
    2,125

    Re: new workbook (WB) from template sheet, name new WB from cell val, then write data from

    Welcome to the board!

    Could you supply a sample workbook with sample data? You can attach it with Manage Attachments, in the Go Advanced editor.
    David
    (*) Reputation points appreciated.

  3. #3
    Registered User
    Join Date
    07-09-2012
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    3

    Re: new workbook (WB) from template sheet, name new WB from cell val, then write data from

    Thanks for the Welcome David!

    I deleted all the data and add a few rows of fictitious data, but here it is. I have a 3 data validation cells in the template (with source data unhidden), which I constrained to the sheet, hoping that these definitions will carry to the newly created sheet, but I'm not sure if that is going to work so I may have to experiment with that a bit.

    Once we get the VBA code sorted out and working to created the new files from the PM_Template sheet, I'll write the formulas to associate the cells in each row of of PM_Rollup (Pull in), from Column G to Column AK, with the corresponding file and cell. For example, Cell G4 in the PM_Rollup sheet of PM_Master will pull the data from cell c9 of the VBA created file "JohnSmithE6-4Pelican Road Pipeline.xlsx". When the PM updates their file it is simultaneously reflected in the PM_Master file.

    Thank you.
    Attached Files Attached Files

  4. #4
    Forum Expert Tinbendr's Avatar
    Join Date
    06-26-2012
    Location
    USA
    MS-Off Ver
    Office 2010
    Posts
    2,125

    Re: new workbook (WB) from template sheet, name new WB from cell val, then write data from

    1. Button on Project Sheet
    2. I rename the destination sheet to Project. Change as required.
    3. Change the path of the destination saves as required.

    Hope you like it.
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    07-09-2012
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    3

    Re: new workbook (WB) from template sheet, name new WB from cell val, then write data from

    Wow, David. Thank you so much. I changed the directory in the source code and it worked like a champ. I can't wait to get started on using this now.
    That was some amazing work, especially since it was written perfectly on the first take!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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