Export Hidden Columns to New Worksheet

Ray789

New Member
Joined
Jul 26, 2008
Messages
4
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:

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?
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,416
Do you just want to unHide column C?
Dest.Columns("C:C").Hidden = False

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

Ray789

New Member
Joined
Jul 26, 2008
Messages
4
Dest.Columns("C:C").Hidden = TRUE worked<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:eek:ffice:eek:ffice" /><o:p></o:p>
<o:p></o:p>
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:p></o:p>
<o:p></o:p>
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:p></o:p>

Thanks for your help before Alpha Frog.
 

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,416
If you want to put this formula in a cell using VBA
=CONCATENATE(AP5," ",AP1)

This would put it in cell A1
Code:
Range("A1").Formula = "=CONCATENATE(AP5,"" "",AP1)"

If you just want the result of that formula in cell A1...
Code:
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...
Code:
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

New Member
Joined
Jul 26, 2008
Messages
4
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.

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("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
:
 

Watch MrExcel Video

Forum statistics

Threads
1,132,872
Messages
5,655,719
Members
418,233
Latest member
hussaind

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top