Copy worksheet excluding defined name ranges
 

Hi,

I am really struggling with this and would appreciate some assistance.

What I am trying to do is to copy a worksheet from one workbook to
another. The thing is that both workbooks contain defined names and I
do not want the defined names to follow the sheet when it is copied to
the other workbook. Is there any macro that would either exclude the
defined names in the process of copying the sheet or delete all local
names after the sheet has been copied? Both alternatives would work
for me.

Really hoping to get some help on this!

Copy worksheet excluding defined name ranges
 

most likely you want:

KEEP local names
KILL global names...

I've done it via a temporary copy
to a new workbook.


just play with following:
set the mode const to the option you want...

Sub CopySheetSansNames()
Dim wkb(0 To 2) As Workbook, n As Long

Const mode = 1 'testing GLOBAL names

Set wkb(1) = ThisWorkbook
Set wkb(2) = Workbooks("foo2.xls")

'Copy to a new workbook
wkb(1).Worksheets(1).Copy
Set wkb(0) = ActiveWorkbook

'add an extra sheet so you can delete duplicate
'local/global names
wkb(0).Worksheets.Add after:=wkb(0).Sheets(1)

With wkb(0).Names

Select Case mode
Case 0
'deleting ALL names
For n = .Count To 1 Step -1
.Item(n).Delete
Next
Case 1
'Deleting GLOBAL names
For n = .Count To 1 Step -1
If InStr(.Item(n).Name, "!") = 0 Then .Item(n).Delete
Next
Case 2
'Deleting LOCAL names
For n = .Count To 1 Step -1
If InStr(.Item(n).Name, "!") 0 Then .Item(n).Delete
Next
End Select

End With
wkb(0).Worksheets(1).Copy after:=wkb(2).Sheets(wkb(2).Sheets.Count)
wkb(0).Close 0

End Sub





--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Thomas Smith wrote :

Hi,

I am really struggling with this and would appreciate some assistance.

What I am trying to do is to copy a worksheet from one workbook to
another. The thing is that both workbooks contain defined names and I
do not want the defined names to follow the sheet when it is copied to
the other workbook. Is there any macro that would either exclude the
defined names in the process of copying the sheet or delete all local
names after the sheet has been copied? Both alternatives would work
for me.

Really hoping to get some help on this!

Copy worksheet excluding defined name ranges
 

"keepITcool" wrote in message oft.com...
most likely you want:

KEEP local names
KILL global names...

I've done it via a temporary copy
to a new workbook.


just play with following:
set the mode const to the option you want...

Sub CopySheetSansNames()
Dim wkb(0 To 2) As Workbook, n As Long

Const mode = 1 'testing GLOBAL names

Set wkb(1) = ThisWorkbook
Set wkb(2) = Workbooks("foo2.xls")

'Copy to a new workbook
wkb(1).Worksheets(1).Copy
Set wkb(0) = ActiveWorkbook

'add an extra sheet so you can delete duplicate
'local/global names
wkb(0).Worksheets.Add after:=wkb(0).Sheets(1)

With wkb(0).Names

Select Case mode
Case 0
'deleting ALL names
For n = .Count To 1 Step -1
.Item(n).Delete
Next
Case 1
'Deleting GLOBAL names
For n = .Count To 1 Step -1
If InStr(.Item(n).Name, "!") = 0 Then .Item(n).Delete
Next
Case 2
'Deleting LOCAL names
For n = .Count To 1 Step -1
If InStr(.Item(n).Name, "!") 0 Then .Item(n).Delete
Next
End Select

End With
wkb(0).Worksheets(1).Copy after:=wkb(2).Sheets(wkb(2).Sheets.Count)
wkb(0).Close 0

End Sub





--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam

Thanks for your quick answer!

I managed to work out a short code string that actually handled what I
was striving for:

Dim MyName As Name
On Error Resume Next
For Each MyName In Worksheets("Sheet1").Names
MyName.Delete
Next MyName

This will delete all local names in Sheet1 which was what I needed.

Thanks again for your help!
//Thomas