Need help in Pasting data in a filtered data range

[Kiran Kumar]

Hi there,


Need one more assistance on copy & pasting data.

For example, I copy data from spreadsheet1 and wanna paste the data in spread sheet. The challenge is i filter some columns in the spreadsheet2 for some validations and then I wanna paste the copied data onto the filtered range.


The problem is, if i paste the data on the filtered range, the data will not get pasted on the visible cells.


can some one look onto this solve for me pls.


Rds,

Kiran Kumar

 

[Luke M]

If you name the range with values that you want to copy "CopyRange", then select the region of cells that you want to copy to, I believe this macro will work for you.

[pre]

Sub Copy_Filtered_Cells()
Dim SourceRange As Range, TargetRange As Range
Dim Cell As Range, i As Integer
If ActiveSheet.FilterMode = True Then
Set SourceRange = Range("CopyRange")
Set TargetRange = Selection.SpecialCells(xlCellTypeVisible)
With Application
.ScreenUpdating = False
For Each Cell In TargetRange
i = i + 1
SourceRange.Cells(i).Copy Cell
Next
.CutCopyMode = False
End With
End If
End Sub

[/pre]

 

[Kiran Kumar]

thats fantastic. it would be great if we have a technique to paste directly without running some macros. It is difficult to set a named range for copied and cells and then select the filted range and then running macros.... oooh! Pls let me know if there is any technique to paste it in a visible cells of a filtered range or else i will go with this macro code only.


thank again for your help on this.