PAste into a filtered list

Hi,

Is there anyway you can paste cells into a filtered list so that only the visible cells are pasted on top of?

i am copying from a filtered list using 'visible cells only' but then when i paste into the filtered list on a different excell book it won't let me paste on the visible cells, it copies into the non filtered cells which is kinda annoying,

not sure if this is possible, thanks in advance,

cc

Hi,

Can you give any more detail on this? I just created a short list with two columns. Filtered on one of the columns and then pasted some new data into the other column. When I turned the filter off only the cells in the filtered list had been copied to.

Maybe, I am misunderstanding your question?

Paul.

Originally Posted by filky

Hi,

Can you give any more detail on this? I just created a short list with two columns. Filtered on one of the columns and then pasted some new data into the other column. When I turned the filter off only the cells in the filtered list had been copied to.

Maybe, I am misunderstanding your question?

Paul.

Hi This def does not work on the excel i am using, when pasting the data into the other column which is filtered then it pastes it into both the visible and hidden cells.

i have tried variuos macro's that i have found online but none of them allow you to select visible cells only, then copy to another workbook and paste into the visible cells only.

Thanks

Hi,

I am using Office 2007, so maybe this was a problem in earlier versions? I don't know.

I can't think of anything clever, but perhaps you could filter as required. The insert a column next to the one you need, then enter a character, say "a", into each cell that corresponds to the filtered data. This would be quite quick.

Then turn the filter off and sort on the new column with the "a" in it. Now you have a non filtered column with all the relevant data in consecutive cells that you can paste into.

A bit long winded, but in the absence of anything more intelligent..!

Paul.

Yes actually that sounds about the easiest way to do it without code. i'll give that a try and see how i get on - Cheers!

i have found this code so if anyone know how to maipulate it maybe you could help me, i can get this to just about paste into visible cells only however it doesn't copy from only visible cells in the first sheet with filtered data:

PHP Code: 

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