I have a spreadsheet with person-names in Col A. The user may freely create
ranges to show different combinations of users and the ranges exists only as
Named Ranges.

Once i a while I'll have to copy the person-names and range names to a new
workbook (a new period of time).

Could anyone please tell me how to transfer all existing ranges from one
workbook to another using VBA or which approach to use?

Thanks in advance

Hi brym

If you use this
ActiveSheet.Copy

It will make a new workbook with the Activesheet with
also the rangenames in it.

Or this with more sheets
Sheets(Array("Sheet1", "Sheet3")).Copy

Or all worksheets
Worksheets.Copy



--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2002 SP-2)
www.rondebruin.nl



"brym" wrote in message ...
I have a spreadsheet with person-names in Col A. The user may freely create
ranges to show different combinations of users and the ranges exists only as
Named Ranges.

Once i a while I'll have to copy the person-names and range names to a new
workbook (a new period of time).

Could anyone please tell me how to transfer all existing ranges from one
workbook to another using VBA or which approach to use?

Thanks in advance

Hello Ron!
....and thanks. But your examples also copies the entire content of the
sheet. Im looking for a solution that copies only the existing ranges in Col
A . (secondly also the person names, but I can handle those). I have a lot
of other stuff in the sheet that I renew with macros.
Got any idea how to do the range stuff?


"brym" skrev i en meddelelse
...
I have a spreadsheet with person-names in Col A. The user may freely
create
ranges to show different combinations of users and the ranges exists only
as
Named Ranges.

Once i a while I'll have to copy the person-names and range names to a new
workbook (a new period of time).

Could anyone please tell me how to transfer all existing ranges from one
workbook to another using VBA or which approach to use?

Thanks in advance

It looks almost as that's what I need except that I need to perform the copy
to another workbook, not to a new sheet in the old wb. It's a must that the
old wb remains unchanged. I just can't make it work with a new wb, but then
again, I guess im too tired to be smart.
I'd be very pleased if you have a solution to this. Otherwise I'll get into
the matter in the morning and so far, thanks for your help, Ron.

Kind regards Birger

"brym" skrev i en meddelelse
...
I have a spreadsheet with person-names in Col A. The user may freely
create
ranges to show different combinations of users and the ranges exists only
as
Named Ranges.

Once i a while I'll have to copy the person-names and range names to a new
workbook (a new period of time).

Could anyone please tell me how to transfer all existing ranges from one
workbook to another using VBA or which approach to use?

Thanks in advance

Try this for the selection
It will add a workbook and copy your selection in the first sheet in the same cells

Sub test()
Dim destrange As Range
Dim myarea As Range
Dim wb As Workbook
Dim newwb As Workbook

Set wb = ActiveWorkbook
Set newwb = Workbooks.Add
Set destrange = newwb.Sheets(1).Cells(1)

wb.Activate
Application.ScreenUpdating = False
For Each myarea In Selection.Areas
With myarea
.Copy Destination:=newwb.Sheets(1).Range(.Address)
End With
Next myarea
Application.ScreenUpdating = True
End Sub



--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2002 SP-2)
www.rondebruin.nl



"brym" wrote in message ...
It looks almost as that's what I need except that I need to perform the copy
to another workbook, not to a new sheet in the old wb. It's a must that the
old wb remains unchanged. I just can't make it work with a new wb, but then
again, I guess im too tired to be smart.
I'd be very pleased if you have a solution to this. Otherwise I'll get into
the matter in the morning and so far, thanks for your help, Ron.

Kind regards Birger

"brym" skrev i en meddelelse
...
I have a spreadsheet with person-names in Col A. The user may freely
create
ranges to show different combinations of users and the ranges exists only
as
Named Ranges.

Once i a while I'll have to copy the person-names and range names to a new
workbook (a new period of time).

Could anyone please tell me how to transfer all existing ranges from one
workbook to another using VBA or which approach to use?

Thanks in advance

Are you trying to copy the definitions of the named ranges or are you trying
to copy only the cells that are in a named range or are you trying to copy
both: the range name definitions ( in Insert=Name=Define) and the cells
they refer to. Or is there a fourth option (all of column A and the range
name definitions)

Regards
Tom Ogilvy

brym wrote in message
...
I have a spreadsheet with person-names in Col A. The user may freely
create
ranges to show different combinations of users and the ranges exists only
as
Named Ranges.

Once i a while I'll have to copy the person-names and range names to a new
workbook (a new period of time).

Could anyone please tell me how to transfer all existing ranges from one
workbook to another using VBA or which approach to use?

Thanks in advance

Hi Tom!
Your question just opened my eyes! Yes, what I need is the definitions of
the named ranges and the personnames in col A to be copied to a new wb.

I wasn't very clear on that, sorry Ron.

To be a little more detailed, I have a calendar for a year. The sheet and
buttons are created with macros. The user adds personnames into Col A6 and
down and may freely create ranges for these persons in any combination so
that any conflict in time could easily be determined by showing the smaller
groups.

When a new year's approaching I'll run the macros to create a new wb, but I
would like to be able to copy the range definitions (and personnames from
Col A) to the new wb. I could do the job with the ranges, saving them in a
separate sheet. But I think it's possible to do the copy directly to avoid
the use of resources of saving and maintaining the range definitions in a
separate sheet.

I hope this will help a little.

Kind regards Birger


"Tom Ogilvy" skrev i en meddelelse
...
Are you trying to copy the definitions of the named ranges or are you
trying
to copy only the cells that are in a named range or are you trying to copy
both: the range name definitions ( in Insert=Name=Define) and the cells
they refer to. Or is there a fourth option (all of column A and the
range
name definitions)

Regards
Tom Ogilvy

brym wrote in message
...
I have a spreadsheet with person-names in Col A. The user may freely
create
ranges to show different combinations of users and the ranges exists
only
as
Named Ranges.

Once i a while I'll have to copy the person-names and range names to a
new
workbook (a new period of time).

Could anyone please tell me how to transfer all existing ranges from one
workbook to another using VBA or which approach to use?

Thanks in advance

Sub CopyDataAndNames()
Dim wb1 As Workbook
Dim wb2 As Workbook
Dim sh1 As Worksheet
Dim sh2 As Worksheet
Dim rng As Range
Dim rng1 As Range
Dim nm As Name
Set wb1 = Workbooks("SourceBook.xls")
Set wb2 = Workbooks("DestBook.xls")
Set sh1 = wb1.Worksheets(1)
Set sh2 = wb2.Worksheets(1)
Set rng = sh1.Range(sh1.Cells(6, 1), _
sh1.Cells(Rows.Count, 1).End(xlUp))
rng.Copy Destination:=sh2.Cells(6, 1)
For Each nm In wb1.Names
Set rng1 = Nothing
On Error Resume Next
Set rng1 = nm.RefersToRange
On Error GoTo 0
If Not rng1 Is Nothing Then
If rng1.Parent.Name = sh1.Name Then
If Not Intersect(rng1, _
sh1.Columns(1)) Is Nothing Then
sh2.Range(rng1.Address).Name = nm.Name
End If
End If
End If
Next

End Sub

Should do what you describe. It only copies range names that intersect
column A, but you can remove the restriction if you want all names defined
for that sheet.

Regards,
Tom Ogilvy



brym wrote in message
...
Hi Tom!
Your question just opened my eyes! Yes, what I need is the definitions of
the named ranges and the personnames in col A to be copied to a new wb.

I wasn't very clear on that, sorry Ron.

To be a little more detailed, I have a calendar for a year. The sheet and
buttons are created with macros. The user adds personnames into Col A6 and
down and may freely create ranges for these persons in any combination so
that any conflict in time could easily be determined by showing the
smaller
groups.

When a new year's approaching I'll run the macros to create a new wb, but
I
would like to be able to copy the range definitions (and personnames from
Col A) to the new wb. I could do the job with the ranges, saving them in a
separate sheet. But I think it's possible to do the copy directly to avoid
the use of resources of saving and maintaining the range definitions in a
separate sheet.

