Macro to Hide Columns

Hi,

I have a Macro that exports selected columns to a new sheet. What I need included in the Macro is the ability to export columns to the new worksheet which are hidden i.e. Columns A,B,C,D are exported to the new sheet however Column C is hidden. Please see the code below:

Sub NewSheet()
Dim Dest    As Worksheet
Dim ColCopy As Long
Dim Val     As Long
Dim MyStrs  As Variant
Dim MyTrgt  As Variant
   
    If Evaluate("ISREF('Export Sheet'!A1)") Then
        MsgBox "The sheet Export Sheet already exists"
        Exit Sub
    Else
        Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = "Export Sheet"
    End If
   
Application.ScreenUpdating = False
Set Dest = Sheets("Export Sheet")

MyStrs = Array(" "First Name", "Last Name", "Email Address", "Contact Number")

MyTrgt = Array("A1", "B1", "C1", "D1")

On Error Resume Next
With Sheets("Main")

    For Val = LBound(MyStrs) To UBound(MyStrs)
        ColCopy = .Rows(5).Find(MyStrs(Val), After:=.Cells(5, .Columns.Count), _
            LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _
            SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Column
   
        If ColCopy > 0 Then
            .Columns(ColCopy).Copy Dest.Range(MyTrgt(Val))
            ColCopy = 0
        End If
    Next Val
.Range("AA:BA").Copy Dest.Range("E1")
End With

Set Dest = Nothing
End Sub

Can anyone help?

Addy

If you use find LookIn:=xlValues then excel will not check hidden cells, however if you change it to LookIn:=xlFormulas it will. Also, as you are copying the column as is it will end up in your destination column as hidden as well. If you want it visible on 'Export Sheet' you will need to unhide it after copying it over. Quick and dirty would be to add If Range(MyTrgt(Val)).EntireColumn.Hidden = True Then Range(MyTrgt(Val)).EntireColumn.Hidden = False after you copy it but before you reset ColCopy to 0. Full code with amendments below:

Sub NewSheet()

Dim Dest    As Worksheet

Dim ColCopy As Long

Dim Val     As Long

Dim MyStrs  As Variant

Dim MyTrgt  As Variant

    If Evaluate("ISREF('Export Sheet'!A1)") Then

        MsgBox "The sheet Export Sheet already exists"

        Exit Sub

    Else

        Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = "Export Sheet"

    End If

Application.ScreenUpdating = False

Set Dest = Sheets("Export Sheet")

MyStrs = Array("First Name", "Last Name", "Email Address", "Contact Number")

MyTrgt = Array("A1", "B1", "C1", "D1")

On Error Resume Next

With Sheets("Main")

    For Val = LBound(MyStrs) To UBound(MyStrs)

        ColCopy = .Rows(5).Find(MyStrs(Val), After:=.Cells(5, .Columns.Count), _

            LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, _

            SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Column

        If ColCopy > 0 Then

            .Columns(ColCopy).Copy Dest.Range(MyTrgt(Val))

            If Range(MyTrgt(Val)).EntireColumn.Hidden = True Then Range(MyTrgt(Val)).EntireColumn.Hidden = False

            ColCopy = 0

        End If

    Next Val

.Range("AA:BA").Copy Dest.Range("E1")

End With

Set Dest = Nothing

End Sub