Hello:

I was trying to find a way to paste into visible cells only. I found a macro
on another site and have pared it down to the following:

Sub VisbleCellsPasting()
On Error Resume Next

Dim aRng As Range
Dim aRn As Range
Dim bRng As Range

Set aRng = ActiveSheet.Range("B234:B273")

Range("F2").Select

For Each aRn In aRng
Set bRng = ActiveCell
If bRng.EntireRow.Hidden = False Then
bRng = aRn
bRng.Offset(1).Select
Else
Do
If ActiveCell.EntireRow.Hidden = True Then
ActiveCell.Offset(1).Select
End If
Loop Until ActiveCell.EntireRow.Hidden = False
ActiveCell = aRn
ActiveCell.Offset(1).Select
End If

Next

End Sub

This code works fine, but I have some issues...
The macro hard codes the selection that I need to paste and where I need to
paste it. I would like to be able to use the normal Excel copy (Ctrl+C) on my
data I need to paste (where ever it lives whether in a new workbook or not)
and click the macro button and have it paste where I need it. Is this
possible?

The following macro is not quite you described. I'm losing the contents of
the clipboard when I invoke the macro - not sure why. This macro copies a
selected cell to only the visible cells in a selected range, but you run it
with the cell selected that you want to copy. The macro prompts you to select
the range to which it should be pasted, then copies & pastes the starting
cell to the visible cells in the range.

Public Sub CopyToVisibleOnly()
'Declare local variables.
Dim cl As Range, Target As Range
Dim CopyRng As Range, ret
'Start with cell selected that you want to copy.
Set CopyRng = Selection
'Now select the range where it should be pasted.
Set Target = Application.InputBox _
(Prompt:="Select the Paste range", Type:=8)
For Each cl In Target
'Only cells in visible rows in the selected
'range are pasted.
If cl.EntireRow.Hidden = False Then
CopyRng.Copy
cl.Select
ActiveSheet.Paste
End If
Next cl
'Free the object variables.
Set CopyRng = Nothing
Set Target = Nothing
End Sub

I'll keep working on a solution that more closely meets your request.

Hope this helps,

Hutch

"WBTKbeezy" wrote:

Hello:

I was trying to find a way to paste into visible cells only. I found a macro
on another site and have pared it down to the following:

Sub VisbleCellsPasting()
On Error Resume Next

Dim aRng As Range
Dim aRn As Range
Dim bRng As Range

Set aRng = ActiveSheet.Range("B234:B273")

Range("F2").Select

For Each aRn In aRng
Set bRng = ActiveCell
If bRng.EntireRow.Hidden = False Then
bRng = aRn
bRng.Offset(1).Select
Else
Do
If ActiveCell.EntireRow.Hidden = True Then
ActiveCell.Offset(1).Select
End If
Loop Until ActiveCell.EntireRow.Hidden = False
ActiveCell = aRn
ActiveCell.Offset(1).Select
End If

Next

End Sub

This code works fine, but I have some issues...
The macro hard codes the selection that I need to paste and where I need to
paste it. I would like to be able to use the normal Excel copy (Ctrl+C) on my
data I need to paste (where ever it lives whether in a new workbook or not)
and click the macro button and have it paste where I need it. Is this
possible?

Yeah - I should have added the stuff I want pasted needs to be somewhere on
the same sheet - that is one of the problems I am trying to avoid. I would
love if it could be in another workbook first and copied only to the visible
cells of a new workbook.

I tried your code and it didn't quite work. It pasted (only if my data was
on that sheet first - an issue) but it pasted to every cell not just visible
ones.

"Tom Hutchins" wrote:

The following macro is not quite you described. I'm losing the contents of
the clipboard when I invoke the macro - not sure why. This macro copies a
selected cell to only the visible cells in a selected range, but you run it
with the cell selected that you want to copy. The macro prompts you to select
the range to which it should be pasted, then copies & pastes the starting
cell to the visible cells in the range.

