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

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

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

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).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


--

HTH

RP
(remove nothere from the email address if mailing direct)


"SIGE" <[email protected]> wrote in message
news:[email protected]...
> 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

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).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


[email protected] (SIGE) 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

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 ***

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 ***