| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy Different Name References to New Workbook
Hi There,
I have created through VBA a couple of Names: eg: VBA1 = Sheet1!$B$1:$B$5 VBA2 = Sheet1!$C$10:$C$50 etc ... -I do not see the light anymore in all the sample codes i found!!!- Is there a way to copy all the ranges of my Names beginning with "VBA" to a new workbook (NOT the Names of the ranges (eg. VBA1) nor the address (eg. Sheet1!$B$1:$B$5) but the content in cells B1:B5 and C10:C50 and ... like Copy= Paste Special A bit like: ... Application.Goto Reference:="VBA1" Selection.Copy Workbooks.Add Selection.PasteSpecial Paste:=xlValues Selection.PasteSpecial Paste:=xlFormats Application.Goto Reference:="VBA2" Selection.Copy Workbooks.Add Selection.PasteSpecial Paste:=xlValues Selection.PasteSpecial Paste:=xlFormats But looping ... so that each Name reference gets copied into the new workbook, putting them on the same worksheet next to each other??? Looking like this in the new workbook: A B ... 1 B1 C10 2 B2 C11 3 ... ... Best Regards, Sige Sub ListVBARangeNames()'Will display the Names in my workbook starting with "VBA" Dim RN As Object Dim listrn As String For Each RN In ActiveWorkbook.Names If RN.Name Like "VBA*" Then listrn = listrn & vbCr & RN.Name End If Next RN MsgBox listrn End Sub |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Copy Different Name References to New Workbook
How about something like
For Each nme In Activeworkbook.Names If Lef(nme.name,3) = "VBA" then Range(nme.Name).Copy Destination:= _ Workboks("other.xls").Worksheets(1).Range(Range(nm e.Name).address) End If Next nme -- HTH RP (remove nothere from the email address if mailing direct) "SIGE" wrote in message om... Hi There, I have created through VBA a couple of Names: eg: VBA1 = Sheet1!$B$1:$B$5 VBA2 = Sheet1!$C$10:$C$50 etc ... -I do not see the light anymore in all the sample codes i found!!!- Is there a way to copy all the ranges of my Names beginning with "VBA" to a new workbook (NOT the Names of the ranges (eg. VBA1) nor the address (eg. Sheet1!$B$1:$B$5) but the content in cells B1:B5 and C10:C50 and ... like Copy= Paste Special A bit like: ... Application.Goto Reference:="VBA1" Selection.Copy Workbooks.Add Selection.PasteSpecial Paste:=xlValues Selection.PasteSpecial Paste:=xlFormats Application.Goto Reference:="VBA2" Selection.Copy Workbooks.Add Selection.PasteSpecial Paste:=xlValues Selection.PasteSpecial Paste:=xlFormats But looping ... so that each Name reference gets copied into the new workbook, putting them on the same worksheet next to each other??? Looking like this in the new workbook: A B ... 1 B1 C10 2 B2 C11 3 ... ... Best Regards, Sige Sub ListVBARangeNames()'Will display the Names in my workbook starting with "VBA" Dim RN As Object Dim listrn As String For Each RN In ActiveWorkbook.Names If RN.Name Like "VBA*" Then listrn = listrn & vbCr & RN.Name End If Next RN MsgBox listrn End Sub |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Copy Different Name References to New Workbook
Hi Bob,
Thanks for your help! I am nearly there ... Your code runs fine ...but I would like to copy the ranges from my current wbk to a new -not yet existing- workbook. I came so far ... but the syntax is not what it should ... as I cannot read the names from the workbook which contain the to-be-exported Names. (and I do not want to hard-code the name of this "sourcefile" neither as it will change too often. Could You Please take a look at the syntax??? Sige Sub sige() Dim ExpBook As Workbook Dim nme Set ExpBook = Workbooks.Add(xlWorksheet) For Each nme In ActiveWorkbook.Names 'unfortunately my newly created wbk is 'active and not the source file which contains the names ... If Left(nme.Name, 3) = "VBA" Then MsgBox nme.Name Range(nme.Name).Copy With ExpBook .Worksheets(1).Range(Range(nme.Name).Address).Past e .SaveAs FileName:=ThisWorkbook.Path & "\temp.xls", FileFormat:=xlWorkbook .Close SaveChanges:=False If Err < 0 Then MsgBox "Cannot export" & ThisWorkbook.Path & "\temp.xls" End With Else Left(nme.Name, 3) = "" MsgBox "No names to export" Exit Sub End If Next nme End Sub "Bob Phillips" wrote in message ... How about something like For Each nme In Activeworkbook.Names If Lef(nme.name,3) = "VBA" then Range(nme.Name).Copy Destination:= _ Workboks("other.xls").Worksheets(1).Range(Range(nm e.Name).address) End If Next nme -- HTH RP (remove nothere from the email address if mailing direct) "SIGE" wrote in message om... Hi There, I have created through VBA a couple of Names: eg: VBA1 = Sheet1!$B$1:$B$5 VBA2 = Sheet1!$C$10:$C$50 etc ... -I do not see the light anymore in all the sample codes i found!!!- Is there a way to copy all the ranges of my Names beginning with "VBA" to a new workbook (NOT the Names of the ranges (eg. VBA1) nor the address (eg. Sheet1!$B$1:$B$5) but the content in cells B1:B5 and C10:C50 and ... like Copy= Paste Special A bit like: ... Application.Goto Reference:="VBA1" Selection.Copy Workbooks.Add Selection.PasteSpecial Paste:=xlValues Selection.PasteSpecial Paste:=xlFormats Application.Goto Reference:="VBA2" Selection.Copy Workbooks.Add Selection.PasteSpecial Paste:=xlValues Selection.PasteSpecial Paste:=xlFormats But looping ... so that each Name reference gets copied into the new workbook, putting them on the same worksheet next to each other??? Looking like this in the new workbook: A B ... 1 B1 C10 2 B2 C11 3 ... ... Best Regards, Sige Sub ListVBARangeNames()'Will display the Names in my workbook starting with "VBA" Dim RN As Object Dim listrn As String For Each RN In ActiveWorkbook.Names If RN.Name Like "VBA*" Then listrn = listrn & vbCr & RN.Name End If Next RN MsgBox listrn End Sub |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Copy Different Name References to New Workbook
How about
Sub sige() Dim ThisBook As Workbook Dim ExpBook As Workbook Dim nme Set ThisBook = ActiveWorkbook Set ExpBook = Workbooks.Add(xlWorksheet) For Each nme In ThisBook.Names If Left(nme.Name, 3) = "VBA" Then MsgBox nme.Name Range(nme.Name).Copy With ExpBook .Worksheets(1).Range(Range(nme.Name).Address).Past e .SaveAs FileName:=ThisWorkbook.Path & "\temp.xls", _ FileFormat:=xlWorkbook .Close SaveChanges:=False If Err < 0 Then MsgBox "Cannot export" & _ ThisWorkbook.Path & "\temp.xls" End With Else Left(nme.Name, 3) = "" MsgBox "No names to export" Exit Sub End If Next nme End Sub -- HTH RP (remove nothere from the email address if mailing direct) "SIGE" wrote in message om... Hi Bob, Thanks for your help! I am nearly there ... Your code runs fine ...but I would like to copy the ranges from my current wbk to a new -not yet existing- workbook. I came so far ... but the syntax is not what it should ... as I cannot read the names from the workbook which contain the to-be-exported Names. (and I do not want to hard-code the name of this "sourcefile" neither as it will change too often. Could You Please take a look at the syntax??? Sige Sub sige() Dim ExpBook As Workbook Dim nme Set ExpBook = Workbooks.Add(xlWorksheet) For Each nme In ActiveWorkbook.Names 'unfortunately my newly created wbk is 'active and not the source file which contains the names ... If Left(nme.Name, 3) = "VBA" Then MsgBox nme.Name Range(nme.Name).Copy With ExpBook .Worksheets(1).Range(Range(nme.Name).Address).Past e .SaveAs FileName:=ThisWorkbook.Path & "\temp.xls", FileFormat:=xlWorkbook .Close SaveChanges:=False If Err < 0 Then MsgBox "Cannot export" & ThisWorkbook.Path & "\temp.xls" End With Else Left(nme.Name, 3) = "" MsgBox "No names to export" Exit Sub End If Next nme End Sub "Bob Phillips" wrote in message ... How about something like For Each nme In Activeworkbook.Names If Lef(nme.name,3) = "VBA" then Range(nme.Name).Copy Destination:= _ Workboks("other.xls").Worksheets(1).Range(Range(nm e.Name).address) End If Next nme -- HTH RP (remove nothere from the email address if mailing direct) "SIGE" wrote in message om... Hi There, I have created through VBA a couple of Names: eg: VBA1 = Sheet1!$B$1:$B$5 VBA2 = Sheet1!$C$10:$C$50 etc ... -I do not see the light anymore in all the sample codes i found!!!- Is there a way to copy all the ranges of my Names beginning with "VBA" to a new workbook (NOT the Names of the ranges (eg. VBA1) nor the address (eg. Sheet1!$B$1:$B$5) but the content in cells B1:B5 and C10:C50 and ... like Copy= Paste Special A bit like: ... Application.Goto Reference:="VBA1" Selection.Copy Workbooks.Add Selection.PasteSpecial Paste:=xlValues Selection.PasteSpecial Paste:=xlFormats Application.Goto Reference:="VBA2" Selection.Copy Workbooks.Add Selection.PasteSpecial Paste:=xlValues Selection.PasteSpecial Paste:=xlFormats But looping ... so that each Name reference gets copied into the new workbook, putting them on the same worksheet next to each other??? Looking like this in the new workbook: A B ... 1 B1 C10 2 B2 C11 3 ... ... Best Regards, Sige Sub ListVBARangeNames()'Will display the Names in my workbook starting with "VBA" Dim RN As Object Dim listrn As String For Each RN In ActiveWorkbook.Names If RN.Name Like "VBA*" Then listrn = listrn & vbCr & RN.Name End If Next RN MsgBox listrn End Sub |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Copy Different Name References to New Workbook
Hi Bob, Thanks for your reply!
I am nearly getting there ... I would like to create a New workbook and put there the names (ranges) from the sourcefile ... instead of transfering the ranges to an already specified workbook open ...where this Workbook-name is hardcoded. I've to repaet this procedure too often for different workbooks...and changing the code seems not very efficient. So far I came up with something like this... Could you please check the syntax??? As this won't copy my ranges because my active workbook is the one i newly created.. DO I need to create a class-module to establish what i want? Cheers Sige Sub sige() Dim ExpBook As Workbook Dim nme Set ExpBook = Workbooks.Add(xlWorksheet) For Each nme In ActiveWorkbook.Names If Left(nme.Name, 3) = "VBA" Then MsgBox nme.Name Range(nme.Name).Copy With ExpBook .Worksheets(1).Range(Range(nme.Name).Address).Past e .SaveAs FileName:=ThisWorkbook.Path & "\temp.xls", FileFormat:=xlWorkbook .Close SaveChanges:=False If Err < 0 Then MsgBox "Cannot export" & ThisWorkbook.Path & "\temp.xls" End With Else Left(nme.Name, 3) = "" MsgBox "No names to export" Exit Sub End If Next nme End Sub (SIGE) wrote in message . com... Hi There, I have created through VBA a couple of Names: eg: VBA1 = Sheet1!$B$1:$B$5 VBA2 = Sheet1!$C$10:$C$50 etc ... -I do not see the light anymore in all the sample codes i found!!!- Is there a way to copy all the ranges of my Names beginning with "VBA" to a new workbook (NOT the Names of the ranges (eg. VBA1) nor the address (eg. Sheet1!$B$1:$B$5) but the content in cells B1:B5 and C10:C50 and ... like Copy= Paste Special A bit like: ... Application.Goto Reference:="VBA1" Selection.Copy Workbooks.Add Selection.PasteSpecial Paste:=xlValues Selection.PasteSpecial Paste:=xlFormats Application.Goto Reference:="VBA2" Selection.Copy Workbooks.Add Selection.PasteSpecial Paste:=xlValues Selection.PasteSpecial Paste:=xlFormats But looping ... so that each Name reference gets copied into the new workbook, putting them on the same worksheet next to each other??? Looking like this in the new workbook: A B ... 1 B1 C10 2 B2 C11 3 ... ... Best Regards, Sige Sub ListVBARangeNames()'Will display the Names in my workbook starting with "VBA" Dim RN As Object Dim listrn As String For Each RN In ActiveWorkbook.Names If RN.Name Like "VBA*" Then listrn = listrn & vbCr & RN.Name End If Next RN MsgBox listrn End Sub |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Copy Different Name References to New Workbook
Sorry Bob for the re-post. I cannot see the Newsgroup-postings appearing over the Internet ... takes more than the 3-9 hours, which they claim! Will test your solution! (and come up probably with more questions ;o)) Thx a lot Sige "NOSPAM" to be removed for direct mailing... *** Sent via Developersdex http://www.developersdex.com *** |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Copy Different Name References to New Workbook
Hi Bob, I am bugging on: =Range(nme.Name).Copy Grrr ..what key do I miss again .. Sige *** Sent via Developersdex http://www.developersdex.com *** |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| workbook references | Excel Worksheet Functions | |||
| how do I copy R[40]C1 references | Excel Worksheet Functions | |||
| Worksheet.copy changes references | Excel Programming | |||
| Workbook references | Excel Programming | |||
| Question for Experts: Opening workbook with workbook references | Excel Programming | |||
Linear Mode