Public Sub CopyToVisibleOnly()
'Declare local variables.
Dim cl As Range, Target As Range
Dim CopyRng As Range, ret
'Start with cell selected that you want to copy.
Set CopyRng = Selection
'Now select the range where it should be pasted.
Set Target = Application.InputBox _
(Prompt:="Select the Paste range", Type:=8)
For Each cl In Target
'Only cells in visible rows in the selected
'range are pasted.
If cl.EntireRow.Hidden = False Then
CopyRng.Copy
cl.Select
ActiveSheet.Paste
End If
Next cl
'Free the object variables.
Set CopyRng = Nothing
Set Target = Nothing
End Sub

I'll keep working on a solution that more closely meets your request.

Hope this helps,

Hutch

"WBTKbeezy" wrote:

Hello:

I was trying to find a way to paste into visible cells only. I found a macro
on another site and have pared it down to the following:

Sub VisbleCellsPasting()
On Error Resume Next

Dim aRng As Range
Dim aRn As Range
Dim bRng As Range

Set aRng = ActiveSheet.Range("B234:B273")

Range("F2").Select

For Each aRn In aRng
Set bRng = ActiveCell
If bRng.EntireRow.Hidden = False Then
bRng = aRn
bRng.Offset(1).Select
Else
Do
If ActiveCell.EntireRow.Hidden = True Then
ActiveCell.Offset(1).Select
End If
Loop Until ActiveCell.EntireRow.Hidden = False
ActiveCell = aRn
ActiveCell.Offset(1).Select
End If

Next

End Sub

This code works fine, but I have some issues...
The macro hard codes the selection that I need to paste and where I need to
paste it. I would like to be able to use the normal Excel copy (Ctrl+C) on my
data I need to paste (where ever it lives whether in a new workbook or not)
and click the macro button and have it paste where I need it. Is this
possible?

I'm guessing that you have hidden columns, not just hidden rows. I didn't
include code to test for that because your sample code only deals with hidden
rows. Here is a revised version that, again, is not a perfect solution, but
will copy the starting cell to only the visible cells in a selected range -
and the range can be in another open workbook if desired. I split the macro
into two parts. The first part calls the second, with a 5-second delay in
between. That's when you can switch to the other workbook or worksheet. Don't
select the paste range until the inputbox pops up.

Option Explicit
Public StartWB As Workbook
Public StartWS As Worksheet
Public CopyRng As String

Public Sub CopyToVisibleOnly1()
'Start with cell selected that you want to copy.
Set StartWB = ActiveWorkbook
Set StartWS = ActiveSheet
CopyRng = Selection.Address
'Call CopyToVisibleOnly2 after a five-second delay.
Application.OnTime Now() + TimeValue("0:00:05"), "CopyToVisibleOnly2"
End Sub

Private Sub CopyToVisibleOnly2()
'Declare local variables.
Dim EndWB As Workbook, EndWS As Worksheet
Dim cl As Range, Target As Range
'Now select the range where it should be pasted.
Set Target = Application.InputBox _
(Prompt:="Select the Paste range", Type:=8)
Set EndWB = ActiveWorkbook
Set EndWS = ActiveSheet
'Copy the cell from the original workbook.
StartWB.Activate
StartWS.Activate
Range(CopyRng).Copy
'Return to the target workbook.
EndWB.Activate
EndWS.Activate
'Check every cell in the seleced range.
For Each cl In Target
'Only cells in visible rows in the selected
'range are pasted.
If (cl.EntireRow.Hidden = False) And _
(cl.EntireColumn.Hidden = False) Then
cl.Select
ActiveSheet.Paste
End If
Next cl
'Free the object variables.
Set Target = Nothing
Set StartWB = Nothing
Set StartWS = Nothing
Set EndWB = Nothing
Set EndWS = Nothing
End Sub

Hope this helps,

Hutch

"WBTKbeezy" wrote:

Yeah - I should have added the stuff I want pasted needs to be somewhere on
the same sheet - that is one of the problems I am trying to avoid. I would
love if it could be in another workbook first and copied only to the visible
cells of a new workbook.

I tried your code and it didn't quite work. It pasted (only if my data was
on that sheet first - an issue) but it pasted to every cell not just visible
ones.

"Tom Hutchins" wrote:

The following macro is not quite you described. I'm losing the contents of
the clipboard when I invoke the macro - not sure why. This macro copies a
selected cell to only the visible cells in a selected range, but you run it
with the cell selected that you want to copy. The macro prompts you to select
the range to which it should be pasted, then copies & pastes the starting
cell to the visible cells in the range.

