Pasting Into Visible Cells Only

[ModerCreed]

Hey guys,

I am new here and have been searching around the internet for a solution to this all morning and although people have asked this question none of the solutions are working for me.

I am using excel 2007 on a 64 bit windows 7 machine if that matters.

Anyways, I am trying to copy a range of cells from one workbook and then paste that range into another workbook, but ONLY on the visable rows in that notebook.

The first workbook has no hidden rows so I don't need to do any go-to specials to copy them, but the second one of course does have them.

It seems I am going to need some sort of macro to do this, but I am not super experienced yet with how to make them. Is is possible that someone could provide me guidance here?

I will rephrase my question another way in case it's confusing:

This is a simplified example.

- Workbook 1 has column A with cells 1-10 with data in them that are going to be copied

- Workbook 2 also has a column A, but it has information in cells 1-15 with cells 6-10 hidden from view.

- The copied data from workbook one needs to only land on the visible cells (read: cells 1-5 and cells 10-15)

Thanks for any help in advance!

 

[adam087]

Hi ModerCreed

I created this code which (seems to) work although I suspect there's a hotter solution out there. First select the source range and run the SpecialCopy macro, then select the destination range (you need to select the whole range not just the upper-left cell) and run SpecialPaste.

Option Explicit

Dim CopyRange As Range
     Public Sub SpecialCopy()
Set CopyRange = Selection

End Sub

Public Sub SpecialPaste()

If CopyRange Is Nothing Then
    MsgBox "Nothing to paste"
    Exit Sub
End If
If CopyRange.Columns.Count <> Selection.Columns.Count Then
    MsgBox "Different number of columns selected"
    Exit Sub
End If

Dim DestRange As Range
Set DestRange = Selection

Dim i As Integer, r As Integer, x As Integer
r = 1

For i = 1 To DestRange.Rows.Count
    If DestRange.Rows(i).Height > 0 Then
        For x = 1 To CopyRange.Columns.Count
            DestRange.Cells(i, x).Value = CopyRange.Cells(r, x).Value
        Next x
        r = r + 1
    End If
    If r > CopyRange.Rows.Count Then Exit Sub
Next i

End Sub

Here's the output I got:

Excel 2010
	A	B
1	Source_c_1	Source_c_2
2	1	A
3	2	B
4	3	C
5	4	D
6	5	E
7	6	F
8	7	G
9	8	H
10	9	I
11	10	J
12
13
14
15	Dest_c_1	Dest_c_2
16	1	A
17	2	B
18	HIDDEN	HIDDEN
19	HIDDEN	HIDDEN
20	HIDDEN	HIDDEN
21	3	C
22	HIDDEN	HIDDEN
23	4	D
24	5	E
25	HIDDEN	HIDDEN
26	HIDDEN	HIDDEN
27	6	F
28	7	G
29	8	H
30	9	I
31	10	J
Sheet1

Let me know if you have any questions.

Regards
Adam

 

[ModerCreed]

Thanks so much Adam!

That was exactly the code I needed!

I really appreciate it.

 

[adam087]

No problem. Glad it did what you needed; and thanks for the feedback.

Regards
Adam

 

[zigojacko]

Spent ages to try and find a solution to paste range only into visible cells/rows and your macro was the only thing that managed to work. Thanks very much!

 

[ginathies8]

This is exactly what I need but I am a newbie. Is there anyone out there kind enough and patient enough to step me through how this works? I copied the code into a module. Now what?