asked on

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.

Martin Liss

You forgot to attach the workbook.

carlosab

ASKER

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

Martin Liss

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

carlosab

ASKER

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.

Martin Liss

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

carlosab

ASKER

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.

ASKER CERTIFIED SOLUTION

Martin Liss

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

carlosab

ASKER

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!

https://www.experts-exchange.com/questions/28227507/VBA-Code-needed.html