Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 54
Default Paste To Visible Cells Macro

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?
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1,069
Default Paste To Visible Cells Macro

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?

  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 54
Default Paste To Visible Cells Macro

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?

  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1,069
Default Paste To Visible Cells Macro

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?

  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 54
Default Paste To Visible Cells Macro

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?



  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1,069
Default Paste To Visible Cells Macro

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?

  #7   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 54
Default Paste To Visible Cells Macro

I do see where our disconnect was...

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.

That is exactly what I am looking for...

"Tom Hutchins" wrote:

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?

  #8   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1,069
Default Paste To Visible Cells Macro

Okay, try this macro. Starting with a vertical (or horizontal) range of cells
on the first sheet, it pauses as before so you can move to another workbook
or worksheet. When the inputbox appears, select the first (top leftmost) cell
of the range where you want to paste the data.

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:04"), "CopyToVisibleOnly2"
End Sub

Private Sub CopyToVisibleOnly2()
'Declare local variables.
Dim EndWB As Workbook, EndWS As Worksheet
Dim Target As Range, CurrCell As Range
Dim x As Long, FromCnt As Long
On Error GoTo CTVOerr
'Select the range where it should be pasted.
Set Target = Application.InputBox _
(Prompt:="Select the first cell in the Paste range", Type:=8)
Set EndWB = ActiveWorkbook
Set EndWS = ActiveSheet
Set CurrCell = Target.Cells(1, 1)
Application.ScreenUpdating = False
'Copy the cells from the original workbook, one at a time.
StartWB.Activate
StartWS.Activate
For x = 1 To Range(CopyRng).Count
StartWB.Activate
StartWS.Activate
Range(CopyRng).Cells(x, 1).Copy
'Return to the target workbook.
EndWB.Activate
EndWS.Activate
CurrCell.Activate
'Only cells in visible rows in the selected
'range are pasted.
Do While (CurrCell.EntireRow.Hidden = True) Or _
(CurrCell.EntireColumn.Hidden = True)
Set CurrCell = CurrCell.Offset(1, 0)
Loop
CurrCell.Select
ActiveSheet.Paste
Set CurrCell = CurrCell.Offset(1, 0)
Next x
Cleanup:
'Free the object variables.
Set Target = Nothing
Set CurrCell = Nothing
Set StartWB = Nothing
Set StartWS = Nothing
Set EndWB = Nothing
Set EndWS = Nothing
Application.ScreenUpdating = True
Exit Sub
CTVOerr:
MsgBox Err.Description
GoTo Cleanup
End Sub

Let me know how it works,

Hutch

"WBTKbeezy" wrote:

I do see where our disconnect was...

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.

That is exactly what I am looking for...

"Tom Hutchins" wrote:

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?

  #9   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 54
Default Paste To Visible Cells Macro

Hutch:

This is PERFECT! It works great. I REALLY appreciate it. I also learned a
few tricks from it to apply to some of my other macros!

Thanks againn!

"Tom Hutchins" wrote:

Okay, try this macro. Starting with a vertical (or horizontal) range of cells
on the first sheet, it pauses as before so you can move to another workbook
or worksheet. When the inputbox appears, select the first (top leftmost) cell
of the range where you want to paste the data.

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:04"), "CopyToVisibleOnly2"
End Sub

Private Sub CopyToVisibleOnly2()
'Declare local variables.
Dim EndWB As Workbook, EndWS As Worksheet
Dim Target As Range, CurrCell As Range
Dim x As Long, FromCnt As Long
On Error GoTo CTVOerr
'Select the range where it should be pasted.
Set Target = Application.InputBox _
(Prompt:="Select the first cell in the Paste range", Type:=8)
Set EndWB = ActiveWorkbook
Set EndWS = ActiveSheet
Set CurrCell = Target.Cells(1, 1)
Application.ScreenUpdating = False
'Copy the cells from the original workbook, one at a time.
StartWB.Activate
StartWS.Activate
For x = 1 To Range(CopyRng).Count
StartWB.Activate
StartWS.Activate
Range(CopyRng).Cells(x, 1).Copy
'Return to the target workbook.
EndWB.Activate
EndWS.Activate
CurrCell.Activate
'Only cells in visible rows in the selected
'range are pasted.
Do While (CurrCell.EntireRow.Hidden = True) Or _
(CurrCell.EntireColumn.Hidden = True)
Set CurrCell = CurrCell.Offset(1, 0)
Loop
CurrCell.Select
ActiveSheet.Paste
Set CurrCell = CurrCell.Offset(1, 0)
Next x
Cleanup:
'Free the object variables.
Set Target = Nothing
Set CurrCell = Nothing
Set StartWB = Nothing
Set StartWS = Nothing
Set EndWB = Nothing
Set EndWS = Nothing
Application.ScreenUpdating = True
Exit Sub
CTVOerr:
MsgBox Err.Description
GoTo Cleanup
End Sub

