copy row only visible columns

rjmdc

Well-known Member
Joined
Apr 29, 2020
Messages
744
Office Version
  1. 365
Platform
  1. Windows
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
 
Hello @rjmdc, Try next code:
VBA 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.
 
Upvote 0
Try This
VBA Code:
Worksheets("Data").Range("A" & CheckRow & ":AK" & CheckRow).SpecialCells(xlCellTypeVisible).Copy Sheets("Sheet3").Range("A" & nr)
 
Upvote 0

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
Back
Top