Forum Discussion

crissmith's avatar
crissmith
Copper Contributor
May 31, 2023

Pasting over filtered cells where one of every 5 cells in a column are password protected.

Good morning!    I'm hoping someone can help with this, otherwise my next 2 days will consist of manually entering data from one spreadsheet to another. Not sure that will be any fun... In fact I'm...
excel
User Adoption
OliverScheurich's avatar
OliverScheurich
Gold Contributor
May 31, 2023

crissmith 

Sub PasteToVisibleCells()

    Dim i, j, k As Long

    Sheets("Sheet2").Range("C:C").Clear

    k = Sheets("Sheet1").Range("B" & Rows.Count).End(xlUp).Row
    j = 1
    
    For i = 1 To k

    Sheets("Sheet2").Cells(j, 3).Value = Sheets("Sheet1").Cells(i, 2).Value
    j = j + 5

    Next i

End Sub

Does this code return the intended result? The assumption is that the entries in Sheet1 start in cell B1. Row 7 of the code determines the last row with an entry. Then the range is looped and the values are returned in column 3 of Sheet2 by the     Sheets("Sheet2").Cells(j, 3).Value      command. The variable j is used to return each value while leaving 4 rows blank.

Resources