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

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
- SergeiBaklan
  MVP
  Mar 09, 2024
  NikolinoDE
  
  Skip Blanks do not overwrite existing values with blanks, that's not about hidden rows.
SergeiBaklan
MVP
Mar 09, 2024
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
- Luma1
  Copper Contributor
  Mar 10, 2024
  SergeiBaklan
  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.
  - SergeiBaklan
    MVP
    Mar 10, 2024
    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.

Forum Discussion

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

Share

Resources