copy row only visible columns

[rjmdc]

hi
this is my code in VBA
copy this sheet
Worksheets("Data").Range("A" & CheckRow & ":AK" & CheckRow).Copy
paste to new sheet
.Range(.Cells(nr, "A"), .Cells(nr, "AK")).PasteSpecial xlPasteValues

i have a few columns hidden on the master sheet that i am copyig from sheet data
therefore whn it pastes it is all off

how do i code to copy visible columns ony
and paste to the same visible columns on the new sheet

 

[MikeVol]

Hello @rjmdc, Try next code:

    Dim rngVisible  As Range

    Dim wsSource    As Worksheet
    Set wsSource = ThisWorkbook.Worksheets("Data")

    Dim CheckRow    As Long
    CheckRow = 2  ' Let's assume that you have "CheckRow" this is the second row, we don't know how you defined this variable

    With ThisWorkbook.Worksheets("NewSheet")

        On Error Resume Next   ' In case all columns are hidden
        Set rngVisible = wsSource.Range("A" & CheckRow & ":AK" & CheckRow).SpecialCells(xlCellTypeVisible)
        On Error GoTo 0        ' Turn error handling back on

        If Not rngVisible Is Nothing Then

            Dim rngTarget As Range
            Set rngTarget = .Cells(CheckRow, 1)

            ' Here we copy only the visible cells and paste them into a new sheet
            rngVisible.Copy
            rngTarget.PasteSpecial Paste:=xlPasteValues
            Application.CutCopyMode = False
        Else
            MsgBox "No visible data to copy!", vbExclamation
        End If

    End With

    Set rngTarget = Nothing
    Set rngVisible = Nothing
    Set wsSource = Nothing

I hope this is what you expect as a result and I was glad to help you. Good luck.

 

[kvsrinivasamurthy]

Try This

Worksheets("Data").Range("A" & CheckRow & ":AK" & CheckRow).SpecialCells(xlCellTypeVisible).Copy Sheets("Sheet3").Range("A" & nr)