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!!
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