Copy and unfilter in VBA

[rjmdc]

hi
this is the code however in copying the row it scrambles as 4 columns are hidden so the copy paste is no correct.
where do i add unhide columns copy paste then rehide
columns hidden are F, L, M, N

Sub ReplaceCheck(sOldCheck As String, sNewCheck As String)
    
    Dim CheckRow            As Long
    Dim CheckRows()         As Long
    Dim nr                  As Long
    Dim cEX                 As New clsExcelSettings
    
    'Set Global
    gOldCheck = sOldCheck
    
    'Check If Old Check Exists and New Check Doesnt
    If CheckExists(sOldCheck) = False Then
        MsgBox "Old check number " & sOldCheck & " could not be located.  Process aborted", vbExclamation
        Exit Sub
    End If
    If CheckExists(sNewCheck) = True Then
        MsgBox "New check number " & sNewCheck & " has been used.  Process aborted", vbExclamation
        Exit Sub
    End If
    
    'Turn settings off
    cEX.SettingsOff
    
    'Find the Worksheet row for the old check
    CheckRows = GetCheckRows(sOldCheck)
    
    'Verify the payee information exists
    If GetLabels(Worksheets("Payments").Range("O" & CStr(CheckRows(1))))(1) = "Not found" Then
        MsgBox "Payee information could not be located.  Process Aborted"
        Exit Sub
    End If
    
    'Turn off events so all the worksheet code doesn't fire
    Application.EnableEvents = False
    
    'Unprotect
    Call WSUnProtect(Worksheets("Replaced Checks"))
    
    'Find the next row
    nr = Worksheets("Replaced Checks").Range("A1048576").End(xlUp).Row
    If nr = 2 And Worksheets("Replaced Checks").Cells(2, "A") = "" Then
        nr = 2
    Else
        nr = nr + 1
    End If
    
    'Loop All Checks in this payment
    For r = 1 To UBound(CheckRows)
        CheckRow = CheckRows(r)
        
        'Make a copy to Replaced Check WS
        Worksheets("Payments").Range("A" & CheckRow & ":AM" & CheckRow).Copy
here is where the issue is the hidden columns
        
        'Write Data to Replaced Checks and change check number
        With Worksheets("Replaced Checks")
            .Range(.Cells(nr, "A"), .Cells(nr, "AM")).PasteSpecial xlPasteValues
            .Cells(nr, "E") = sNewCheck
            .Cells(nr, "G") = "Replaced Check " & sOldCheck & " with " & sNewCheck & " on " & Date
            .Cells(nr, "AN") = sOldCheck
        End With

        'Alter Payments
        With Worksheets("Payments")
            .Cells(CheckRow, "E") = sNewCheck
            .Cells(CheckRow, "G") = "Replaced Check " & sOldCheck & " with " & sNewCheck & " on " & Date
        End With
        
        'Advance nr
        nr = nr + 1
    Next r
    
    'Protect
    Call WSProtect(Worksheets("Replaced Checks"))
        
    'Fire Check Entry Code
    With Worksheets("Payments")
        Call .CheckMailed(.Cells(CheckRow, "E"), 9999999, False, False, False)
    End With
    
    'Enable
    Application.EnableEvents = True
    
    'Turn settings On
    cEX.SettingsOn
End Sub

 

[Alex Blakenburg]

Try replacing this line:

    Worksheets("Payments").Range("A" & checkrow & ":AM" & checkrow).Copy

With these lines:

        With Worksheets("Payments")
            .Range("F1,L1,M1,N1").EntireColumn.Hidden = False
            .Range("A" & checkrow & ":AM" & checkrow).Copy
            .Range("F1,L1,M1,N1").EntireColumn.Hidden = True
        End With