VBA code required to paste the range over another filtered range.

[mchilapur]

Dear all,
I have sheet1 and sheet2.
Sheet1 has filtered cells/rows (Out of 2000 cells, only 200 are visible).
I am copying equal number of rows i;e 200 from sheet2 (With some values) and trying to paste it against the visible 200 rows in sheet1 (Say in Column B).
Because sheet1 is filtered, all 200 values doesnt get pasted in sheet1m specially when filtered rows are not in sequence (It works only if filtered rows are in sequence).
Any solution to paste these values on visible 200 cells in sheet1??
Thanks for your help.

 

[skywriter]

Dear all,
I have sheet1 and sheet2.
Sheet1 has filtered cells/rows (Out of 2000 cells, only 200 are visible).
I am copying equal number of rows i;e 200 from sheet2 (With some values) and trying to paste it against the visible 200 rows in sheet1 (Say in Column B).
Because sheet1 is filtered, all 200 values doesnt get pasted in sheet1m specially when filtered rows are not in sequence (It works only if filtered rows are in sequence).
Any solution to paste these values on visible 200 cells in sheet1??
Thanks for your help.

Wow that's interesting, I didn't know of that issue. Thanks for the education.

I found some code on the internet and modified it slightly and it seems to work fine.

When you run the code you will get two input boxes. When the first pops up select the items to copy, this is a selectable type input box so just select the cells and the input box will fill in, hit okay then the second input box will pop up and you will select the paste range, which needs to be the same size of visible cells. If as you say the items you are copying are the same length as the visible cells this will be easy enough for you. When you click okay on the second input box after selection you should get what you want, the range pasted into the visible cells.

Good luck.

I don't want to assume you know how to install a macro so if you do don't be offended.

To install the macro hit Alt + F11, in the visual basic editor window that pops up, select Insert>Module and paste the code into the blank screen.

If you have Excel 2007 or later you need to save the file as a .xlsm type workbook and make sure on all versions you enable the macros.

Sub Copy_Filtered_Cells()
Dim too As Range
Dim from As Range
Dim c As Range
Dim thing As Range


    Set from = Application.InputBox("Select cells to copy ", Type:=8)
    Set too = Application.InputBox("Select range to past cell into, same size as copied cells", Type:=8)
    Application.ScreenUpdating = False
    
    For Each c In from
        c.Copy
        For Each thing In too
            If thing.EntireRow.RowHeight > 0 Then
                thing.PasteSpecial
                Set too = thing.Offset(1).Resize(too.Rows.Count)
                Exit For
            End If
        Next
    Next
  Application.ScreenUpdating = True
     
End Sub

 

[mchilapur]

Thanks for your response,

I tried your code, but it doesnt paste vales well...Still Output is wrong.

Please try is once again and make sure while testing : After you filter the data, rows numbering must not be continuous. And the point where this row continuity breaks, its stops updating the values..

 

[skywriter]

Thanks for your response,

I tried your code, but it doesnt paste vales well...Still Output is wrong.

Please try is once again and make sure while testing : After you filter the data, rows numbering must not be continuous. And the point where this row continuity breaks, its stops updating the values..

I tested it with my data and it worked fine. Without your sheet I can't fix the problem. I have no way of knowing if there is something different about your sheet that I don't know about nor do I know if you installed or are using the macro correctly.
So unless you want to share your sheet I can't help you any further.

I created a filter that hides some rows. I am able to copy data from another sheet directly into the visible only cells on the filtered sheet using the macro.

 

[mchilapur]

How can i send you the file...? Please let me know...This forum doesnt allow to attach a file.

 

[skywriter]

How can i send you the file...? Please let me know...This forum doesnt allow to attach a file.

https://www.dropbox.com/