Export Hidden Columns to New Worksheet

[Ray789]

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:

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?

 

[AlphaFrog]

Do you just want to unHide column C?
Dest.Columns("C:C").Hidden = False

Or Unhide all columns
Dest.Cells.EntireColumn.Hidden = False

 

[Ray789]

Dest.Columns("C:C").Hidden = TRUE worked<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
I just wanted to ask how I can embed a '=CONCATENATE(AP5," ",AP1)' which only displays in the Export Sheet when the Macro is run. The CONCAT would have to formulate on cells AP5 to FH5.<o></o>
<o></o>
I would want to also run a Vlookup to perform a task and want to know where in the code this would fit in and whether I would have to change the Vlookup standard formulae.<o></o>

Thanks for your help before Alpha Frog.

 

[AlphaFrog]

If you want to put this formula in a cell using VBA
=CONCATENATE(AP5," ",AP1)

This would put it in cell A1

Range("A1").Formula = "=CONCATENATE(AP5,"" "",AP1)"

If you just want the result of that formula in cell A1...

Range("A1").Value = Range("AP5").Value & " " & Range("AP1").Value

If you want to use the VLookup function in VBA, it would be something like this...

Range("A1") = Application.Vlookup(Range("D1"), Range("E1:F100"), 2, 0)

You could also use the .Find method to locate a value in a range and then offset from that location to get the lookup value. This is a more efficient method than using Vlookup if you have a lot of searches to do.

 

[Ray789]

Hi,

In my export I have a range
'.Range("F:N").Copy Dest.Range("E1")' which copies all the columns from F to N to the export columns following "E" which works fine.

What I want to do is to have a lookup which:

1 - In row 5 checks all values in range "F:N" to see if a value exists in row 3

2- If the value exists then do a look up against Sheet7 and return the corresponding value of Sheet7 Column 3 in the export replacing whatever entry existed in the range F:N (Row 5). I believe a simple look up would not work.

I could not attach a sample copy of the code and output to make this clearer. I have attached the code again which has been amended slightly.

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("Sheet1")
    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("F:N").Copy Dest.Range("E1")
End With
Set Dest = Nothing
End Sub

: