hi,

i wanted to write a few macros to perform some of the routine
maintenance on a fairly large workbook, and look at actually creating
parts of it.

The required tasks, formatting / update of formulae are not quite the
same but have many similarities, and so I thought i'd write subs for
the common tasks which would be called where they are needed, and
write the unique stuff in a sub for each sheet. then all of these
would be called up by a master sub.

I'd like to aim to minimise the number of variables passed, ideally
I'd do this:

Sub withthis()
Dim sWB as string
sWB = ActiveWorkbook.name
With Workbooks(sWB).Sheets(2)
WriteToCells iRow:=5, iCol:=5, sValue:="will this work?"
End With
End Sub

Sub WriteToCellsiRow As Integer, iCol As Integer, sValue As String)
.Cells(iRow, iCol) = sValue
End Sub

but instead it looks like i will need to pass the sheet name each
time, which means I will refer to it once when the sheet specific
stuff is being wtitten, next pass it to the sub, which in turn will be
need a with statement. Is that my only option or is there a more
efficient way of doing that? merci beacoup!

Something like this?...
'--
Sub withthis()
Dim sht As Worksheet
Dim sText As String
sText = "will this work?"

For Each sht In ActiveWorkbook.Worksheets
Call WriteToCells(iRow:=5, iCol:=5, sValue:=sText, oSht:=sht)
Next 'sht
End Sub
'--
Function WriteToCells(iRow As Long, iCol As Long, _
sValue As String, oSht As Worksheet)
oSht.Cells(iRow, iCol) = sValue
End Function
--
Jim Cone
Portland, Oregon USA



"Cako"
wrote in message
hi,
i wanted to write a few macros to perform some of the routine
maintenance on a fairly large workbook, and look at actually creating
parts of it.
The required tasks, formatting / update of formulae are not quite the
same but have many similarities, and so I thought i'd write subs for
the common tasks which would be called where they are needed, and
write the unique stuff in a sub for each sheet. then all of these
would be called up by a master sub.
I'd like to aim to minimise the number of variables passed, ideally
I'd do this:

Sub withthis()
Dim sWB as string
sWB = ActiveWorkbook.name
With Workbooks(sWB).Sheets(2)
WriteToCells iRow:=5, iCol:=5, sValue:="will this work?"
End With
End Sub

Sub WriteToCellsiRow As Integer, iCol As Integer, sValue As String)
.Cells(iRow, iCol) = sValue
End Sub

but instead it looks like i will need to pass the sheet name each
time, which means I will refer to it once when the sheet specific
stuff is being wtitten, next pass it to the sub, which in turn will be
need a with statement. Is that my only option or is there a more
efficient way of doing that? merci beacoup!