Named range by VBA to another workbook
Hi,
I am trying to set named range by VBA to another workbook. Dim LR As Integer Dim I As Integer Dim J As Integer Dim WRM As Workbook Dim SDR As Worksheet Set WRM = Workbooks("RUIM new.xls") Set SDR = WRM.Sheets("Damage Receipt") For I = 1 To 12 ActiveWorkbook.Names.Add Name:=SDR.Cells(1, I).Value, _ RefersTo:=SDR.Range((Cells(1, I)), Cells(20000, I)), Visible:=True <<<ERROR Next I am getting error on the above statement. Need help. Regards, Madiya |
Named range by VBA to another workbook
You are missing the line continuation character: "_" (an underscore). RefersTo:=SDR.Range((Cells(1, I)), Cells(20000, I)), _ Visible:=True -- Jim Cone San Francisco, USA http://www.realezsites.com/bus/primitivesoftware (Excel Add-ins / Excel Programming) "Madiya" wrote in message ups.com... Hi, I am trying to set named range by VBA to another workbook. Dim LR As Integer Dim I As Integer Dim J As Integer Dim WRM As Workbook Dim SDR As Worksheet Set WRM = Workbooks("RUIM new.xls") Set SDR = WRM.Sheets("Damage Receipt") For I = 1 To 12 ActiveWorkbook.Names.Add Name:=SDR.Cells(1, I).Value, _ RefersTo:=SDR.Range((Cells(1, I)), Cells(20000, I)), Visible:=True <<<ERROR Next I am getting error on the above statement. Need help. Regards, Madiya |
Named range by VBA to another workbook
On Aug 1, 3:21 am, "Jim Cone" wrote:
You are missing the line continuation character: "_" (an underscore). RefersTo:=SDR.Range((Cells(1, I)), Cells(20000, I)), _ Visible:=True -- Jim Cone San Francisco, USAhttp://www.realezsites.com/bus/primitivesoftware (Excel Add-ins / Excel Programming) "Madiya" wrote in oglegroups.com... Hi, I am trying to set named range by VBA to another workbook. Dim LR As Integer Dim I As Integer Dim J As Integer Dim WRM As Workbook Dim SDR As Worksheet Set WRM = Workbooks("RUIM new.xls") Set SDR = WRM.Sheets("Damage Receipt") For I = 1 To 12 ActiveWorkbook.Names.Add Name:=SDR.Cells(1, I).Value, _ RefersTo:=SDR.Range((Cells(1, I)), Cells(20000, I)), Visible:=True <<<ERROR Next I am getting error on the above statement. Need help. Regards, Madiya Thanks for your reply. Actually this is google error. I have got the whole line from refers upto visible in the same line in VBA. There is something else very simple and obvious which I am missing. But thanks for your help. Pl let me know if you have any other thought. Regards, Madiya |
Named range by VBA to another workbook
On Aug 1, 4:39 am, Madiya wrote:
On Aug 1, 3:21 am, "Jim Cone" wrote: You are missing the line continuation character: "_" (an underscore). RefersTo:=SDR.Range((Cells(1, I)), Cells(20000, I)), _ Visible:=True -- Jim Cone San Francisco, USAhttp://www.realezsites.com/bus/primitivesoftware (Excel Add-ins / Excel Programming) "Madiya" wrote in oglegroups.com... Hi, I am trying to set named range by VBA to another workbook. Dim LR As Integer Dim I As Integer Dim J As Integer Dim WRM As Workbook Dim SDR As Worksheet Set WRM = Workbooks("RUIM new.xls") Set SDR = WRM.Sheets("Damage Receipt") For I = 1 To 12 ActiveWorkbook.Names.Add Name:=SDR.Cells(1, I).Value, _ RefersTo:=SDR.Range((Cells(1, I)), Cells(20000, I)), Visible:=True <<<ERROR Next I am getting error on the above statement. Need help. Regards, Madiya Thanks for your reply. Actually this is google error. I have got the whole line from refers upto visible in the same line in VBA. There is something else very simple and obvious which I am missing. But thanks for your help. Pl let me know if you have any other thought. Regards, Madiya- Hide quoted text - - Show quoted text - Hi Jim, Got the error. The name is not valid (name should not contain any blank cherecter). Is there any way I can convert any text in cell containing space to "_" For example, say I have a text "abc xyz" which should get converted in "abc_xyz" Regards, Madiya |
Named range by VBA to another workbook
Sub FillInTheBlanks() Dim str As String str = "abc xyz" str = Application.Substitute(str, " ", "_") MsgBox str End Sub -- Jim Cone San Francisco, USA http://www.realezsites.com/bus/primitivesoftware (Excel Add-ins / Excel Programming) "Madiya" wrote in message Hi Jim, Got the error. The name is not valid (name should not contain any blank cherecter). Is there any way I can convert any text in cell containing space to "_" For example, say I have a text "abc xyz" which should get converted in "abc_xyz" Regards, Madiya |
Named range by VBA to another workbook
On Aug 1, 5:24 am, "Jim Cone" wrote:
Sub FillInTheBlanks() Dim str As String str = "abc xyz" str = Application.Substitute(str, " ", "_") MsgBox str End Sub -- Jim Cone San Francisco, USAhttp://www.realezsites.com/bus/primitivesoftware (Excel Add-ins / Excel Programming) "Madiya" wrote in message Hi Jim, Got the error. The name is not valid (name should not contain any blank cherecter). Is there any way I can convert any text in cell containing space to "_" For example, say I have a text "abc xyz" which should get converted in "abc_xyz" Regards, Madiya Thank you dear Jim. My job is done with your help. Regards, Madiya |
All times are GMT +1. The time now is 07:29 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com