Excel Macro error
Patrick,
Sorry I forgot to tell you the error message that I
encountered while executing this macro:
"Run-time Error '1004':
Application-defined or object-defined error"
Thanks,
Sheela
-----Original Message-----
It does look like the range name does not exist. Most
likely is that your code expects to find it in the
active
workbook, but that hasn't yet been activated. The code
below is easier to read, and will help you to identifyt
he problem more easily. In the code I set wbSource as
the
active workbook. Set it to a workbook in the same way
that I set the target.
You'll see the main difference is that this code avoids
cutting and pasting, and is easier to follow...
Option Explicit
Dim wbSource As Workbook
Sub DailyMOPS()
Dim wsTarget As Worksheet
Set wbSource = ActiveWorkbook
Set wsTarget = Workbooks("Mopsprod.xls").Sheets
("Daily MOPS")
With wsTarget
.Range("A5").Value = RangeValue
("mthProdDateRange")
.Range("B5").Value = RangeValue("mthULG97Range")
.Range("C5").Value = RangeValue("mthULG92Range")
.Range("D5").Value = RangeValue("mthKeroRange")
.Range("E5").Value = RangeValue("mthGORange")
.Range("F5").Value = RangeValue("mthNaphthaRange")
.Range("G5").Value = RangeValue("mth180Range")
.Range("H5").Value = RangeValue("mthLSWRCrkRange")
.Range("A5:H30").Sort _
Key1:=.Range("A5"), _
Order1:=xlDescending, _
Header:=xlGuess, _
OrderCustom:=1, _
MatchCase:=False, _
Orientation:=xlTopToBottom
End With
End Sub
Private Function RangeValue(sName As String) As String
RangeValue = wbSource.Names
(sName).RefersToRange.Value
End Function
The function just returns the value from the name passed.
HTH
Patrick Molloy
Microsoft Excel MVP
-----Original Message-----
Hello,
Ths coding below is giving an error. I had posted this
question before but I don't really see any problem:
Sub DailyMOPS()
Application.Goto Reference:="mthProdDateRange"
Selection.Copy
Windows("Mopsprod.xls").Activate
Worksheets("Daily MOPS").Select
Range("A5").Select
ActiveSheet.Paste
Application.Goto Reference:="mthULG97Range"
Selection.Copy
Windows("Mopsprod.xls").Activate
Range("B5").Select
ActiveSheet.Paste
Application.Goto Reference:="mthULG92Range"
Selection.Copy
Windows("Mopsprod.xls").Activate
Range("C5").Select
ActiveSheet.Paste
Application.Goto Reference:="mthKeroRange"
Selection.Copy
Windows("Mopsprod.xls").Activate
Range("D5").Select
ActiveSheet.Paste
Application.Goto Reference:="mthGORange"
Selection.Copy
Windows("Mopsprod.xls").Activate
Range("E5").Select
ActiveSheet.Paste
Application.Goto Reference:="mthNaphthaRange"
Selection.Copy
Windows("Mopsprod.xls").Activate
Range("F5").Select
ActiveSheet.Paste
Application.Goto Reference:="mth180Range"
Selection.Copy
Windows("Mopsprod.xls").Activate
Range("G5").Select
ActiveSheet.Paste
Application.Goto Reference:="mthLSWRCrkRange"
Selection.Copy
Windows("Mopsprod.xls").Activate
Range("H5").Select
ActiveSheet.Paste
Range("A5:H30").Select
Selection.Sort Key1:=Range("A5"),
Order1:=xlDescending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False,
Orientation:= _
xlTopToBottom
ActiveWorkbook.Save
xltohtml
End Sub
The error message says:
"Run-time error '1004':
The text you entered is not a valid reference or
defined
name."
As for the first line in the macro,
the "mthProdDateRange" is named. The range of this
reference is "A5:A27" but even after I change it gave
me
the same error msg.
This macro is called in another sub function:
Application.Run Macro:="Mopsprod.xls!DailyMOPS"
Any idea why does this occur?
Thanks,
Sheela
.
.
|