+ Reply to Thread
Results 1 to 12 of 12

Need help for creating new sheet with predetermined tabular column in it

  1. #1
    Registered User
    Join Date
    07-29-2013
    Location
    India
    MS-Off Ver
    Excel 2002
    Posts
    50

    Wink Need help for creating new sheet with predetermined tabular column in it

    Dear Experts,

    At first, my sincere thanks to Holger and all other experts for extensive support provided. I need to develop a master to

    maintain customer information, of which the excel spread sheet is attached.

    It is already containing event macro, which does the following

    1. Sheet1 - Column G is the master list of customers, which contains existing customer names.

    2. For each of the existing customers, there is a separate sheet available, namely - Spencer, TATA, Ford, Kiwi.

    3. The existing sheets - Spencer, TATA, Ford, Kiwi - cells A1 to R3 are password protected.

    3. The customer name will be typed in sheet1 cell B5. If the name is in the existing list of customers in column G, then

    excel should take me to the respective customer sheet (This function is not happening now, and for this also I need your

    support)

    4. If the customer name is not available in the column G, then it will be added in the column G, and a new sheet in the name

    of new customer will be created at the end of the workbook.


    Now my requirements are

    1. The newly created sheet to automatically contain the tabular columns as in the existing sheets

    2. Newly created sheet with tabular column should automatically protect the cells A1 to R3.

    3. In the newly created sheet, Cell D1 to I1 to be merged, and it should show the name of the sheet (Name of the customer)

    automatically.

    4. If the name of the customer typed in the sheet1 is already existing in the column G, then excel should go to the specific

    sheet having the name of the customer typed.


    I know just copy and paste macro codes. I fully depend on experts like you for this. Can you please support to provide the

    macro code for the above functions?


    With sincere thanks for all

    gm2612

    Data Sheet1.xls

  2. #2
    Forum Guru jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Need help for creating new sheet with predetermined tabular column in it

    Hi gm2612

    This Code in the attached appears to do as you require. Please notice the Named Range "Customers" and the Hidden Sheet "Template". Change the Password as required.

    In the Workbook Code Module
    Please Login or Register  to view this content.
    In Sheet1 Code Module
    Please Login or Register  to view this content.
    Attached Files Attached Files
    John

    If you have issues with Code I've provided, I appreciate your feedback.

    In the event Code provided resolves your issue, please mark your Thread as SOLVED.

    If you're satisfied by any members response to your issue please use the star icon at the lower left of their post.

  3. #3
    Registered User
    Join Date
    07-29-2013
    Location
    India
    MS-Off Ver
    Excel 2002
    Posts
    50

    Re: Need help for creating new sheet with predetermined tabular column in it

    Hello Jaslake,

    Wishing you a heppy new year. I am sorry for the late reply. The site was not opening in my computer for around a week. Now, I used your sheet, and it works just perfect.

    By the way, if I want to open a new work book in the same folder (instead of a sheet in same file), can you please support with the code that I would have to add?

    Thanks for the help.

    Regards,
    gm2612

  4. #4
    Forum Guru jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Need help for creating new sheet with predetermined tabular column in it

    Hi gm2612

    What will be the Name of the "Other" Workbook?

    Will each Customer be a separate Workbook of will they all reside as Worksheets in the still to be identified "Other" Workbook?

    Where will the "Template" Worksheet live, in the Data Sheet Workbook or in the still to be identified "Other" Workbook.

  5. #5
    Registered User
    Join Date
    07-29-2013
    Location
    India
    MS-Off Ver
    Excel 2002
    Posts
    50

    Re: Need help for creating new sheet with predetermined tabular column in it

    Hello Jaslake,

    Yes, each customer will have a separate workbook

    "Template" will be in the main workbook, in which first sheet - "Sheet1" will have the cell B5 where the customer name will be typed. (Column G will have master list of existing customers, as indicated above in the first post.) Let us say, the sheets "Sheet1" & Template together live in workbook "Data Sheet1 v1.xls"

    The new workbooks to be named with the customer names. (In the existing codes you have helped, if the customer is a new customer, a new worksheet in the name of the new customer is created. Similarly, instead of new sheet, a new work book in the name of the customer to be created)

    Location of all these files will be in "\\ws1\myDocs\gmk\My Documents\Excel trials"

    Can you please help me out?

    Thanks and regards,
    gm2612

  6. #6
    Forum Guru jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Need help for creating new sheet with predetermined tabular column in it

    Hi gm2612

    The Code in the attached has been modified to do this
    if I want to open a new work book in the same folder (instead of a sheet in same file), can you please support with the code
    . The Code assumes that the Newly Created Customer Files will live in the same Folder as Data Sheet Workbook.
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    07-29-2013
    Location
    India
    MS-Off Ver
    Excel 2002
    Posts
    50

    Re: Need help for creating new sheet with predetermined tabular column in it

    Hello Jaslake,

    Thank you so much for the valuable help you have done to me. The sheet you have provided is more than perfect. I thank you so much again.

    boy_scout_salute.jpgRose-Flowers-Bouquet-1.jpg



    With Warm Regards,
    gm2612

  8. #8
    Forum Guru jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Need help for creating new sheet with predetermined tabular column in it

    You're welcome...glad I could help. Thanks for the Rep.

    Thanks for the Salute and Flowesrs...

  9. #9
    Registered User
    Join Date
    07-29-2013
    Location
    India
    MS-Off Ver
    Excel 2002
    Posts
    50

    Re: Need help for creating new sheet with predetermined tabular column in it

    Hello Jaslake,

    Offlate, I thought of two more requirements in the same file. One is that enabling autofilter in the column B of the new workbooks created, and second is protecting both new and "Data Sheet 1 V2.xls" workbook. Can you please provide any help on the same?

    Thank you so much in advance.
    Regards,
    gm2612

  10. #10
    Forum Guru jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Need help for creating new sheet with predetermined tabular column in it

    Hi gm2612

    The Code in the attached has been modified to do this
    enabling autofilter in the column B of the new workbooks created, and second is protecting both new and "Data Sheet 1 V2.xls" workbook
    Attached Files Attached Files

  11. #11
    Registered User
    Join Date
    07-29-2013
    Location
    India
    MS-Off Ver
    Excel 2002
    Posts
    50

    Re: Need help for creating new sheet with predetermined tabular column in it

    Dear Jaslake, Thank you so much for your help.

    7020c_American-Spirit-Flower-Bouquet.jpg

  12. #12
    Forum Guru jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Need help for creating new sheet with predetermined tabular column in it

    You're welcome...glad I could help.

+ 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. In Pivot Tabular - how to set year & column filter in accending order
    By bsaimsc in forum Excel Charting & Pivots
    Replies: 3
    Last Post: 11-03-2013, 12:46 AM
  2. Generate Summary Report in a tabular form from the Input Sheet
    By excelhelp18 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 08-30-2012, 12:52 PM
  3. [SOLVED] Code Creating new folder with predetermined filename
    By ufopilot3 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-18-2012, 01:00 PM
  4. Sum Across predetermined sheet range
    By pauldaddyadams in forum Excel General
    Replies: 2
    Last Post: 01-17-2012, 09:52 AM
  5. Importing DATA in tabular form fromone sheet to another sheet
    By rahulk in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-08-2010, 09:59 PM

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