With Worksheets [Call Module] ? 1

[richand1]

Hi all, I have a number of sheets within a number of workbooks that are all the same in terms of format. These sheets need to be formatted regularly, and in exactly the same way, to make way for new data.

Now, instead of writing a huge piece of code and copying and pasting the same code over and over again to format the sheets, I wrote two modules: one is the base module that activates each sheet and then calls the one, singular format module and the other is the format module itself:

E.g.

Sub Base_Module()

Sheets("Sheet1").Activate
Format_Module

Sheets("Sheet2").Activate
Format_Module

Sheets("Sheet3").Activate
Format_Module

etc etc

End Sub

My problem is the inefficient sheet activation. I don't seem to be able to use:

With Worksheets ("Sheet1")

Format_Module

With Worksheets ("Sheet2")

Format_Module

etc etc

because the module runs over the current, active sheet and not the one declared.

Can someone point me in the right direction please, or is there no way to call another module without 'physically' activating the sheet?

Many thanks.

R

 

[combo]

In 'With' structure you continue referencing ti the object after dot:

Sub Base_Module()
Format_Module "Sheet1"
Format_Module "Sheet2"
etc etc
End Sub

Sub Format_Module(wsName as String)
With Worksheets(wsName)
    . ' format here
End With
End Sub

combo

 

[richand1]

Combo, I know you're probably thinking "Well that was an easy one", but that belies how much this has both helped with this specific project and further projects, and increased my understanding of VBA. This will increase the efficiency of my code immeasurably!

Thank you very, very much.

Rich

 

[combo]

You just need to precisely address the worksheet. If the code is in the same workbook you can precise it too:
With ThisWorkbook.Worksheets(wsName)
If no, the reference to workbook (instead of ThisWorkbook) can be necessary.
BTW, thanks for the star!

combo

 

[richand1]

No problem at all!

Ahh, I get it. The code is in another workbook so I'll just reference it's name!

Many thanks again.

 

[richand1]

Combo (or anyone),

How do I reference the workbook and worksheet BEFORE I run the format module?

The format module is to be used with multiple workbooks and worksheets so something like:

Sub Base_Module()

Format_Module Workbooks("WorkbookName1").Worksheets("WorksheetName1")

Format_Module Workbooks("WorkbookName2").Worksheets("WorksheetName1")

End Sub

 

[combo]

You could consider calling format procedure with worksheet (as object) as argument. The procedure could be:

Sub Format_Module(objWorksheet as Worksheet)
With objWorksheet
    . ' format here
End With
End Sub

Now you have more freedom in passing the argument:
Format_Module Workbooks("WorkbookName1").Worksheets("WorksheetName1")
set objWs=ActiveWorkbook.Worksheets(2)
Format_Module objWs


combo

 

[richand1]

Magic! Thank you again Combo!

I used your code as a base and came up with this:

(how do I input a "Code" section into my posts by the way, like you have done? I have looked at faqs, but can't find anything on it)

Code:

Sub Base_Module()

Format_Module Workbooks("WorkbookName1").Worksheets("WorksheetName1")

End Sub
______________________________________________________

Sub Format_Module(objWs as Worksheet)

With objWs
. ' format here
End With

End Sub


This [seems to] works perfectly; I tested it by selecting a cell in the workbook that has the modules and running the module. The formatting was completed in the non-activated workbook.

Do you see any problems with my version at all?

 

[combo]

In general there is no need to select/activate excel objects to work with.
For text enhancment see 'Emoticons/Smileys' and 'Process TGML' links below the message window.

combo

 

[SkipVought]

"(how do I input a "Code" section into my posts by the way, like you have done? I have looked at faqs, but can't find anything on it)"

Find [blue]Process TGML[/blue] below the REPLY Window in
[red]Step 2
Options[/red]

FYI, Your use of "Module" in the name of your Procedure and referring to it as, "has the modules and running the module" is very misleading. "Modules" are not run. "Modules" have Procedures the can be run.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[richand1]

Skip, thanks very much again for your invaluable input.

I don't know where I'd be without Tek-Tips and people like you. How do you get time to help so many people AND have a day job

Sub Test()

'This is a procedure, not a module!

End Sub