D
Darren Hill
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 Before:=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
===============================
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 Before:=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
===============================