D
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
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