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!

[Tom Ogilvy]

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
>
>