An excel file without macros conundrum

[garyi]

Hello everyone.

I have a great little excel stock taking sheet, allowing the user to create from a hidden sheet any number of new sheets for different areas for stock taking, for instance, dry stores, fridge, freezer etc.


Once the user has entered the amounts of stock for each area a click of a macro button runs a macro that runs through all the sheets and picks out specific data to summarise the data, from hidden cells on each sheet.

The issue is within our organisation we have excel 2003 through 2013, some are on our computers, some on client computers, some have macros enable by default, some have all macros disabled etc etc, its a nightmare. All in all I need to create a sheet without macros.

Basically I can do the main bulk of it without bother, except that the user needs to be able to create new stock locations from a master sheet which means there will not be a defined amount of specifically name sheets. So is there anyway therefore to create a formula or similar to run through the sheets which could be 2 through to 20 and summarise the data as the macro has done?


Any help gratefully received!

[alansidman]

If you upload a sample of your worksheet, then someone here may be able to offer up a solution. Without seeing what you want, it is virtually impossible to advise you.

Attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are demonstrated, mock them up manually if needed. Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

[garyi]

HI there.

I certainly will, however to be honest I am struggling with the first hurdle!

In my current sheet I have a hidden 'template' stock sheet, the user currently clicks a macro button to generate a new sheet from this template, I don't even think there is a way to do this without macros?

Currently macros do the following:
generate a copy of a hidden worksheet
Cycle through the worksheets picking out information from specific cells, adding them up and presenting them on a summary sheet
Select only cells with content for printing.


I guess the question is, could I achieve any of this without macros? If the answer is no then I am a bit stuffed!

[garyi]

I suppose another option would be can the file somehow at least invoke the 'turn on macros' option in excel, I have seen for myself that in some set ups such as 2010 you don't even get the little yellow bar saying allow content, because within the many layer deep options, macros are disabled. On some client systems you cannot change this without admin, and to be frank some of our users would struggle anyhow.

[alansidman]

Maybe something like this would help.

http://www.datapigtechnologies.com/f...rcemacros.html

[garyi]

Thanks for that, it might be just the answer, for the most part any how, for those on corporate computers, I guess they'll have to speak with the admins