Adding a Call command to an existing VBA

I am not sure if this needs a new thread or not so i posted a new one just in case.

I had a previous thread that repeates the same set of commands on all visible sheets located here:

http://www.excelforum.com/excel-gene...n-combine.html

The one problem i am having with the code is i need to add a Call command ( Call Copy_Punches ) to run on each visible sheet and im not sure where to put it. Here is the code i am trying but it only Calls it on the first visible sheet and it does not call it on the rest of the visible sheets. Where should i move it so it works properly on each sheet.

Sub Generate_CSV()
    'Application.ScreenUpdating = False
    
    Dim wks         As Worksheet
    Dim j           As Long

    Sheets("Customer Info").Visible = False
    Set wks = Worksheets("CSV_Export")
        Worksheets("CSV_Export").Visible = True
        Worksheets("CSV_Export").Select
        ActiveSheet.Unprotect Password:="2010Turbo"
        
        ' Clears out previous run of Copy_Sort command
        
        Worksheets("CSV_Export").Range("A1:IV200").ClearContents
        Worksheets("CSV_Export").Range("A1").Value = "*** JOB NUMBER"
        Worksheets("CSV_Export").Range("A3").Value = "*** PUNCH PATTERNS"
        Worksheets("CSV_Export").Range("A4").Value = "*** MEMBER DEFINITIONS"
        
        Worksheets("CSV_Export").Visible = False
        Call Select_First_Visible_Sheet
        
        With wks
            For j = 1 To Worksheets.Count
                If Worksheets(j).Visible = xlSheetVisible Then ' shg added this
                    
                   Call Copy_Punches ' I need this to run on all visible sheets _
                                        before it performs the copy/paste below.
                                        
                .Unprotect
                         Worksheets(j).Range("G101:IC112").Copy
                .Cells(.Rows.Count, "A").End(xlUp).Offset(1).PasteSpecial _
                        Paste:=xlPasteValues, _
                        Operation:=xlNone, _
                        SkipBlanks:=False, _
                        Transpose:=False
                .Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
                
                Range("A10").Select
                
            End If
        Next j
    End With

    Application.CutCopyMode = False
    Application.ScreenUpdating = True
    
    End Sub

Where's the code for Copy_Punches?

Try this:

Sub Generate_CSV()
    Dim wksCSV      As Worksheet
    Dim wks         As Worksheet
    Dim i           As Long

    Sheets("Customer Info").Visible = xlSheetHidden

    Set wksCSV = Worksheets("CSV_Export")
    With wksCSV
        .Unprotect Password:="2010Turbo"
        .Rows("1:200").ClearContents
        .Range("A1").Value = "*** JOB NUMBER"
        .Range("A3").Value = "*** PUNCH PATTERNS"
        .Range("A4").Value = "*** MEMBER DEFINITIONS"
    End With

    For i = 1 To Worksheets.Count
        Set wks = Worksheets(i)
        With wks
            If .Visible = xlSheetVisible Then
                CopyPunches wks
                .Unprotect
                .Range("G101:IC112").Copy
                wksCSV.Cells(.Rows.Count, "A").End(xlUp).Offset(1).PasteSpecial _
                        Paste:=xlPasteValues
                .Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
            End If
        End With
    Next i
End Sub

Sub CopyPunches(wks As Worksheet)
    With wks
        .Unprotect Password:="**********"
        
        .Range("W70:AH81").Copy    'Standard Punches
        .Range("A86").PasteSpecial Paste:=xlPasteValues
        
        .Range("E55:P65").Copy    ' Continue Test 1
        .Range("M86").PasteSpecial Paste:=xlPasteValues
        
        .Range("E70:P79").Copy    ' Continue Test 2
        .Range("Y86").PasteSpecial Paste:=xlPasteValues
        
        .Range("V33:AN44").Copy    ' Leading Punch
        .Range("AK86").PasteSpecial Paste:=xlPasteValues
        
        .Range("V46:AN57").Copy    ' Trailing Punch
        .Range("BD86").PasteSpecial Paste:=xlPasteValues

        .Protect Password:="**********"
    End With
End Sub

You need to be consistent with the passwords on the visible sheets, and it appears you are not.

I fixed the passwords in the vba code and it seems your new code is working perfectly. I made a tweak to it so i can see a specific sheet again.


As always Shg, thanks for your help!