macros need to be available to all users

[ChloeS]

Hello
Please can you help urgently....?
I need to send out hundreds of spreadsheets to individual people within my company, and would usually do this by pivoting the data, splitting this out into separate worksheets (by manager name), and then using a macro to split the worksheets into separate excel files. The problem I have is that the worksheets each need to have macros assigned so that the user can hide, then unhide certain columns, but when the macro splits out the sheets into separate files, the macros to hide/unhide columns don't work as they are referencing the original workbook.
Is there any way round this - please help asap!!
[The end users may have different versions of Excel (2003 or 2007) and some will have macro security at high and some at medium so there may be issues there, but the main one is how to get this data out to all these individuals with the ability for them to click a button to view/hide data.]

Thanks in advance

 

[xenou]

Hi,

My low-tech solution to this sort of thing is to save the entire workbook rather than just a sheet - then work the other way and delete the sheets you don't need. What remains is the sheet you want plus the workbook with the macros still in it. Of course you do lose any worksheet code in the worksheets you delete - but not in the standard modules.

Sample code might be:

[COLOR="Navy"]Sub[/COLOR] Bar()
[COLOR="Navy"]Dim[/COLOR] wb [COLOR="Navy"]As[/COLOR] Workbook
[COLOR="Navy"]Dim[/COLOR] ws [COLOR="Navy"]As[/COLOR] Worksheet
[COLOR="Navy"]Const[/COLOR] fPath [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR] = "C:\myTemp\Test.xlsm"

    [COLOR="SeaGreen"]'//Delete any existing copy of destination workbook[/COLOR]
    [COLOR="Navy"]On[/COLOR] [COLOR="Navy"]Error[/COLOR] [COLOR="Navy"]Resume[/COLOR] [COLOR="Navy"]Next[/COLOR]
    Kill fPath
    [COLOR="Navy"]On[/COLOR] [COLOR="Navy"]Error[/COLOR] [COLOR="Navy"]GoTo[/COLOR] 0
    
    [COLOR="SeaGreen"]'//Save Copy of This Workbook[/COLOR]
    ThisWorkbook.SaveCopyAs fPath
    
    [COLOR="SeaGreen"]'//Open the copy and Delete all sheets except Sheet1 and Sheet2[/COLOR]
    [COLOR="Navy"]Set[/COLOR] wb = Workbooks.Open("C:\myTemp\Test.xlsm")
    [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] ws [COLOR="Navy"]In[/COLOR] wb.Worksheets
        [COLOR="Navy"]Select[/COLOR] [COLOR="Navy"]Case[/COLOR] ws.Name
            [COLOR="Navy"]Case[/COLOR] "Sheet1", "Sheet2"
                [COLOR="SeaGreen"]'//Do Nothing[/COLOR]
            [COLOR="Navy"]Case[/COLOR] [COLOR="Navy"]Else[/COLOR]
                Application.DisplayAlerts = False
                ws.Delete
                Application.DisplayAlerts = True
        [COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Select[/COLOR]
    [COLOR="Navy"]Next[/COLOR] ws
    
    [COLOR="SeaGreen"]'//Save and close the copy[/COLOR]
    wb.Save
    wb.Close False


[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Another low-tech solution would be to have a "template" workbook with the macro code in it. Then rather than copying worksheets to new workbooks, copy them to your "template" workbook. Then they are ready to go. The "hi-tech" solution, if you will, would be to write code to actually move or copy your code from one workbook to another. There are some examples on the web, but I rarely do this myself so I don't have any ready examples to give you from my own experience. It's not really all that complicated, but I haven't bothered to work coding to the VBE into my regular set of VBA skills yet.

 

[ChloeS]

Thanks for this, i like 'low tech' solutions! Still can't see how I can apply this quickly for hundreds of different worksheets, as they will all need the same file name for the macro to work wouldn't they?
Then I'd have trouble identifying them - whose file is whose?
When I split the worksheets into separate workbooks I ideally want the workbooks named as the original worksheets name so we can identify who to send them to. Even if I managed to get the code to copy data to template workbook and then email the workbook to the recipient (? How - not very experienced in vba as you can tell!), I would need to save the files as different names so we have a record of them.
Any ideas?

 

[xenou]

You can certainly use the sheet names as the base for the file names, and macros can run in workbooks regardless of the name of the file (depending on how you write the macro). At this time, it's a little too big of a project for me to show you all of those steps. I would start with one workbook as your test so you can figure out the procedure. In principle, once you can do one workbook, it's just a matter of creating a loop to do all of them. Although anything involving hundreds of workbooks is potentially messy - I'd try to keep the whole thing as simple as possible.