computing
  • 0

Solved Problem In Pasting In Visible Cells Only From Other Sheet

  • 0

Hi team,
We got a big trouble while working on data in excel.
We tried to copy a list of values that are straight not hide or unhide.
And then paste to other sheet in same file in visible cells only by using command “=Sheet1!Cell
reference” and then Ctrl+enter.
It do copy only upto straight sequence like 1-8 and then 9th & 10th cell hide then 11th cell require value due to visible cells but it unable to do so.

So the basic problem occur due to copy from a file which is straight and paste to other only in visible cells only in other sheet.
But pasting wouldn’t consider the visible cells and paste the number in hide cells also.

Hope you understand my problem.
Kindly provide the solution asap

Share

1 Answer

  1. This is solution to problem.
    Just click Alt+F11 then click insert and then click module
    Copy below code :

    Sub CopyFilteredCells()
    'Updateby20150203
    Dim rng1 As Range
    Dim rng2 As Range
    Dim InputRng As Range
    Dim OutRng As Range
      xTitleId = "KutoolsforExcel"
       Set InputRng = Application.Selection
       Set InputRng = Application.InputBox("Copy Range :", xTitleId, _
                      InputRng.Address, Type:=8)
       Set OutRng = Application.InputBox("Paste Range:", xTitleId, Type:=8)
           For Each rng1 In InputRng
              rng1.Copy
             For Each rng2 In OutRng
               If rng2.EntireRow.RowHeight > 0 Then
                  rng2.PasteSpecial
                  Set OutRng = rng2.Offset(1).Resize(OutRng.Rows.Count)
                  Exit For
               End If
             Next
           Next
      Application.CutCopyMode = False
    End Sub

    Then press F5 to run then it ask to copy range and then paste range.
    Just click on copy what you would like to copy and then click paste range where would like to paste either visible or not

    Thanks team for further clarify the problem which helped me to find the above codes.

    • 0