Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy a worksheet and ensure range names stay local
I'm having a problem with the procedure below.
The macro prompts the user to pick a file, and when that file is picked, the "TestIfLandRecord" cycles through every worksheet in the chosen workbook - if it finds one or more that fit my criteria, they are transferred to the current workbook. Each "landrecord" has numerous references to ranges in other worksheets. These ranges exist both in their source workbook, and the destination workbook. If I copied the sheets manually, I'd get a message popping up: "A range of the same name exists in the destination worksheet, do you you want to use this name," and I'd click Yes. But when I run this macro, it seems to be selecting yes for some ranges, and no for others - some ranges point to the original file, and some poiint to the new one. Is there any way to modify this macro to ensure all range references are properly updated? Darren ====================================== Sub TransferLandRecordsToThisBook() Dim wkb As Workbook, ws As Worksheet Dim NewFN Dim FileName As String Application.ScreenUpdating = False Select Case MsgBox("This will import all Land Records from another file." _ & vbCrLf _ & vbCrLf & "Do you wish to proceed?" _ , vbYesNo Or vbExclamation Or vbDefaultButton2, "Import Land Records") Case vbNo Exit Sub End Select NewFN = Application.GetOpenFilename(FileFilter:="Excel Files (*..xls), *.xls", Title:="Please select a file") If NewFN = False Then ' They pressed Cancel MsgBox "Stopping because you did not select a file" Exit Sub Else Set wkb = Workbooks.Open(NewFN) End If For Each ws In wkb.Worksheets If TestIfLandRecord(ws, True) Then ws.Copy Befo=ThisWorkbook.Sheets(1) End If Next ws ' need test to ensure same names don't exist wkb.Close savechanges:=False Application.ScreenUpdating = True End Sub =============================== |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Convert Local Names to Global Names | Excel Worksheet Functions | |||
Worksheet copy problem - local names | Excel Discussion (Misc queries) | |||
copy range on every worksheet (diff names) to a master worksheet (to be created) | Excel Programming | |||
copy range on every worksheet (diff names) to a master worksheet (to be created) | Excel Programming | |||
Copy a range from a CSV file in a webpage to my local worksheet | Excel Programming |