Forum Discussion

Copper Contributor

Mar 09, 2024

Solved

Copy data into worksheet with hidden rows but want only visible cells to recieve...

I am moving data from one Excel file to another by copying it, but in the receiving Excel workbook, some lines are filtered away and hidden (part of a long series of historical data but no longer rec...

excel

Formulas and Functions

NikolinoDE

Mar 10, 2024

Luma1

I'm still not sure what exactly is meant (the translation software is probably overwhelmed), but here is a VBA code as an example with a test file.

Sub CopyVisibleCellsOnly()
    Dim sourceRange As Range
    Dim targetRange As Range
    Dim cell As Range
    
    ' Set the source range to be copied (adjust as needed)
    Set sourceRange = ThisWorkbook.Sheets("Quelldaten").Range("A1:A100")
    
    ' Set the target range where data should be pasted (adjust as needed)
    Set targetRange = ThisWorkbook.Sheets("Empfangsbereich").Range("A1:A100")
    
    ' Loop through each cell in the source range
    For Each cell In sourceRange
        ' Check if the corresponding row in the target range is visible
        If Not targetRange.Rows(cell.Row).Hidden Then
            ' If visible, copy the value from the source cell to the target cell
            targetRange.Cells(cell.Row, 1).Value = cell.Value
        End If
    Next cell
End Sub

Hope this is what you want.

copy with out hiden.xlsx13 KB

NikolinoDE

Gold Contributor

Mar 09, 2024

Luma1

To copy data from one Excel workbook to another while ensuring that it is only pasted into visible cells, you can use the "Paste Special" feature with the "Skip Blanks" option. Here's how you can do it:

Select the range of data that you want to copy in the source Excel workbook.
Press Ctrl + C to copy the selected data.
Switch to the destination Excel workbook.
Select the cell where you want to paste the data.
Click on the "Home" tab in the Excel ribbon.
Click on the small arrow under the "Paste" button to open the "Paste Special" menu.
In the "Paste Special" menu, select "Values" from the options.
Check the box next to "Skip Blanks".
Click "OK" to paste the data.

By selecting "Values" and checking "Skip Blanks", Excel will only paste the data into visible cells, ignoring any hidden or filtered cells in the destination worksheet. This way, you can ensure that the data is only posted to the visible cells without affecting the hidden rows. The text was created with the help of AI.

My answers are voluntary and without guarantee!

Hope this will help you.

Was the answer useful? Mark as best response and like it!

This will help all forum participants.

Luma1

Copper Contributor

Mar 10, 2024

I did not get this to work, though good to learn more about Excel. See my response to Sergei with some more explanation.
Thanks

NikolinoDE

Gold Contributor

Mar 10, 2024

Luma1

I'm still not sure what exactly is meant (the translation software is probably overwhelmed), but here is a VBA code as an example with a test file.

Sub CopyVisibleCellsOnly()
    Dim sourceRange As Range
    Dim targetRange As Range
    Dim cell As Range
    
    ' Set the source range to be copied (adjust as needed)
    Set sourceRange = ThisWorkbook.Sheets("Quelldaten").Range("A1:A100")
    
    ' Set the target range where data should be pasted (adjust as needed)
    Set targetRange = ThisWorkbook.Sheets("Empfangsbereich").Range("A1:A100")
    
    ' Loop through each cell in the source range
    For Each cell In sourceRange
        ' Check if the corresponding row in the target range is visible
        If Not targetRange.Rows(cell.Row).Hidden Then
            ' If visible, copy the value from the source cell to the target cell
            targetRange.Cells(cell.Row, 1).Value = cell.Value
        End If
    Next cell
End Sub

Hope this is what you want.

copy with out hiden.xlsx13 KB

Luma1
Copper Contributor
Mar 10, 2024
I think this could work but I am using to much force for my problem, I will have to simplify my workprocess.
Thanks for your effort