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

[Luma1]

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 receiving new data). When I copy, data is posted to all cells, viewable as well as hidden but should only be posted to the visible ones. How can this be achieved?

My fall back would be to sort them to be in the bottom, but since this will happen in several places etc it is not desirable.

[#2]

@Luma1 

How do you do that?

If I have source data as

filter on first column hiding b

copy all above and paste into another file, result is

[#3]

@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:

1. Select the range of data that you want to copy in the source Excel workbook.
2. Press Ctrl + C to copy the selected data.
3. Switch to the destination Excel workbook.
4. Select the cell where you want to paste the data.
5. Click on the "Home" tab in the Excel ribbon.
6. Click on the small arrow under the "Paste" button to open the "Paste Special" menu.
7. In the "Paste Special" menu, select "Values" from the options.
8. Check the box next to "Skip Blanks".
9. 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.

[#4]

@NikolinoDE 

Skip Blanks do not overwrite existing values with blanks, that's not about hidden rows.

[#5]

@Sergei Baklan 

Thanks, but I need to explain further.

I may have been unclear.

The source data can be copied just like that, e g  value in cell A1, A2, A3, A4

The receiving area is the issue and has rows 1,2,3,4,5 and 6 where row 2 and 3 are hidden and not supposed to receive any value e g  Row one= A1, Row 3 = A2, Row 3 = A3 etc. No value to hidden rows 3 and 3

I need Excel to jump the hidden rows.

[#6]

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

[#7 · Accepted solution]

@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.

[#8]

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

[#9]

@Luma1 

I see, that opposite situation.

If to skip both hidden rows in target and related values in source that could be done with keyboard. Otherwise with script.