How do i paste data (which has no hidden cells) into a worksheet which has hidden columns

Question

Is it possible to prevent data being pasted into the hidden columns in one copy/paste movement rather than having to copy/paste multiple times?

Answer 1

I would like to suggest comparatively the best & fastest method to Paste data by skipping hidden Columns.

Before Copy & Paste:

After Paste:

N.B. As you can find the Macro has skipped the hidden Column H, while Paste data from Range A1:C9.

How it works:

• Hide Column/Columns, with this post Col H is hidden.

• Either Right Click the Sheet Tab & from poped up menu Click View Code, or press Alt+F11, to get the VB editor.

• Copy & Paste this VBA code(Macro) as Standard Module then RUN the Macro TestCopyPaste.

  Sub TestCopyPaste()
  
  CopySkippingHidden ActiveSheet.Range("A1:C9"), _
    ActiveSheet.Range("G1")
  
    End Sub
  

---

Sub CopySkippingHidden(rngToCopy As Range, pasteStart As Range)

    Dim c As Range


    For Each c In rngToCopy.Columns

        Do While pasteStart.EntireColumn.Hidden
            Set pasteStart = pasteStart.Offset(0, 1)
        Loop
        c.Copy pasteStart

        Set pasteStart = pasteStart.Offset(0, 1)

    Next c

End Sub

• ActiveSheet.Range("A1:C9") & ActiveSheet.Range("G1") are editable.
• Where A1:C9 is Source to be copied, and G1 is Target to Paste copied Data.
• You may adjust cell references/Data Range also.