VBA Code needed

A user will select a cell in a worksheet. After the cell is selected, I need code that will:

1. Hide columns Blue:Green (where "Blue" and "Green" are each names that I've defined for the columns in the worksheet)

2. Copy the visible cells in the row that the user had selected

3. Insert the copied data to the second row on a sheet that's been named "Paid"

Thanks.
carlosabAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Martin LissOlder than dirtCommented:
You forgot to attach the workbook.
0
carlosabAuthor Commented:
Sorry. Attached now. Also, for step 3, when it inserts the data, it should move down the cells that were in the second row on sheet "Paid".
Exchange.xlsx
0
Martin LissOlder than dirtCommented:
Sub Paid()

Dim ws As Worksheet
Dim lngCol As Long
Dim cel As Range
Dim rng As Range

Set ws = Worksheets("Main")
With ws
    Worksheets("Paid").Rows("2:2").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    .Columns(3).EntireColumn.Hidden = True
    .Columns(4).EntireColumn.Hidden = True
    .Columns(5).EntireColumn.Hidden = True
    
    Set rng = Selection.Cells.SpecialCells(xlCellTypeVisible)
    For Each cel In rng
        lngCol = lngCol + 1
        Worksheets("Paid").Cells(2, lngCol) = cel
    Next
End With
End Sub

Open in new window

0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

carlosabAuthor Commented:
Thanks Martin. I expect additional columns to be periodically added between Blue and Green. So, what I really need is code that uses those defined names when selecting the range to hide. Thanks again.
0
Martin LissOlder than dirtCommented:
Sub Paid()

Dim ws As Worksheet
Dim lngCol As Long
Dim cel As Range
Dim rng As Range

Set ws = Worksheets("Main")
With ws
    Worksheets("Paid").Rows("2:2").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    .Range("Blue" & ":" & "Green").EntireColumn.Hidden = True
    Set rng = Selection.Cells.SpecialCells(xlCellTypeVisible)
    For Each cel In rng
        lngCol = lngCol + 1
        Worksheets("Paid").Cells(2, lngCol) = cel
    Next
End With
End Sub

Open in new window

0
carlosabAuthor Commented:
When i tried this code, it copied data from all of the rows (instead of from just the one row that the user had selected). Thanks.
0
Martin LissOlder than dirtCommented:
Did I do my selection wrong? Here are before and after when I do it.
BeforeAfter
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
carlosabAuthor Commented:
The wording in my request was ambiguous, so, I've awarded points and posted a new question here that more explicitly explains that the user is only going to select one cell (not the whole row). When I run  your code after selecting the whole row, it works (though it takes about 5 seconds). But, if I run it when selecting just one cell, it doesn't work right.

Here is the link to the new question. Thanks!

http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_28227507.html
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.