Public Sub CopyToVisibleOnly()
'Declare local variables.
Dim cl As Range, Target As Range
Dim CopyRng As Range, ret
'Start with cell selected that you want to copy.
Set CopyRng = Selection
'Now select the range where it should be pasted.
Set Target = Application.InputBox _
(Prompt:="Select the Paste range", Type:=8)
For Each cl In Target
'Only cells in visible rows in the selected
'range are pasted.
If cl.EntireRow.Hidden = False Then
CopyRng.Copy
cl.Select
ActiveSheet.Paste
End If
Next cl
'Free the object variables.
Set CopyRng = Nothing
Set Target = Nothing
End Sub

I'll keep working on a solution that more closely meets your request.

Hope this helps,

Hutch

"WBTKbeezy" wrote:

Hello:

I was trying to find a way to paste into visible cells only. I found a macro
on another site and have pared it down to the following:

Sub VisbleCellsPasting()
On Error Resume Next

Dim aRng As Range
Dim aRn As Range
Dim bRng As Range

Set aRng = ActiveSheet.Range("B234:B273")

Range("F2").Select

For Each aRn In aRng
Set bRng = ActiveCell
If bRng.EntireRow.Hidden = False Then
bRng = aRn
bRng.Offset(1).Select
Else
Do
If ActiveCell.EntireRow.Hidden = True Then
ActiveCell.Offset(1).Select
End If
Loop Until ActiveCell.EntireRow.Hidden = False
ActiveCell = aRn
ActiveCell.Offset(1).Select
End If

Next

End Sub

This code works fine, but I have some issues...
The macro hard codes the selection that I need to paste and where I need to
paste it. I would like to be able to use the normal Excel copy (Ctrl+C) on my
data I need to paste (where ever it lives whether in a new workbook or not)
and click the macro button and have it paste where I need it. Is this
possible?

Hutch:

This is cool...I understand what it is trying to do, but it is still pasting
to all cells not just visible ones. I do not actually have any hidden columns
and my data is only one column-width of data. The delay section, the copy and
input box all work correctly, but the final step of visible cells only does
not work for me. In case it helps I am running Excel 2003...

Would it be possible to have you create a sample workbook(s) for me to check
out, if this code is working for you?

Any thoughts...

"Tom Hutchins" wrote:

I'm guessing that you have hidden columns, not just hidden rows. I didn't
include code to test for that because your sample code only deals with hidden
rows. Here is a revised version that, again, is not a perfect solution, but
will copy the starting cell to only the visible cells in a selected range -
and the range can be in another open workbook if desired. I split the macro
into two parts. The first part calls the second, with a 5-second delay in
between. That's when you can switch to the other workbook or worksheet. Don't
select the paste range until the inputbox pops up.

Option Explicit
Public StartWB As Workbook
Public StartWS As Worksheet
Public CopyRng As String

Public Sub CopyToVisibleOnly1()
'Start with cell selected that you want to copy.
Set StartWB = ActiveWorkbook
Set StartWS = ActiveSheet
CopyRng = Selection.Address
'Call CopyToVisibleOnly2 after a five-second delay.
Application.OnTime Now() + TimeValue("0:00:05"), "CopyToVisibleOnly2"
End Sub

Private Sub CopyToVisibleOnly2()
'Declare local variables.
Dim EndWB As Workbook, EndWS As Worksheet
Dim cl As Range, Target As Range
'Now select the range where it should be pasted.
Set Target = Application.InputBox _
(Prompt:="Select the Paste range", Type:=8)
Set EndWB = ActiveWorkbook
Set EndWS = ActiveSheet
'Copy the cell from the original workbook.
StartWB.Activate
StartWS.Activate
Range(CopyRng).Copy
'Return to the target workbook.
EndWB.Activate
EndWS.Activate
'Check every cell in the seleced range.
For Each cl In Target
'Only cells in visible rows in the selected
'range are pasted.
If (cl.EntireRow.Hidden = False) And _
(cl.EntireColumn.Hidden = False) Then
cl.Select
ActiveSheet.Paste
End If
Next cl
'Free the object variables.
Set Target = Nothing
Set StartWB = Nothing
Set StartWS = Nothing
Set EndWB = Nothing
Set EndWS = Nothing
End Sub

