Excel Macro error

  • Thread starter Thread starter Sheela
  • Start date Start date
S

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
 
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
 
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
 
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
 
Back
Top