+ Reply to Thread
Results 1 to 8 of 8

how to use macro (split sheets/tab) to all open workbooks

  1. #1
    Registered User
    Join Date
    04-20-2015
    Location
    Kingdom of Saudi Arabia
    MS-Off Ver
    EXCEL 2013
    Posts
    6

    how to use macro (split sheets/tab) to all open workbooks

    hi everyone,

    i have this macro copied from the internet. i would copy and paste this in the VBe to split the sheets in the same workbook.
    can this macro be saved in a template, so I can open simultaneously this template and the the workbook which i need to split the sheets?
    my other macros are working while using the shortcuts. thanks.

    VBA: Split a workbook into multiple workbooks and save in the same folder.


    Please Login or Register  to view this content.
    Last edited by JBeaucaire; 04-20-2015 at 01:57 PM. Reason: Added missing CODE tags. Please read and follow the Forum Rules, link above in the menu bar. Thanks.

  2. #2
    Forum Expert p24leclerc's Avatar
    Join Date
    07-05-2010
    Location
    Québec
    MS-Off Ver
    Excel 2021
    Posts
    2,081

    Re: how to use macro (split sheets/tab) to all open workbooks

    You should copy this macro to your "Personal Macro Workbook". This way, this macro will always be available.
    You should see this Personal Macro Workbook in VBE.
    If not, the easy way is to Record a new macro and in the Record macro window to choose to store it in your Personal Macro Workbook.
    This should create the file and you'll be able to copy your macro to it.
    Pierre Leclerc
    _______________________________________________________

    If you like the help you got,
    Click on the STAR "Add reputation" icon at the bottom.

  3. #3
    Registered User
    Join Date
    04-20-2015
    Location
    Kingdom of Saudi Arabia
    MS-Off Ver
    EXCEL 2013
    Posts
    6

    Re: how to use macro (split sheets/tab) to all open workbooks

    Thanks @JBeaucaire for the code tags, appreciate it. I will remember next time on my posts.

    Thanks @p24leclerc for the quick reply. I have these macros, only 4 of them, saved separately in workbooks I named as Template ("Personal Workbook" as you called it). For example, I have this macro to delete specified range of cells in an active worksheet template opened. Then I would open another workbook on which the worksheet I would use my macro. I'd just click on this worksheet and use my keyboard shortcuts assigned to this macro. Voila. It's done. The others are Print the whole workbook and rename the sheet tabs macros, are working while I opened these templates. The simplest thing I'm doing is have this 2 workbooks opened only on my desktop. Nothing more. My code for splitting tabs just won't work like the above. I have this code saved in notepad on my desktop and every time I would split any workbook, I will would always copy it and paste in the VBE. Also I've tried to make a template/personal workbook for it and opened it together with another workbook, but it will split its on tab and NOT on the other workbook which is intended.
    Last edited by 2977cc; 04-21-2015 at 05:37 AM.

  4. #4
    Forum Expert p24leclerc's Avatar
    Join Date
    07-05-2010
    Location
    Québec
    MS-Off Ver
    Excel 2021
    Posts
    2,081

    Re: how to use macro (split sheets/tab) to all open workbooks

    The Personal macros workbook is not visible and, as far as I know, you can't work on it. It just contains a bunch of utilities macros that you store there because you want to use them very often and on different workbooks.
    It is related to an xlsb workbook called Personal.xlsb and it is located in your \Users\AppData\Roaming\Microsoft\Excel\XLSTART folder.
    It should already be there or you can create one by recording a macro to it. If the Personal.xlsb file does not exist it will be created. You'll then be able to store your macros in that place and use them on any other workbook. Make sure your macro code works on the active workbook or active sheets. If you name specific workbooks or sheets in your code, the macro may not find it if you use it on another workbook or sheet.
    Hope this helps

  5. #5
    Registered User
    Join Date
    04-20-2015
    Location
    Kingdom of Saudi Arabia
    MS-Off Ver
    EXCEL 2013
    Posts
    6

    Re: how to use macro (split sheets/tab) to all open workbooks

    thanks again for the quick reply.

    I've created my personal workbook, with reference to Mr. Ron de Bruin's web page. I did paste above the code on my personal workbook and saved it. Next I opened the file which I need to split the sheets to individual files, opened the VBE and I also saw my code as expected, however, I run it. The result was the personal workbook's sheet was split and not the file which I plan to. I guess I have to tinker on the code first. Thanks again for the personal workbook idea, I will be working on it.
    "Never stop learning: Self-Made-VBA-Dude"

  6. #6
    Forum Expert p24leclerc's Avatar
    Join Date
    07-05-2010
    Location
    Québec
    MS-Off Ver
    Excel 2021
    Posts
    2,081

    Re: how to use macro (split sheets/tab) to all open workbooks

    your only problem is to use "ThisWorkBook" instead of "ActiveWorkBook" in your loop statement.
    Change your code to this one:
    Please Login or Register  to view this content.
    By using ThisWorkBook, you were actually working on the Personal.xlsb workbook.

  7. #7
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: how to use macro (split sheets/tab) to all open workbooks

    I think you should take a look at Work Space saving function. It will let you save the layout/postion on numerous excel files, and then open them in the exact same postion.

    Add this option to the ribbon...click Excel Options (in the circle, top left corner, or the dropdown arrow next to the Save icon)
    click Customise/All Commands (click Popular DD), click any option and press S/scroll down until you see the Save WorkSpace/click Add
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  8. #8
    Registered User
    Join Date
    04-20-2015
    Location
    Kingdom of Saudi Arabia
    MS-Off Ver
    EXCEL 2013
    Posts
    6

    Re: how to use macro (split sheets/tab) to all open workbooks

    @p24leclerc, Thanks for the suggestion, with that simple correction you made I also made 1 more macro to work.

    @FDibbins, i'm not so sure about following your procedure...

    Anyway, my Excel looks different now, with a little tab customized, with personal macros added under Quicktime Tab.

    Capture.JPG


    Merci.
    Last edited by 2977cc; 04-23-2015 at 11:48 AM.

+ 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. Macro Code to keep Formulas in Split Workbooks
    By Phil_Lloyd_1979 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 09-03-2014, 10:27 AM
  2. Split this up over different workbooks or sheets.
    By a1b2c3d4e5f6g7 in forum Excel General
    Replies: 1
    Last Post: 07-31-2014, 01:42 PM
  3. [SOLVED] Copy sheets to all open workbooks ??
    By james 35 in forum Excel Programming / VBA / Macros
    Replies: 14
    Last Post: 01-31-2013, 06:26 AM
  4. [SOLVED] Split Macro modification to Split into new Workbooks instead of sheets within one workbook
    By DLSmith in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-13-2012, 08:11 PM
  5. [SOLVED] How to split and consolidate sheets into new workbooks
    By Xluser@work in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 01-21-2006, 05:17 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