Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copying Worksheets which contain Named Ranges | Excel Worksheet Functions | |||
Copying ranges | Excel Programming | |||
Copying ranges of data between worksheets | Excel Worksheet Functions | |||
copying ranges etc | Excel Programming | |||
Copying ranges | Excel Programming |