Let me know how it works,

Hutch

"WBTKbeezy" wrote:

I do see where our disconnect was...

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.

That is exactly what I am looking for...

"Tom Hutchins" wrote:

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?

  #10   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1
Default Okay, try this macro.

In this case I select the data and then I run the macro.

Is there a way that I can select the data, copy them and then run the macro? With the data already copied?

Another thing. I want the data to be pasted directly into a particular cell, such as D6.

Thanks! (I'm sorry for my english, I do not speak the language)


On Tuesday, March 04, 2008 5:27 PM WBTKbeez 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?



On Tuesday, March 04, 2008 11:53 PM TomHutchin 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:



On Wednesday, March 05, 2008 12:06 PM WBTKbeez 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:



On Wednesday, March 05, 2008 2:12 PM TomHutchin 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:



On Wednesday, March 05, 2008 2:26 PM WBTKbeez 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:



On Wednesday, March 05, 2008 2:59 PM TomHutchin wrote:


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:



On Wednesday, March 05, 2008 3:14 PM WBTKbeez wrote:


I do see where our disconnect was...

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.

That is exactly what I am looking for...

"Tom Hutchins" wrote:



On Wednesday, March 05, 2008 6:27 PM TomHutchin wrote:


Okay, try this macro. Starting with a vertical (or horizontal) range of cells
on the first sheet, it pauses as before so you can move to another workbook
or worksheet. When the inputbox appears, select the first (top leftmost) cell
of the range where you want to paste the data.

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:04"), "CopyToVisibleOnly2"
End Sub

Private Sub CopyToVisibleOnly2()
'Declare local variables.
Dim EndWB As Workbook, EndWS As Worksheet
Dim Target As Range, CurrCell As Range
Dim x As Long, FromCnt As Long
On Error GoTo CTVOerr
'Select the range where it should be pasted.
Set Target = Application.InputBox _
(Prompt:="Select the first cell in the Paste range", Type:=8)
Set EndWB = ActiveWorkbook
Set EndWS = ActiveSheet
Set CurrCell = Target.Cells(1, 1)
Application.ScreenUpdating = False
'Copy the cells from the original workbook, one at a time.
StartWB.Activate
StartWS.Activate
For x = 1 To Range(CopyRng).Count
StartWB.Activate
StartWS.Activate
Range(CopyRng).Cells(x, 1).Copy
'Return to the target workbook.
EndWB.Activate
EndWS.Activate
CurrCell.Activate
'Only cells in visible rows in the selected
'range are pasted.
Do While (CurrCell.EntireRow.Hidden = True) Or _
(CurrCell.EntireColumn.Hidden = True)
Set CurrCell = CurrCell.Offset(1, 0)
Loop
CurrCell.Select
ActiveSheet.Paste
Set CurrCell = CurrCell.Offset(1, 0)
Next x
Cleanup:
'Free the object variables.
Set Target = Nothing
Set CurrCell = Nothing
Set StartWB = Nothing
Set StartWS = Nothing
Set EndWB = Nothing
Set EndWS = Nothing
Application.ScreenUpdating = True
Exit Sub
CTVOerr:
MsgBox Err.Description
GoTo Cleanup
End Sub

Let me know how it works,

Hutch

"WBTKbeezy" wrote:



On Thursday, March 06, 2008 1:02 PM WBTKbeez wrote:


Hutch:

This is PERFECT! It works great. I REALLY appreciate it. I also learned a
few tricks from it to apply to some of my other macros!

Thanks againn!

"Tom Hutchins" wrote:




Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Paste visible cells? Tim Miser Excel Worksheet Functions 8 May 5th 09 11:21 PM
Is it possible paste to visible cells only? NeedToKnow Excel Discussion (Misc queries) 2 August 7th 08 06:04 AM
Paste to Visible Cells only Sandy Burgess Excel Discussion (Misc queries) 1 June 19th 08 11:33 PM
Paste into visible cells ERR229 Excel Discussion (Misc queries) 4 January 19th 06 04:28 AM
Paste into Visible Cells Only CR Excel Discussion (Misc queries) 1 June 10th 05 07:18 PM


All times are GMT +1. The time now is 07:22 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"