Copy Raw Data and Paste into Visible Columns In Destination Sheet

Question

I am trying to copy data from a fixed range in one sheet and paste into the visible cells/columns of another sheet by last visible row. The VBA executes but only pastes the first source value and stops. It should run the For statement and paste each source range value into cells skipping hidden columns:

Sub copy_visible_cells()

Dim sourceRange As Range
Dim destRange As Range
Dim cell As Range
Dim i As Integer
dest_old_lrow = Sheets("Raw Data Inputs").Cells(Rows.Count, "A").End(xlUp).Row

' Define your source and destination ranges
Set sourceRange = Sheets("Automated Raw Data").Range("A88:BW90")
Set destRange = Sheets("Raw Data Inputs").Cells(dest_old_lrow + 1, 1)

i = 1
For Each cell In destRange
    If cell.EntireColumn.Hidden = False Then
        cell.Value = sourceRange.Cells(i).Value
        i = i + 1
    End If
    If i > sourceRange.Cells.Count Then Exit For
Next cell
End Sub

I know my modification caused the For statement to stop looping but not sure why. Any help would be greatly appreciated!!

Answer 1

Pls try

Sub copy_visible_cells()
    Dim sourceRange As Range
    Dim destRange As Range
    Dim rngCol As Range
    Dim dest_old_lrow As Long
    dest_old_lrow = Sheets("Raw Data Inputs").Cells(Rows.Count, "A").End(xlUp).Row
    
    ' Define your source and destination ranges
    Set sourceRange = Sheets("Automated Raw Data").Range("A88:BW90")
    
    Dim ColCnt As Long: ColCnt = Sheets("Raw Data Inputs").Columns.Count
    Dim RowCnt As Long: RowCnt = sourceRange.Rows.Count
    
    Set destRange = Sheets("Raw Data Inputs").Cells(dest_old_lrow + 1, 1).Resize(RowCnt, 1)
    
    Application.ScreenUpdating = False
    ' loop through columns in sourceRange
    For Each rngCol In sourceRange.Columns
        Do While destRange.EntireColumn.Hidden ' locate visible column on dest sheet
            If destRange.Column = ColCnt Then Exit Sub ' exit if destRange is on the last column
            Set destRange = destRange.Offset(0, 1)
        Loop
        ' copy data to dest.
        destRange.Value = rngCol.Value
        If destRange.Column = ColCnt Then Exit Sub
        Set destRange = destRange.Offset(0, 1) ' move to next col.
    Next rngCol
    Application.ScreenUpdating = True
End Sub