Error 424 Object Required when passing range as parameter
 

I have a procedure that should copy a table template (borders and such)
to a specified range. When I pass the target range AS Range, the proc
will alternately work and not work. Yes alternaterly. When it doesn't
work, i get error 424.

Note: If i rewrtie the code so that the the target range is passed as
a string, the proc works fine.

What am i doing wrong?

Sub MDebug_CallTblCreate()
'QDResults. is waht the table will be called/
'wsQDResults.Range("E6") is where i want the table created.
wsQDResults is a named worksheet
MTables_TblCreate "QDResults", wsQDResults.Range("E5"),
Range("TD_Style3"), Range("QD_AllYees"
End Sub

Sub MTables_TblCreate(ByVal sTblName As String, ByVal rWhere As Range,
ByVal rTblDef As Range, ByVal sQryDef As Range,
ByVal bExclusive as Bollean)
'Proc to copy a table template onto target range
'Parameters
' sTblName Name that will be given to the table.
' rWhere Range where table will be copied to
' rTblDef Table definition associated with the table
' rQryDef Query definition associated with the table
' bExclusive If True, clear sheet before copy-paste

Dim wsStart As Worksheet .
Dim rTDTemplate As Range 'will hold reference to table template

Set wsStart = ActiveSheet 'remember where we started from
rWhere.Parent.Select 'go to target worksheet

'Delete existing UsedRange if exclusivsive
If bExclusive Then
rWhere.Parent.UsedRange.EntireRow.Delete
End If

'rTblDef is a single cell named-range. It is used as an anchor to
'offset to cells containing values that decribe the formatting of a
'table. In this case,
' rTblDef.Offset(giTTNameRow,0).value
'returns the name of of the table template.
Set rTDTemplate = Range(rTblDef.Offset(giTTNameRow, 0).Value)
rTDTemplate.Copy 'Copy the table template
rWhere.PasteSpecial xlPasteAll 'THIS LINE CAUSES ERROR ALTERNATELY!

'.
'rest of procedure not shown...
End Sub

Thank you in advance ^^

Error 424 Object Required when passing range as parameter
 

2 things:
Put Option Explicit at the top of every module.
At least it would pick up the error of: as Bollean
Secondly, take out all your ByVal.
It will then be the default, which is ByRef and I think that is what you
want.

RBS

"carrick" wrote in message
oups.com...
I have a procedure that should copy a table template (borders and such)
to a specified range. When I pass the target range AS Range, the proc
will alternately work and not work. Yes alternaterly. When it doesn't
work, i get error 424.

Note: If i rewrtie the code so that the the target range is passed as
a string, the proc works fine.

What am i doing wrong?

Sub MDebug_CallTblCreate()
'QDResults. is waht the table will be called/
'wsQDResults.Range("E6") is where i want the table created.
wsQDResults is a named worksheet
MTables_TblCreate "QDResults", wsQDResults.Range("E5"),
Range("TD_Style3"), Range("QD_AllYees"
End Sub

Sub MTables_TblCreate(ByVal sTblName As String, ByVal rWhere As Range,
ByVal rTblDef As Range, ByVal sQryDef As Range,
ByVal bExclusive as Bollean)
'Proc to copy a table template onto target range
'Parameters
' sTblName Name that will be given to the table.
' rWhere Range where table will be copied to
' rTblDef Table definition associated with the table
' rQryDef Query definition associated with the table
' bExclusive If True, clear sheet before copy-paste

Dim wsStart As Worksheet .
Dim rTDTemplate As Range 'will hold reference to table template

Set wsStart = ActiveSheet 'remember where we started from
rWhere.Parent.Select 'go to target worksheet

'Delete existing UsedRange if exclusivsive
If bExclusive Then
rWhere.Parent.UsedRange.EntireRow.Delete
End If

'rTblDef is a single cell named-range. It is used as an anchor to
'offset to cells containing values that decribe the formatting of a
'table. In this case,
' rTblDef.Offset(giTTNameRow,0).value
'returns the name of of the table template.
Set rTDTemplate = Range(rTblDef.Offset(giTTNameRow, 0).Value)
rTDTemplate.Copy 'Copy the table template
rWhere.PasteSpecial xlPasteAll 'THIS LINE CAUSES ERROR ALTERNATELY!

'.
'rest of procedure not shown...
End Sub

Thank you in advance ^^

Error 424 Object Required when passing range as parameter
 

Sorry for the as Bollean typo.
The module does have an Option Explicit statement.
I took of all the ByVal as you suggested but am still getting the same
result.
But thank you for the suggestion.

RB Smissaert wrote:
2 things:
Put Option Explicit at the top of every module.
At least it would pick up the error of: as Bollean
Secondly, take out all your ByVal.
It will then be the default, which is ByRef and I think that is what you
want.

RBS

"carrick" wrote in message
oups.com...
I have a procedure that should copy a table template (borders and such)
to a specified range. When I pass the target range AS Range, the proc
will alternately work and not work. Yes alternaterly. When it doesn't
work, i get error 424.

Note: If i rewrtie the code so that the the target range is passed as
a string, the proc works fine.

What am i doing wrong?

Sub MDebug_CallTblCreate()
'QDResults. is waht the table will be called/
'wsQDResults.Range("E6") is where i want the table created.
wsQDResults is a named worksheet
MTables_TblCreate "QDResults", wsQDResults.Range("E5"),
Range("TD_Style3"), Range("QD_AllYees"
End Sub

Sub MTables_TblCreate(ByVal sTblName As String, ByVal rWhere As Range,
ByVal rTblDef As Range, ByVal sQryDef As Range,
ByVal bExclusive as Bollean)
'Proc to copy a table template onto target range
'Parameters
' sTblName Name that will be given to the table.
' rWhere Range where table will be copied to
' rTblDef Table definition associated with the table
' rQryDef Query definition associated with the table
' bExclusive If True, clear sheet before copy-paste

Dim wsStart As Worksheet .
Dim rTDTemplate As Range 'will hold reference to table template

Set wsStart = ActiveSheet 'remember where we started from
rWhere.Parent.Select 'go to target worksheet

'Delete existing UsedRange if exclusivsive
If bExclusive Then
rWhere.Parent.UsedRange.EntireRow.Delete
End If

'rTblDef is a single cell named-range. It is used as an anchor to
'offset to cells containing values that decribe the formatting of a
'table. In this case,
' rTblDef.Offset(giTTNameRow,0).value
'returns the name of of the table template.
Set rTDTemplate = Range(rTblDef.Offset(giTTNameRow, 0).Value)
rTDTemplate.Copy 'Copy the table template
rWhere.PasteSpecial xlPasteAll 'THIS LINE CAUSES ERROR ALTERNATELY!

'.
'rest of procedure not shown...
End Sub

Thank you in advance ^^