I have this macro that works on the first sheet (activesheet) that I would like to work on sheets 1 and 2 the same. Could you please help advise the best way to accomplish this?
Code:
Option Explicit
Dim cXref As Collection
Dim rSource As Range
Dim stDestination As String
Dim wDestination As Workbook
Dim sDestination As Worksheet
Dim rDestination As Range
Dim i As Integer, j As Integer
Dim sPW As String
Sub UpdateDNS()
Set cXref = New Collection
Call buildXREF
stDestination = Application.GetOpenFilename()
sPW = InputBox("Password?", "DNS Cross-Reference")
Workbooks.Open stDestination, , , , sPW
ActiveSheet.AutoFilterMode = False
Range("A2").Select
ActiveCell.CurrentRegion.Select
Selection.Offset(1, 4).Select
Selection.Resize(Selection.Rows.Count - 1, 1).Select
Set rDestination = Selection
j = rDestination.Rows.Count
On Error Resume Next
For i = 2 To j + 1
If Cells(i, 9).Value = 0 Then Cells(i, 9).Value = cXref(Cells(i, 5).Value)
Next
On Error GoTo 0
Err = 0
ActiveCell.CurrentRegion.AutoFilter
Range("A1").Select
End Sub
Sub buildXREF()
Range("A2").Select
ActiveCell.CurrentRegion.Select
Selection.Offset(1, 0).Select
Selection.Resize(Selection.Rows.Count - 1, 2).Select
Set rSource = Selection
j = rSource.Rows.Count
For i = 2 To j + 1
cXref.Add Cells(i, 2).Value, Cells(i, 1).Value
Next
End Sub
Last edited by a moderator:
