Pasting over hidden columns in Excel

Sub PasteOverHidden()

Dim col As Range
Dim intCol As Integer
Dim wsSource As Worksheet
Dim wsDest As Worksheet

Set wsSource = ThisWorkbook.Sheets("Sheet6")
Set wsDest = ThisWorkbook.Sheets("FS_Item")

' Don't update the screen until we're done. This
' speeds things up and avoids any flickering
Application.ScreenUpdating = False

' Ititialize the variable that will be used to keep track
' of the next column to write to
intCol = 1

' Precess every column in the source sheet
For Each col In wsSource.UsedRange.Columns
    If wsDest.Columns(intCol).Hidden = False Then
        ' The destimation column represented by intCol isn't hidden so
        ' copy contents of the source column represented by col to
        ' the cell in the rirst row of the desination column. Note that
        ' the whole column is copied and not just the first cell.
        col.Copy Destination:=wsDest.Cells(1, intCol)
        ' Set up the next column to be written to
        intCol = intCol + 1
    Else
        ' The column is represented bu intCol is hidden
        ' so loop through the columns until a non-hidden
        ' one is found
        Do Until wsDest.Columns(intCol).Hidden = False
            intCol = intCol + 1
        Loop
        ' Copy to that column
        col.Copy Destination:=wsDest.Cells(1, intCol)
        ' Set up the next column to be written to
        intCol = intCol + 1
    End If
Next

Application.ScreenUpdating = True
End Sub

Select allOpen in new window