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

 

[igold]

Hi Beach Boy,

Welcome to the MrExcel Forum.

When your book opens what are the names of the three sheets that you want the macro to work on. Please do not forget the name of the ActiveSheet, in fact notate which name is the ActiveSheet. Additionally, if possible also provide the names of other sheets as well. If there are a lot of other sheets then just the names of the three that you want to work on will suffice.

 

[Beach Boy]

The name(s) of the sheets change weekly as they get generated. If possible, can we reference them by code name or index name? IE: worksheet(1) or sheet1?

 

[igold]

Sure, Code Name should work...

 

[Beach Boy]

That would be my original question: how would this be best coded? What would be the best way to accomplish this? I assume a loop, just not sure how to code it in.

 

[igold]

Ah, since you are phrasing like that, if it were me, I would hard code the sheets code names in with a loop, after the book opens, and before anything that happens to the ActiveSheet.

 

[Beach Boy]

Here is what I have so far, but the second sheet is not getting populated per the macro. Works fine on sheet1 - What may I have wrong?
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 Long
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

Dim a As Integer
For a = 1 To 2

Sheets(a).Activate

MsgBox ActiveSheet.Name

With ActiveSheet

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 With

Next a
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

 

[igold]

Perhaps these two lines are not in a position to act on the second sheet properly...

Set cXref = New Collection
Call buildXREF

 

[Fluff]

Cross posted http://www.vbaexpress.com/forum/showthread.php?62849-Looping-Macro

Cross-Posting
While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules).
This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

 

[Beach Boy]

If you find my post in violation of your service use policy then I am requesting that my subscription to this forum be terminated effective immediately.