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 certain it will not be any fun. Thank you for your help!

 

I'm trying to paste a 15000+ line column of numbers from one spreadsheet to another. In the original spreadsheet the data in column is consecutive (not filtered and no spaces between the numbers in the column.) I need to take the numbers from the original spreadsheet and paste them to the final spreadsheet where each one line from the original spreadsheet needs to be on every 5th line of the final spreadsheet. On top of this, the 4 lines in the final spreadsheet that I'm trying to bypass are password protected. I hope I explained that correctly.    

 

I already tried running the code below with no luck. 

 

Sub PasteToVisibleCells()

    Dim rng As Range

    Dim inputValues As Range

    Dim cell As Range

    Dim i As Integer

    

    ' Define the range of the values you want to paste. You need to adjust this to your specific case.

    ' For instance, if your values are in column B from row 1 to 10, it should be: 

    ' Set inputValues = ThisWorkbook.Sheets("Sheet1").Range("B1:B10")

    Set inputValues = ThisWorkbook.Sheets("Sheet1").Range("B1:B10")

    

    ' Define the range where the values will be pasted

    Set rng = Selection

    

    i = 1

    

    ' Loop through each visible cell in the range and paste the values

    For Each cell In rng.SpecialCells(xlCellTypeVisible)

        If i <= inputValues.Cells.Count Then

            cell.Value = inputValues.Cells(i, 1).Value

            i = i + 1

        Else

            Exit For

        End If

    Next cell

End Sub

 

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