Solved Problem in pasting in visible cells only from other sheet

March 23, 2016 at 07:52:08
Specs: Windows 8
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


See More: Problem in pasting in visible cells only from other sheet

Report •


✔ Best Answer
March 26, 2016 at 21:31:01
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.



#1
March 23, 2016 at 12:46:34
To Copy & Paste only Visible cells, see if this helps:

https://support.office.com/en-ie/ar...

MIKE

http://www.skeptic.com/

message edited by mmcconaghy


Report •

#2
March 25, 2016 at 00:36:21
This is not my solution to answer.
Kindly do the needful again.

Report •

#3
March 25, 2016 at 07:32:05
We tried to copy a list of values

If you are trying to Copy a cell,
why are you using the formula "=Sheet1!CellReference"?

This make a duplicate, which will mirror the original,
so if a change is made on sheet1, i will also appear on your new sheet.

I'm not sure what it is you are trying to accomplish.

MIKE

http://www.skeptic.com/

message edited by mmcconaghy


Report •

Related Solutions

#4
March 25, 2016 at 09:04:35
Hi,
I know i have to use just ctrl + C and ctrl + V.
But the problem lies that i'd like to paste in visible cells only.

If I use ctrl+V then it couldn't pickup visible cells to paste
For that we have other way like if we want to paste in same sheet then we just use formula by " =Cell reference" and then enter and then drag. then it auto fill up the visible cells only.
But when i use second sheet for paste for that i will have to use "=Sheet No.!Cell reference"
But in that case it works only upto straight sequence and when ever any hide cells come it paste in that also.
In this i want to not pickup hide cells and only paste in visible cells.
As i have 1200 rows in which many hide cells there.
Hope you understand the same


Report •

#5
March 25, 2016 at 10:28:07
But the problem lies that i'd like to paste in visible cells only.

I don't understand what you mean by this?
Does your second sheet have hidden rows/columns?


Have you tried using the CTRL key to select non adjacent cells or ranges?

Select your first range of cells,
then hold the CTRL key, move your mouse to the second range you wish to copy
and select the second range.
Press CTRL-C to copy.
When you Paste, you will get only the two ranges you have selected.

MIKE

http://www.skeptic.com/


Report •

#6
March 25, 2016 at 10:52:05
Do the cells you are copying from sheet 1
have to be in the same cells on sheet 2?

So when you select two ranges, they need to maintain their relative position?

When you select the cell range Sheet 1 A1:A10 and Sheet 1 D4:D24
they have to be in Sheet 2 A1:A10 and Sheet 2 D4:D24?

MIKE

http://www.skeptic.com/


Report •

#7
March 25, 2016 at 11:47:30
If I am not mistaken, this is an example of what you are trying to do:

Sheet1 has data in A1:A10, which are all visible.

Sheet2 has hidden rows, e.g. A1:A8 are visible, A9:A10 are hidden, A11:A12 are visible.

Therefore you want the data in Sheet1!A1:A10 to end up in Sheet2!A1:A8, Sheet2!A11:A12.

If that is true, the only way I know how to do it is with a macro. If that is the case, I'd need to know if this is a "variable" requirement (meaning it will not always be the same rows that are Sheet1!Copied, Sheet2!Visible) or if it will always be the same rows.

Please clarify.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#8
March 25, 2016 at 20:35:58
Hi DerbyDad03 ,
Yeah that is exact problem i faced.
It is variable requirement and everytime change.
Sometimes one row hidden, sometimes 3-5-9 so on.
Also data also changes as per different files.

Kindly note that i'd like to forward the sheet to someone without any formula mentione like just paste special only values files. Where no formula exist.
Waiting for your urgent reply for this problem.
Thanks


Report •

#9
March 26, 2016 at 21:31:01
✔ Best Answer
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.


Report •

#10
August 25, 2016 at 03:27:12
Google sheets pastes only to visible cells. it's possible to carry out the copy paste in google sheets then copy back into excel.

Report •

Ask Question