Hope this helps,

Hutch

"WBTKbeezy" wrote:

Yeah - I should have added the stuff I want pasted needs to be somewhere on
the same sheet - that is one of the problems I am trying to avoid. I would
love if it could be in another workbook first and copied only to the visible
cells of a new workbook.

I tried your code and it didn't quite work. It pasted (only if my data was
on that sheet first - an issue) but it pasted to every cell not just visible
ones.

"Tom Hutchins" wrote:

The following macro is not quite you described. I'm losing the contents of
the clipboard when I invoke the macro - not sure why. This macro copies a
selected cell to only the visible cells in a selected range, but you run it
with the cell selected that you want to copy. The macro prompts you to select
the range to which it should be pasted, then copies & pastes the starting
cell to the visible cells in the range.

Public Sub CopyToVisibleOnly()
'Declare local variables.
Dim cl As Range, Target As Range
Dim CopyRng As Range, ret
'Start with cell selected that you want to copy.
Set CopyRng = Selection
'Now select the range where it should be pasted.
Set Target = Application.InputBox _
(Prompt:="Select the Paste range", Type:=8)
For Each cl In Target
'Only cells in visible rows in the selected
'range are pasted.
If cl.EntireRow.Hidden = False Then
CopyRng.Copy
cl.Select
ActiveSheet.Paste
End If
Next cl
'Free the object variables.
Set CopyRng = Nothing
Set Target = Nothing
End Sub

I'll keep working on a solution that more closely meets your request.

Hope this helps,

Hutch

"WBTKbeezy" wrote:

Hello:

I was trying to find a way to paste into visible cells only. I found a macro
on another site and have pared it down to the following:

Sub VisbleCellsPasting()
On Error Resume Next

Dim aRng As Range
Dim aRn As Range
Dim bRng As Range

Set aRng = ActiveSheet.Range("B234:B273")

Range("F2").Select

For Each aRn In aRng
Set bRng = ActiveCell
If bRng.EntireRow.Hidden = False Then
bRng = aRn
bRng.Offset(1).Select
Else
Do
If ActiveCell.EntireRow.Hidden = True Then
ActiveCell.Offset(1).Select
End If
Loop Until ActiveCell.EntireRow.Hidden = False
ActiveCell = aRn
ActiveCell.Offset(1).Select
End If

Next

End Sub

This code works fine, but I have some issues...
The macro hard codes the selection that I need to paste and where I need to
paste it. I would like to be able to use the normal Excel copy (Ctrl+C) on my
data I need to paste (where ever it lives whether in a new workbook or not)
and click the macro button and have it paste where I need it. Is this
possible?

I'm also using xl2003, and this code works perfectly for me. I was only
copying a single cell to a range to cells when I tested it. If your starting
cell is a range of cells spanning more than one row, then some data will get
pasted to hidden cells - not when the hidden cells are evaluated, but when
the cells above them are. Maybe your request is slightly different than I
understood.

Is what you want to do like this: Copy A1:A10 from one sheet and paste it
to A1:A15 in another workbook where rows 6-10 are hidden? If that's what you
want, I think I can help you, but it will require a different solution.

Hutch

"WBTKbeezy" wrote:

Hutch:

This is cool...I understand what it is trying to do, but it is still pasting
to all cells not just visible ones. I do not actually have any hidden columns
and my data is only one column-width of data. The delay section, the copy and
input box all work correctly, but the final step of visible cells only does
not work for me. In case it helps I am running Excel 2003...

Would it be possible to have you create a sample workbook(s) for me to check
out, if this code is working for you?

Any thoughts...

"Tom Hutchins" wrote:

I'm guessing that you have hidden columns, not just hidden rows. I didn't
include code to test for that because your sample code only deals with hidden
rows. Here is a revised version that, again, is not a perfect solution, but
will copy the starting cell to only the visible cells in a selected range -
and the range can be in another open workbook if desired. I split the macro
into two parts. The first part calls the second, with a 5-second delay in
between. That's when you can switch to the other workbook or worksheet. Don't
select the paste range until the inputbox pops up.

