+ Reply to Thread
Results 1 to 5 of 5

how to change this macro so it makes sheets in a new book not in the current book

  1. #1
    Registered User
    Join Date
    10-07-2013
    Location
    yorkshire, england
    MS-Off Ver
    Excel 2010
    Posts
    23

    how to change this macro so it makes sheets in a new book not in the current book

    This Macro is working but i want the new sheets to made in a new workbook
    2 things i'd like to change in this macro.

    1) Can it create these new worksheets in a new workbook.
    2) Either can the new sheets have an ascending number in A1 or can the sheets name be copied into H1. Both options have the same result H1 is a lookup based on A1 value.

    Please Login or Register  to view this content.
    I found this function for adding a worksheet name to a cell, thank you Kevin B
    HTML Code: 
    http://www.excelforum.com/excel-general/554727-make-a-cell-equal-a-worksheet-name.html
    Please Login or Register  to view this content.
    But i've not tried using a function and a sub in 1 code before?

    The reason for this is there is a drop down box on the original sheet so you can choose a walk name it then does lots of lookups and shows you all the details for that walk. But to print you have to select each walk separately. With this macro you can select print entire workbook and it should save a lot of time.

    Thanks in advance
    Last edited by gavwalsh; 02-28-2015 at 08:30 AM. Reason: Hopefully a clearer title

  2. #2
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: how to change this macro so it makes sheets in a new book not in the current book

    Maybe this:

    Please Login or Register  to view this content.
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    “None of us is as good as all of us” - Ray Kroc
    “Actually, I *am* a rocket scientist.” - JB (little ones count!)

  3. #3
    Registered User
    Join Date
    10-07-2013
    Location
    yorkshire, england
    MS-Off Ver
    Excel 2010
    Posts
    23

    Re: how to change this macro so it makes sheets in a new book not in the current book

    Thank you. I'm just going out now but I will try it in a few hours.

  4. #4
    Registered User
    Join Date
    10-07-2013
    Location
    yorkshire, england
    MS-Off Ver
    Excel 2010
    Posts
    23

    Re: how to change this macro so it makes sheets in a new book not in the current book

    It works exactly as I asked it to, thank you. Unfortunately the formulas aren't pulling through correctly. I've tried changing the formulas but only the lookup from A1 works, the numbers are ranked from H1 and not numerical.
    Can A1 be a number sequence? The first sheet "1" the next sheet "2"

  5. #5
    Registered User
    Join Date
    10-07-2013
    Location
    yorkshire, england
    MS-Off Ver
    Excel 2010
    Posts
    23

    Re: how to change this macro so it makes sheets in a new book not in the current book

    It works! Thanks so much for your time. I changed the vlookup to an index with match formula.

    Thank you

+ 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: 2
    Last Post: 04-15-2013, 09:06 AM
  2. [SOLVED] Is is possible to run a macro on workbook change?
    By Rob* in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-09-2013, 07:20 AM
  3. Replies: 1
    Last Post: 03-16-2012, 02:31 PM
  4. Use a macro in one workbook to change another workbook?
    By scubadiver007 in forum Excel Programming / VBA / Macros
    Replies: 14
    Last Post: 03-02-2011, 10:20 AM
  5. How to change the Sheet name of a workbook from a macro present in another workbook
    By ananthakumar.e in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-13-2008, 05:57 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