|
Excel Macro error
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 |
Excel VBA Error
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 -----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 . . |
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 . . |
Excel VBA Error
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 -----Original Message----- 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 -----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 . . . |
| All times are GMT +1. The time now is 07:26 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com