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).Paste
.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" <[email protected]> wrote in message news:<#[email protected]>...
> 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(nme.Name).address)
> End If
> Next nme
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "SIGE" <[email protected]> wrote in message
> news:[email protected]...
> > 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