PASTE INTO JUST VISIBLES CELLS

J

JUAN

Hello,
is it possible to paste info into visibles cells only? I
have a spreadsheet that I have hidden cells so I only show
visibles cells, but I want to paste into those visibles
cells. Can this be done?

please advise any help would be appreciated.

Thanks,

Juan
 
F

Frank Kabel

Hi
try the following:
- after you have selected the range hit F5
- click 'Special' and choose 'only visible cells'

Now try pasting your values. Though you may have problems with
differing source and target ranges
 
J

Juan

Hello Frank,
thanks. What I actually did was first copy the visibles
cells into another sheet and then I paste my info. But the
end user wants to copy this into the original file. So my
suggestion was to perhaps delete those visibles cells and
then unhide and then copy to the other sheet. So I guess
there is no way to actually paste into the visibles cells
right?

please advise thanks,
juan
 
D

Dave Peterson

If you keep track of what visible cells were copied and where they should be
pasted, you could do it:

This worked for me under light testing

Option Explicit
Sub testme01()

Dim RngToCopy As Range
Dim RngToCopyV As Range
Dim destRng As Range
Dim destCell As Range
Dim myRow As Range
Dim myArea As Range
Dim oRow As Long

Set RngToCopy = Nothing
Set RngToCopyV = Nothing
On Error Resume Next
Set RngToCopy = Application.InputBox(prompt:="select a range", Type:=8)
Set RngToCopyV = RngToCopy.Cells.SpecialCells(xlCellTypeVisible)
On Error GoTo 0

If RngToCopyV Is Nothing Then
Exit Sub
End If

If Intersect(RngToCopy.EntireColumn, _
RngToCopy.Parent.Rows(1)).Areas.Count > 1 Then
MsgBox "please just one set of columns at a time"
Exit Sub
End If

Set destRng = Nothing
On Error Resume Next
Set destRng = Application.InputBox _
(prompt:="Select a range to paste", Type:=8)
On Error GoTo 0

If destRng Is Nothing Then
Exit Sub
End If

If Intersect(destRng.EntireColumn, _
destRng.Parent.Rows(1)).Areas.Count > 1 Then
MsgBox "please just one set of columns at a time"
Exit Sub
End If

If Intersect(destRng, _
destRng.Columns(1).Cells.SpecialCells(xlCellTypeVisible)) _
.Cells.Count _
< Intersect(RngToCopy, _
RngToCopy.Columns(1).Cells.SpecialCells(xlCellTypeVisible)) _
.Cells.Count Then
MsgBox "not enough visible rows in the paste-to range!"
Exit Sub
End If

oRow = 0
For Each myArea In RngToCopyV.Areas
For Each myRow In myArea.Rows
Do
If destRng.Offset(oRow, 0).Cells(1, 1).EntireRow.Hidden _
= False Then
Set destCell = destRng.Offset(oRow, 0).Cells(1, 1)
Exit Do
Else
oRow = oRow + 1
End If
Loop
myRow.Copy _
Destination:=destCell
oRow = oRow + 1
Next myRow
Next myArea

End Sub

But there's lots that can go wrong. I'd do a lot more testing before I released
it to a user.

But it might give you some ideas.
 
Top