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?
 

Some videos you may like

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,271
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,271
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
:
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,052
Messages
5,509,048
Members
408,701
Latest member
daz457

This Week's Hot Topics

  • Turn fraction around
    Hello I need to turn a fraction around, for example I have 1/3 but I need to present as 3/1
  • TIme Clock record reformatting to ???
    Hello All, I'd like some help formatting this (Tbl-A)(Loaded via Power Query) [ATTACH type="full" width="511px" alt="PQdata.png"]22252[/ATTACH]...
  • TextBox Match
    hi, I am having a few issues with my code below, what I need it to do is when they enter a value in textbox8 (QTY) either 1,2 or 3 the 3 textboxes...
  • Using Large function based on Multiple Criteria
    Hello, I can't seem to get a Large formula to work based on two criteria's. I can easily get a oldest value based one value, but I'm struggling...
  • Can you check my code please
    Hi, Im going round in circles with a Compil Error End With Without With Here is the code [CODE=rich] Private Sub...
  • Combining 2 pivot tables into 1 chart
    Hello everyone, My question sounds simple but I do not know the answer. I have 2 pivot tables and 2 charts that go with this. However I want to...
Top