Hi,
I have a Macro that exports selected columns to a new sheet. What I need included in the Macro is the ability to display certain columns as hidden in the exported worksheet i.e. Columns A,B,C,D are exported to the new sheet however Column C appears but is hidden. Please see the code below:
Can anyone help?
I have a Macro that exports selected columns to a new sheet. What I need included in the Macro is the ability to display certain columns as hidden in the exported worksheet i.e. Columns A,B,C,D are exported to the new sheet however Column C appears but is hidden. Please see the code below:
Code:
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?