Looping macro

[Beach Boy]

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?

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

 

[Fluff]

There is no need to have your account terminated.
All we ask is that you let us know you have posted elsewhere.
Please see this for the reasons https://www.excelguru.ca/content.php?184

 

[igold]

Errant post...