Copying ranges

  • Thread starter Thread starter brym
  • Start date Start date
B

brym

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
 
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?
 
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
 
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
 
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
 
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
 
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
 
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
 
Back
Top