error; " 'Range' of object '_Worksheet' failed"?

[DK]

Sheet1 has a list of records and additional "fields" that are common to all
records.
Sheet2 has a single row of formulas "ExpRow" that needs to be copied down
for the number of records on Sheet1.
The following code is in a general module and works well *Only* when Sheet2
is active.
If the active sheet is not Sheet2 I get the error; " 'Range' of object
'_Worksheet' failed".
This error happens at each statement that interacts with Sheet2 begining
with > ExpRows = Sheet2.Range(Cells(2, 1), Cells(4000, c)).Address
How can I rephase the statements to allow the the code to run regardless of
which sheet is active?
Also, I may move this code to Sheet1 to activate it with a button. Will this
affect the syntax?
Thank you.

Sub Build_Export()
Dim ExpRows As String, r As Integer, c As Integer, LastMtrRow As Long,
Msg As String
r = Range("MtrCounter").Value
c = Range("ExpRow").Columns.Count
LastMtrRow = Sheet1.Range("A65536").End(xlUp).Row -
Range("MtrHeader").Row
Msg = "Check for blank rows in the Input list."
ExpRows = Sheet2.Range(Cells(2, 1), Cells(4000, c)).Address

Range(ExpRows).Clear
If LastMtrRow < 1 Or LastMtrRow <> Range("MtrCounter").Value _
Then
Info = MsgBox(Msg, vbInformation, "Missing Information")
Exit Sub
Else
Sheet2.Range(Cells(2, 1), Cells(r, c)) = Range("ExpRow").Formula
End If

End Sub

 

[Homey]

ExpRows = Sheet2.Range(Cells(2, 1), Cells(4000, c)).Address

Cells work with active sheet unless said otherwise so:

With Sheet2
ExpRows = .Range(.Cells(2, 1), .Cells(4000, c)).Address
End With

H

| Sheet1 has a list of records and additional "fields" that are common to
all
| records.
| Sheet2 has a single row of formulas "ExpRow" that needs to be copied down
| for the number of records on Sheet1.
| The following code is in a general module and works well *Only* when
Sheet2
| is active.
| If the active sheet is not Sheet2 I get the error; " 'Range' of object
| '_Worksheet' failed".
| This error happens at each statement that interacts with Sheet2 begining
| with > ExpRows = Sheet2.Range(Cells(2, 1), Cells(4000, c)).Address
| How can I rephase the statements to allow the the code to run regardless
of
| which sheet is active?
| Also, I may move this code to Sheet1 to activate it with a button. Will
this
| affect the syntax?
| Thank you.
|
| Sub Build_Export()
| Dim ExpRows As String, r As Integer, c As Integer, LastMtrRow As Long,
| Msg As String
| r = Range("MtrCounter").Value
| c = Range("ExpRow").Columns.Count
| LastMtrRow = Sheet1.Range("A65536").End(xlUp).Row -
| Range("MtrHeader").Row
| Msg = "Check for blank rows in the Input list."
| ExpRows = Sheet2.Range(Cells(2, 1), Cells(4000, c)).Address
|
| Range(ExpRows).Clear
| If LastMtrRow < 1 Or LastMtrRow <> Range("MtrCounter").Value _
| Then
| Info = MsgBox(Msg, vbInformation, "Missing Information")
| Exit Sub
| Else
| Sheet2.Range(Cells(2, 1), Cells(r, c)) = Range("ExpRow").Formula
| End If
|
| End Sub
|
|

 

[DK]

H,
Thanks very much for the info. Works fine.
DK