convert macro from defined sheet to any or current

[AkaTrouble]

hiya

have found a piece of code that works well but has a defined sheet name it works on

i would like to change it so it works on any worksheet or active worksheet

have tried changing in different ways to .ActiveWorksheet with no luck

currently it is called from sheet update on the named sheet

i want to copy that sheet as a master sheet and be able to change copy and the macro to run on field B3 updating

thanks for help

Please Login or Register  to view this content.

[PCI]

See next code for the active sheet

Please Login or Register  to view this content.

[Tinbendr]

Please Login or Register  to view this content.

[AkaTrouble]

thanks very much for the reply David

created this as a new module and for some reason is returning error at

Please Login or Register  to view this content.

this is the field that has date entered

edit

error returned run-time error '91':

object variable or with block variable not set

[AkaTrouble]

tried a few variations but my VBA knowledge to limited

will attach file

aim is to create a new month sheet and the next based on todays date when workbook opened

this part works well and it copies the master sheet to new sheet with name of month

the code above i was or am trying to create the correct calendar layout for the new sheet (on changing date in B1)

it might be issue in the sheet being created automatically i am lost so thanks for looking

[Tinbendr]

When you assign a range to a variable, you have to use set.

Please Login or Register  to view this content.

[AkaTrouble]

When you assign a range to a variable, you have to use set.

Please Login or Register  to view this content.

this little bit of code helped make the macro option work properly

Thank You very much

will mark post solved as original question has been sorted

will try to workout how to automate it too so it runs code on sheet creation

[Tinbendr]

will try to workout how to automate it

I guess I'm not understand how you plan to use this.

The way it's written now, when ever you change the value in B1 on the activesheet, the macro will run.

[AkaTrouble]

yes if the call statement is added to the sheet code

however the "on open" = create sheet automatically does not add this the the sheet code

so it will not call the macro based on the update on the new pages

so there is no on update code for the new sheet

hope this makes sense

[Tinbendr]

hope this makes sense

Yes, it does. and this is going to get complicated quickly.

What about a Ribbon control that you can click that would add a page on demand, based on the current sheet? That code will be in a module code page and wouldn't have to be duplicated.

I don't think you need an Open event because every time the workbook is opened, it would update the last active sheet.

I guess I'm not really sure how you are going to use this. I mean, I know it's a calendar, but wouldn't you only need a new month on demand? Or you could check to see if all the months are created and add them if necessary.

[AkaTrouble]

i agree it has already got complicated but glad you replied as think you gave solution

the idea was to automatically create the new month for the user from the master sheet which could contain other fixed data required not always associated directly to the date creation etc

but think simple solution will be to create a button on the 'master' sheet that runs the macro in theory the button should be copied as part of the new sheet creation

also i apologise for the complication it was a project to teach me how to use a lot of the VBA functions along with the events etc

i am learning how at the expense of the time of the contributors .. for this i am grateful and appreciate the help given .. and hope i haven't breached the rules or spirit of the forums

thanks again

[AkaTrouble]

Update

my above possible solution to creating button on master sheet does not work

creating a button on 'master' sheet does work to run macro and updates the 'master' sheet

the on opening event that creates the new page copies all the sheet contents but not the button

seemed like a solution but alas

[Tinbendr]

OK, here is my solution.

Look for TOOLS next to the File Tab. In that, you will find a insert calendar button. Selecting it will add a calendar, based on the master. I put a Today() formula for B1. That way, the macro will create a calendar based on Today's date.

Give it a look and see if you like it.

[AkaTrouble]

firstly thanks for coming back and following up on post that in itself is awesome

secondly that is a very good solution and works really well so thanks for that to

was a little concerned that i was maybe over asking on question etc but feel more comfortable to now



although i like the Today() option for date is there anyway that could ask for date or have second button to create Next Month

reason for asking for this is that while working on this month or towards end of month can carry forward to following

please feel free to ignore you have already gone above and beyond

many many thanks

[Tinbendr]

OK, how about his. I added another button for the next month. And instead of having the Today(0 formula, the code just inserts either Today's date, or Today's date + 30.

We can always use the Workbook_Open event to automatically keep one (or more) month ahead of the date opened, instead of having the user to manually do it.

[AkaTrouble]

yepp new button works great

will have to do some tweaking on the date format in the OnOpen Event so date formats are the same so the toolbar buttons do not create if OnOpen event has already

other than that gives me plenty to now work on improving the master page

so Thanks very much


if i had one question it would be how to add a button to the new Tools group you have added as i do not see it in the customize ribbon options

thanks again

[Tinbendr]

how to add a button to the new Tools group

Microsoft didn't make it easy. You have to d/l the XML editor. (They say you can edit it manually, but I've never been successful.)

Here is a link to the editor.

Here is a another resource on ribbons.

[AkaTrouble]

thanks for that

will do some reading

much appreciated

[AkaTrouble]

Update

your links guided me perfectly

tried and tested

thank you very much