PAste into a filtered list

[CC_mfc]

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

[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.

[CC_mfc]

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

[filky]

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.

[CC_mfc]

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:

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