Excel & VBA Problem

[Doofus]

I've created a macro which I want to run on three sheets automatically.

So I created another macro:

Sub RunMacroOnMultipleSheets()[

Sheets("First Sheet").Activate
Call Col_Display

Sheets("Second Sheet").Activate
Call Col_Display

Sheets("Third Sheet").Activate
Call Col_Display


End Sub

But when I run it, it fails at the first Call command, telling me "Compile Error. Argument Not Optional".

WTF is an argument?

If it matters, the macro 'Col_Display' begins like this:

Sub Col_Display(ByVal Target As Range)


If Not Application.Intersect(Range("A5"), Range(Target.Address)) Is Nothing Then

Select Case Target.Value

Case Is = "1": Columns("h:r").EntireColumn.Hidden = True

Columns("f:f").EntireColumn.Hidden = False

[balham123]

Sub Col_Display(ByVal Target As Range)

Is expecting you to pass a range in ( and argument), something like

Call Col_Display (A1 : H 7)

Not in front of computer to check actual syntax

[tribbles]

An 'argument' is another word for 'parameter'.

Your Col_Display is expecting a range in order for it to process, and because you're not giving it this parameter, it's giving you an error.

Can't quite remember the exact syntax for a range; it's been a very long time since I've had to do this!

[Doofus]

Thanks both. So what would that range represent?

All the cells in the sheet?
The cells I want to affect?
The cell that determines which cells are affected?

[balham123]

Normally it would be the cells you want to affect.

Ultimately depends what your function is doing

[tribbles]

It depends on what the subroutine is meant to be doing.

My guess is that it's the cells you want to affect based on its name of "Target".

[Doofus]

balham123 said:

Normally it would be the cells you want to affect.

Ultimately depends what your function is doing

I want to hide specific columns based upon a value in A5, on each of three sheets.

So if A5 - 1, I want to hide columns J - R. If A5 = 2, I want to hide I and K-R etc.

I'm trying to run the macro on First Sheet, Second Sheet and Third Sheet in turn, based upon the values in A5 on each sheet. But those cells are linked, so if I change the value in A5 on First Sheet, the values in A5 on the other two sheets will change. But the macro will only run on First Sheet. Nothing happens on the other two.

So I've deleted the RunMacroOnMultipleSheets code and tried the Col_Display macro, with the code repeated three times, separated by

Sheets("First Sheet").Activate
Sheets("Second Sheet").Activate
Sheets("Third Sheet").Activate

It works perfectly (so I know the code is good), but it still only acts upon the first sheet. Third Sheet becomes the active sheet, so I know it's running the entire macro, but it's not hiding any columns on the second two sheets.

[balham123]

You probably want to just loop through all sheets in the workbook

Something like this bit

Sub forEachWs()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
Call resizingColumns(ws)
Next
End Sub

From

https://stackoverflow.com/questions/21918166/excel...

[Doofus]

I can't loop through all the sheets, because I only want to change three, and there are eleven.

I've sorted it. It ain't pretty, but it works.

Thanks all for your help.