Copying Name Ranges along with Worksheets
 

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!


--
cratediggah
------------------------------------------------------------------------
cratediggah's Profile: http://www.excelforum.com/member.php...o&userid=30363
View this thread: http://www.excelforum.com/showthread...hreadid=572249

Copying Name Ranges along with Worksheets
 

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

--
Regards,
Tom Ogilvy

"cratediggah" wrote:


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!


--
cratediggah
------------------------------------------------------------------------
cratediggah's Profile: http://www.excelforum.com/member.php...o&userid=30363
View this thread: http://www.excelforum.com/showthread...hreadid=572249