Excel Macro error

[Sheela]

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

 

[Sheela]

Hi Patrick,

You told me to set wbsource in the same way that you set
the target.

This code was given by you:

Sub DailyMOPS()
Dim wsTarget As Worksheet
Set wbSource = ActiveWorkbook
Set wsTarget = Workbooks("Mopsprod.xls").Sheets
("Daily MOPS")

Do I have to just copy the same statement like this:

Set wbSource = Workbooks("Mopsprod.xls").Sheets
("Daily MOPS")

Or do assign it with the just the workbook name?

Sorry, I'm new to this....so I'm not familiar on how to
declare it.

Thanks,
Sheela

 

[Sheela]

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

 

[Sheela]

Patrick,

I managed to solve this problem using the coding which
was initially coded. I have put a remark with a pointer
to the line which caused the error.

Sub DailyMOPS()
Application.Goto Reference:="mthProdDateRange"
Selection.Copy
Windows("Mopsprod.xls").Activate <-- 'Must be removed
Worksheets("Daily MOPS").Select <-- 'replaced with
Sheets("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


Thanks alot for your help.

Regards,
Sheela