combine macros and have access from my personal workbook

[mml]

Absolute newbie here . I am teaching myself VBA and wanted to create a macro that would complete formatting of a spread sheet . Things like deleting rows ,adding headers etc. I have managed to write these modules separately and thought that I could combine into one macro . This is where the problem comes in probably newbie mistake. In the attached file with the separate modules. How do I get this into one macro that I can access in my personal macros ? The spread sheet that I get comes each month and thought that a macro to format would be a way to automate a laborious process . If some one could give me some guidance on how to refine I would be grateful . PS try not to too harsh in your judgement on my code ... I am trying to solve , understand VBA and attempting .At least it will give some a laugh

[paulked]

Hi there

To make it easier to find things, I think it is always better to organise your modules into sub-sets of code. So I've reduced your modules to 3 and named them... modCommon (for general items), modFormat (for Formatting routines) and modSheets (for sheet manipulations) and moved the Sub's to where I think they should be. To re-name the modules, select the module and then type the new name in the (Name) box.

You can move the Sub's anywhere, but be sure to delete the one from the destination. I noticed there was Module7 and Module71 with the same Sub in it. Using Debug - Compile VBAProject from the VBA menu before running code will pick up errors like these.

Also, try to keep the Sheet modules free for the Code specifically for that sheet. EG a Worksheet_Change event etc.

Option Explicit is also a good idea. This forces you to declare any variables you use and can be turned on by selecting the 'Require Variable Declaration' from the Tools - Options... menu.

Finally, to run in sequence...



Sub DoStuff()
addheader
Rename_sheet
InsertColumns
'etc...
End Sub


which you will find in modCommon.

Best of luck :thumb

25202

PS I shouldn't mention the rugby... oops! :whistle:

[paulked]

Sorry, forgot to say export the modules (right click on the module) and then import them to your Personal workbook.

[mml]

Hi Paulked
Thank you for taking the time to assist I will review while at work tomorrow . Really appreciate the instruction and really want to understand fully !
Now as for the RUGBY sir ... You may mention this , I am an Aussie Rules fan though . Congrats to Wales

[SamT]

Absolute newbie here . I am teaching myself VBA and wanted...
You will write, (or look at,) macros that kill Excel. Been there, Done that. When I lost a 100+kb Personal.xlsm, I added these to my version of Personal.xlsm. They go in the Personal.xlsm ThisWorkbook Code Module. Edit to suit your hard drives.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
If Not Me.Saved Then _
Me.SaveCopyAs ("C:\Downloads\Excel\Personal_" & CStr(CDbl(Now)) & ".xlsm")
End Sub

Private Sub Workbook_BeforeSave(ByVal SaveAsUi As Boolean, Cancel As Boolean)
Me.SaveCopyAs ("C:\Downloads\Excel\Personal_" & CStr(CDbl(Now)) & ".xlsm")
End Sub
The CStr(CDbl(Now)) bit insures that all saves will have a different name and that Windows will sort them in chronological order

[mml]

2521225212 Hi All having problems with customising the ribbon. I have attached a word doc in way of explanation .

[paulked]

Sorry for delay, I've been busy the last few days!

There isn't a way to do what you want directly. You have to have a sub which 'Calls' other subs as in my example 'DoStuff' above.

So, to reiterate, if you wanted to run all the subs in modSheets you would need to write the sub:



Sub DoSheetStuff()
Call Delete_Sheet
Call Rename_sheet
End Sub


You don't need to use Call, so it can be simplified to:



Sub DoSheetStuff()
Delete_Sheet
Rename_sheet
End Sub


Then in your Macro list you will have another macro Personal.XLSB!DoSheetStuff (as you have DoStuff in your list)

Hope that helps :thumb

SamT's post is excellent advice and it would be a fool who didn't take it :doh:

[Paul_Hossler]

Customising the Ribbon

I have got the macros in my Personal .XLSB!. They have come up as the individual macros .

They come up as individual macros, because 1) they are individual macros, and 2) you added them to the ribbon as individual macros

The fact that there are several macros in a single standard module doesn't affect what I think you're trying to do

If you want to 'InsertColumns' + 'PageBreak' + 'DoStuff' with a single click, then you need an 'umbrella' macro and call THAT from the ribbon and NOT the lower level ones



Sub StandardFormat
InsertColumns
PageBreak
DoStuff


Add StandardFormat to the ribbon



Is there a way of getting them in the grouped macros , modsheet , mod format even that would be preferable if possible .Then I could get the

Create different tabs for each kind of macro is one way, or put them into different Groups under a single Tab

25237


(SamT will make fun because I can never draw a good circle :crying::crying::crying:)

[mml]

We have had a long weekend in Aus so no work over the weekend !! Paul H and Paulked thanks for getting back to me . Split these into to sub groups as explained , works well. This small change will save time for about 6 people so its worth it . As for SamT's advice I will take it once I understand it fully .That's the trouble with being a newbie you see stuff everywhere , not quite sure what to do with it at this moment . So thanks again to you all for taking the time and sharing your wisdom.

[paulked]

SamT's code is missing an End If in the Before_Close event code:



Private Sub Workbook_BeforeClose(Cancel As Boolean)
If Not Me.Saved Then _
Me.SaveCopyAs ("C:\temp\Personal_" & CStr(CDbl(Now)) & ".xlsm")
End If
End Sub


As he says, it goes in the ThisWorkbook code module of your Personal workbook.

[SamT]

SamT's code is NOT missing an "End If." It is a Single Line "If... Then". Note the Line Continuation mark ( _)

[paulked]

My humble apologies :bow:

[SamT]

:thumb
We all hadda lern some-ow