Option Explicit
Public StartWB As Workbook
Public StartWS As Worksheet
Public CopyRng As String

Public Sub CopyToVisibleOnly1()
'Start with cell selected that you want to copy.
Set StartWB = ActiveWorkbook
Set StartWS = ActiveSheet
CopyRng = Selection.Address
'Call CopyToVisibleOnly2 after a five-second delay.
Application.OnTime Now() + TimeValue("0:00:05"), "CopyToVisibleOnly2"
End Sub

Private Sub CopyToVisibleOnly2()
'Declare local variables.
Dim EndWB As Workbook, EndWS As Worksheet
Dim cl As Range, Target As Range
'Now select the range where it should be pasted.
Set Target = Application.InputBox _
(Prompt:="Select the Paste range", Type:=8)
Set EndWB = ActiveWorkbook
Set EndWS = ActiveSheet
'Copy the cell from the original workbook.
StartWB.Activate
StartWS.Activate
Range(CopyRng).Copy
'Return to the target workbook.
EndWB.Activate
EndWS.Activate
'Check every cell in the seleced range.
For Each cl In Target
'Only cells in visible rows in the selected
'range are pasted.
If (cl.EntireRow.Hidden = False) And _
(cl.EntireColumn.Hidden = False) Then
cl.Select
ActiveSheet.Paste
End If
Next cl
'Free the object variables.
Set Target = Nothing
Set StartWB = Nothing
Set StartWS = Nothing
Set EndWB = Nothing
Set EndWS = Nothing
End Sub

Hope this helps,

Hutch

"WBTKbeezy" wrote:

Yeah - I should have added the stuff I want pasted needs to be somewhere on
the same sheet - that is one of the problems I am trying to avoid. I would
love if it could be in another workbook first and copied only to the visible
cells of a new workbook.

I tried your code and it didn't quite work. It pasted (only if my data was
on that sheet first - an issue) but it pasted to every cell not just visible
ones.

"Tom Hutchins" wrote:

The following macro is not quite you described. I'm losing the contents of
the clipboard when I invoke the macro - not sure why. This macro copies a
selected cell to only the visible cells in a selected range, but you run it
with the cell selected that you want to copy. The macro prompts you to select
the range to which it should be pasted, then copies & pastes the starting
cell to the visible cells in the range.

Public Sub CopyToVisibleOnly()
'Declare local variables.
Dim cl As Range, Target As Range
Dim CopyRng As Range, ret
'Start with cell selected that you want to copy.
Set CopyRng = Selection
'Now select the range where it should be pasted.
Set Target = Application.InputBox _
(Prompt:="Select the Paste range", Type:=8)
For Each cl In Target
'Only cells in visible rows in the selected
'range are pasted.
If cl.EntireRow.Hidden = False Then
CopyRng.Copy
cl.Select
ActiveSheet.Paste
End If
Next cl
'Free the object variables.
Set CopyRng = Nothing
Set Target = Nothing
End Sub

I'll keep working on a solution that more closely meets your request.

Hope this helps,

Hutch

"WBTKbeezy" wrote:

Hello:

I was trying to find a way to paste into visible cells only. I found a macro
on another site and have pared it down to the following:

Sub VisbleCellsPasting()
On Error Resume Next

Dim aRng As Range
Dim aRn As Range
Dim bRng As Range

Set aRng = ActiveSheet.Range("B234:B273")

Range("F2").Select

For Each aRn In aRng
Set bRng = ActiveCell
If bRng.EntireRow.Hidden = False Then
bRng = aRn
bRng.Offset(1).Select
Else
Do
If ActiveCell.EntireRow.Hidden = True Then
ActiveCell.Offset(1).Select
End If
Loop Until ActiveCell.EntireRow.Hidden = False
ActiveCell = aRn
ActiveCell.Offset(1).Select
End If

Next

End Sub

This code works fine, but I have some issues...
The macro hard codes the selection that I need to paste and where I need to
paste it. I would like to be able to use the normal Excel copy (Ctrl+C) on my
data I need to paste (where ever it lives whether in a new workbook or not)
and click the macro button and have it paste where I need it. Is this
possible?