Copying Name Ranges along with Worksheets

[cratediggah]

Hi All,

I'm building a forecasting tool - on (hypothetically) Month2, I am
"importing" sheets from the previous Month's Excel file.

Set objBook = Workbooks.Open(openWorkbook)
Set objApp = objBook.Parent
Set objSheet = objBook.Worksheets("FDP Archive")

'Delete Current FDP Sheet
With ThisWorkbook
..Sheets("FDP Archive").Delete
End With

With objSheet
..Visible = True
..Select
..Copy After:=Workbooks(wBook).Sheets(numSheets) 'Copy
after last worksheet in workbook
..Visible = False
End With

Copying the sheet into my new month's workbook works perfectly, however
I am *also inadvertantly* copying Named Ranges from the previous month
as well, which is screwing up my charts.

Is there a way to copy a sheet *without* copying any associated Name
Ranges?

Thanks!

 

[Guest]

Set objBook = Workbooks.Open(openWorkbook)
Set objApp = objBook.Parent
Set objSheet = objBook.Worksheets("FDP Archive")
objSheet.Cells.Replace What:="=", _
Replacement:="ZZZ=", _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
MatchCase:=False

'Delete Current FDP Sheet
Application.DisplayAlerts = False
With ThisWorkbook
..Sheets("FDP Archive").Delete
End With
Application.DisplayAlerts = True

With objSheet
..Visible = True
..Select
..Copy After:=Workbooks(wBook).Sheets(numSheets) 'Copy
..Visible = False
End With

ActiveSheet.Cells.Replace What:="ZZZ=", _
Replacement:="=", _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
MatchCase:=False

ObjSheet.Cells.Replace What:="ZZZ=", _
Replacement:="=", _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
MatchCase:=False