I hope this will help a little.

Kind regards Birger


"Tom Ogilvy" skrev i en meddelelse
...
Are you trying to copy the definitions of the named ranges or are you
trying
to copy only the cells that are in a named range or are you trying to
copy
both: the range name definitions ( in Insert=Name=Define) and the
cells
they refer to. Or is there a fourth option (all of column A and the
range
name definitions)

Regards
Tom Ogilvy

brym wrote in message
...
I have a spreadsheet with person-names in Col A. The user may freely
create
ranges to show different combinations of users and the ranges exists
only
as
Named Ranges.

Once i a while I'll have to copy the person-names and range names to a
new
workbook (a new period of time).

Could anyone please tell me how to transfer all existing ranges from
one
workbook to another using VBA or which approach to use?

Thanks in advance

No less than PERFECT. Thanks Tom. Really appreaciate that.
Hrrmm ... Ehh .. if you consider selling your brain, give me a call, will
you ;-))

Kind regards Birger


"Tom Ogilvy" skrev i en meddelelse
...
Sub CopyDataAndNames()
Dim wb1 As Workbook
Dim wb2 As Workbook
Dim sh1 As Worksheet
Dim sh2 As Worksheet
Dim rng As Range
Dim rng1 As Range
Dim nm As Name
Set wb1 = Workbooks("SourceBook.xls")
Set wb2 = Workbooks("DestBook.xls")
Set sh1 = wb1.Worksheets(1)
Set sh2 = wb2.Worksheets(1)
Set rng = sh1.Range(sh1.Cells(6, 1), _
sh1.Cells(Rows.Count, 1).End(xlUp))
rng.Copy Destination:=sh2.Cells(6, 1)
For Each nm In wb1.Names
Set rng1 = Nothing
On Error Resume Next
Set rng1 = nm.RefersToRange
On Error GoTo 0
If Not rng1 Is Nothing Then
If rng1.Parent.Name = sh1.Name Then
If Not Intersect(rng1, _
sh1.Columns(1)) Is Nothing Then
sh2.Range(rng1.Address).Name = nm.Name
End If
End If
End If
Next

End Sub

Should do what you describe. It only copies range names that intersect
column A, but you can remove the restriction if you want all names defined
for that sheet.

Regards,
Tom Ogilvy



brym wrote in message
...
Hi Tom!
Your question just opened my eyes! Yes, what I need is the definitions
of
the named ranges and the personnames in col A to be copied to a new wb.

I wasn't very clear on that, sorry Ron.

To be a little more detailed, I have a calendar for a year. The sheet
and
buttons are created with macros. The user adds personnames into Col A6
and
down and may freely create ranges for these persons in any combination
so
that any conflict in time could easily be determined by showing the
smaller
groups.

When a new year's approaching I'll run the macros to create a new wb,
but
I
would like to be able to copy the range definitions (and personnames
from
Col A) to the new wb. I could do the job with the ranges, saving them in
a
separate sheet. But I think it's possible to do the copy directly to
avoid
the use of resources of saving and maintaining the range definitions in
a
separate sheet.

I hope this will help a little.

Kind regards Birger


"Tom Ogilvy" skrev i en meddelelse
...
Are you trying to copy the definitions of the named ranges or are you
trying
to copy only the cells that are in a named range or are you trying to
copy
both: the range name definitions ( in Insert=Name=Define) and the
cells
they refer to. Or is there a fourth option (all of column A and the
range
name definitions)

Regards
Tom Ogilvy

brym wrote in message
...
I have a spreadsheet with person-names in Col A. The user may freely
create
ranges to show different combinations of users and the ranges exists
only
as
Named Ranges.

Once i a while I'll have to copy the person-names and range names to
a
new
workbook (a new period of time).

Could anyone please tell me how to transfer all existing ranges from
one
workbook to another using VBA or which approach to use?

Thanks in advance