Can't pass Excel worksheet to Sub
I know I am missing something on my declarations somewhere but I am just not seeing it. When I try to enter my sub "CreateHeaderRow" I get a run-time error 438. Can someone see where I have gone astray?
Thanks
Note: This code is in VBA not VB so I need to define the Workbook or Excel variables.
Code:
Sub Main()
Dim WS As Excel.Worksheet
For Each WS In MiscWB.Worksheets
For ColIndex = 1 To 3
WS.Columns(ColIndex).NumberFormat = "@"
Next ColIndex
Next WS
' Add header rows To all worksheets
For Each WS In MiscWB.Worksheets
CreateHeaderRow (WS)
Next WS
MiscWB.Save
End Sub
Sub CreateHeaderRow(TempWS As Excel.Worksheet)
TempWS.Cells(1, 1).Value = "Title"
TempWS.Cells(1, 2).Value = "Contractor"
TempWS.Cells(1, 3).Value = "Author"
End Sub