+ Reply to Thread
Results 1 to 10 of 10

Populate template in new sheet when new row is added on master data sheet

  1. #1
    Registered User
    Join Date
    07-17-2020
    Location
    New York
    MS-Off Ver
    2020
    Posts
    24

    Populate template in new sheet when new row is added on master data sheet

    Hello,

    I am fairly new to working with macros but my boss wants me to organize a system for him...

    The goal is, have one sheet with a table that has categories, example: Name, Date, Location, Time, Type. When a new row is added with name, date, location, time I want excel to create a new sheet with a template that will be pre made by me, that contains the name, date, location data imported from the master data sheet. The tricky part is, there for will be 3 different templates. It would work great if the category on the data sheet, "Type" will have 3 options and if they choose 1 then the data will be copied to my first template, if they choose 2 it will be copied to my second template and so on.

    Then I have to have the option on each template to "Download as PDF" BUT it must prompt the user which location they want to save to because this sheet will be accessed from multiple computers that all have to save it as a PDF.

    Lastly it would be great if the data could connect with the outlook. Whoever input the data in the excel sheet could click a button that generates an email with the information from the row and prompts them to accept an event at the time from the row.

    If someone could help me or offer advice I would very much appreciate it! Thanks

    UPDATE: I have attached the VERY ROUGH draft of the excel sheet. The data sheet columns that are highlighted is the data that I need to be put in to a copy of the "Pressure Test Template".
    I apologize for the disorganized form it still has a lot more formatting to be done. Thank you thank you!
    Attached Files Attached Files
    Last edited by lexipants33; 07-17-2020 at 02:04 PM.

  2. #2
    Valued Forum Contributor
    Join Date
    08-03-2012
    Location
    Newcastle
    MS-Off Ver
    Excel 2007, 2010, 2013, 2016, Office 365
    Posts
    399

    Re: Populate template in new sheet when new row is added on master data sheet

    Hi

    A bit more sample data would help.
    I tweaked your file a bit.

    Here's a starting point for you.
    see attached file

    If this is of any help, please let me know.
    If not, let me know anyway.

    zeddy
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    07-17-2020
    Location
    New York
    MS-Off Ver
    2020
    Posts
    24

    Re: Populate template in new sheet when new row is added on master data sheet

    Hello, thank you very much this is a great starting point. It successfully populates data from the first line in the data sheet into the Pressure Test Template. Except when I try to click "Select Folder..." I get an error, "Run-time error '91': Object variable or With block variable not set". Also if I try to click on "Show Form..." it give me the error, "Run-time error '1004': Application-defined or object-defined error". Do you know what I am doing wrong? I am a beginner so the answer may be obvious and I am sorry for that!

    I have added 2 rows of data so everyone can see what an example entry would look like on the data sheet. I am very pleased that the data sheet top row populates the template, but how could I have it so every new row will populate the template. For example, if row one is filled out it populates the template and the person can save the pdf. Then they go to the second row and insert data into that row, could that row populate the template after the first one has? This data sheet is meant to be on going and will have many rows of data entered in and I would like each new row to populate the template. I have also added a second (rough) template that I would also need populated. Is it possible for the response in the data sheet column C, "Request Type" to dictate which template the data goes to. If "Pressure Test Only" is written for request type then the "Pressure Test Template" will be populated. If "Final Walk Down Only" is written for request type then "Final Walk Down Template" will be populated. If "Pressure Test & Final Walk Down" is chosen then both will be populated.

    Thank you times a million
    Attached Files Attached Files

  4. #4
    Valued Forum Contributor
    Join Date
    08-03-2012
    Location
    Newcastle
    MS-Off Ver
    Excel 2007, 2010, 2013, 2016, Office 365
    Posts
    399

    Re: Populate template in new sheet when new row is added on master data sheet

    Hi

    Not sure what version of Excel you are using, what operating system you are using (e.g. Windows 10 ??) and where your files are located.
    Hopefully it is not a web-based set-up??? Please let us know. It helps.

    Save the attached v2a sample file to a folder of your choice for testing (e.g on local C: drive folder???)

    Then open that saved file, with macros enabled.

    Thanks for the extra sample data. Extra is always good.

    There are a few ways we can get the 'last record row' for each corresponding Report type.
    As an example, I've added some formulas in columns [AG] and [AG] on [Data Sheet]

    This allows us to 'fetch' the record data for the last entry according to Report Type, and place this in the template.

    Check the template sheets to see the formulas we can use to fetch the relevant data from the last-corresponding-report-type-data-record-row.

    I'va added a couple of tweaks to the Form, to show which Report Template is being used etc etc etc.

    Lets see how this works out so far.

    Please let us know if you get debug errors.
    I have tested this on a local drive and it seems to work ok here.

    zeddy
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    07-17-2020
    Location
    New York
    MS-Off Ver
    2020
    Posts
    24

    Re: Populate template in new sheet when new row is added on master data sheet

    Hi zeddy,

    Im using excel version 16.38. It is not a web based set up it is on my desktop. I probably should have mentioned that I am on a Mac, it is all that I have. It is Mac OS Catalina and version 10.15.5. In the code, ".Title = saywhat" gets highlighted when I debug. Do you know why this could be? I hope I am not just SOL because I am on a Mac.

    lexi

  6. #6
    Valued Forum Contributor
    Join Date
    08-03-2012
    Location
    Newcastle
    MS-Off Ver
    Excel 2007, 2010, 2013, 2016, Office 365
    Posts
    399

    Re: Populate template in new sheet when new row is added on master data sheet

    Hi lexie

    You're in the big Apple, big home of Apple users. You are using a Mac.
    I should've seen that.

    Office 2011 for the Mac is always 32 bit.
    Office 2016 for the Mac can be 32 or 64 bit.
    If the Mac build is 15.26 or later, then it is 64-bit.

    Windows can have 32-bit Excel or 64-bit Excel.

    This all makes a big difference when using VBA.

    I can easily add code to the workbook to detect whether it's running on a Mac or not, and 32-bit or 64-bit.

    Then we would have to deal with the different ways they behave.
    Apple uses a significantly different way of dealing with folders.

    This is why you were having an issue with my windows-based sample file.

    Do you have ANY Users that work with Windows PCs???
    Or are they ALL using Macs?? Is there a mix of versions???

    Please advise.

    I'll have a look at making a v3 sample version to tackle this.
    In the meantime, extra info is still always welcome

    zeddy

    PS Our UK Gov are subsidising meals out next month..
    ..which makes the UK the CHEAPEST PLACE IN THE WHOLE WORLD FOR A BIG MAC
    ..just saying

  7. #7
    Registered User
    Join Date
    07-17-2020
    Location
    New York
    MS-Off Ver
    2020
    Posts
    24

    Re: Populate template in new sheet when new row is added on master data sheet

    Yes.. the big home of Apple Users. There is a mixture here at my work, some use mac and some use windows. Also they use tablets. I would like it to work on a tablet as well if possible. I am currently updating the template so the cells have changed, it is attached.

    And good for you about the big mac! Go UK

  8. #8
    Valued Forum Contributor
    Join Date
    08-03-2012
    Location
    Newcastle
    MS-Off Ver
    Excel 2007, 2010, 2013, 2016, Office 365
    Posts
    399

    Re: Populate template in new sheet when new row is added on master data sheet

    Hi lexie

    it is attached
    is it still on your Mac?

    zeddy

  9. #9
    Registered User
    Join Date
    07-17-2020
    Location
    New York
    MS-Off Ver
    2020
    Posts
    24

    Re: Populate template in new sheet when new row is added on master data sheet

    Hi, yep it was still on my mac... here u go
    Attached Files Attached Files

  10. #10
    Valued Forum Contributor
    Join Date
    08-03-2012
    Location
    Newcastle
    MS-Off Ver
    Excel 2007, 2010, 2013, 2016, Office 365
    Posts
    399

    Re: Populate template in new sheet when new row is added on master data sheet

    Hi lexi

    Thanks for that.
    - we have a major incident here with phones and broadband out.
    Mobile phone's OK
    ..engineer is visiting

    will respond as soon as everything here is fixed

    zeddy

+ 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. Populate “template” sheet from data in “source” sheet
    By eddiecruz in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 11-22-2016, 03:08 PM
  2. Master sheet into template, copy template to sheets named after master column.
    By M4R1N3R in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-26-2015, 12:24 PM
  3. To populate data to a new sheet from a master sheet.
    By saptarshi27 in forum Excel General
    Replies: 1
    Last Post: 07-22-2014, 01:01 PM
  4. Want to auto populate an excel sheet from a master sheet of data
    By M>E> in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 06-07-2014, 03:34 PM
  5. Replies: 0
    Last Post: 04-16-2013, 07:29 AM
  6. Macro to populate a word template and feed an excel master sheet
    By hieldy in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-05-2013, 05:40 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