Pate to the visible cell

[aleksandra123]

Hi Guys,
I developed a macro however I am still struggling with pasting into the first visible cell.
Here is my macro. The problem is: The macro doesn't recognize the first blank filtered row but pasts the row into the first blank row. However, a table, where macro pastes row, is always filtered.

Sub cpline()

Dim rngFilt As Range

With ActiveSheet
  Set rngFilt = Application.Intersect(.AutoFilter.Range.Offset(1).SpecialCells(xlCellTypeVisible), .Range("A:AB"))
End With

rngFilt.Copy

Sheets("xx").Range("B" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues

End Sub

Any suggestions?

Thanks a lot !

 

[Hui]

That code works ok
Can you please paste the file with some data?

 

[aleksandra123]

Hello

Please find enclosed my testing file. Macro is in sheet aaa and copy data from sheet aaa to ATLAS. I've already filtered column A in the Atlas sheet but the macro doesn't paste row to the first filtered blank row but to the first blank row.

Thank for any advises

 

[Hui]

Your code is trying to copy worksheet AA
Range
? RNGFILT.Address
$A$7:$AB$7260

to Worksheet Atlas
Range B7

Is that correct?

Or should it be copying $A$7:$AB$13 ?

 

[Hui]

If the first case is correct use:

Sub cpline()

Dim rngFilt As Range

With ActiveSheet
  Set rngFilt = Application.Intersect(.AutoFilter.Range.Offset(1).SpecialCells(xlCellTypeVisible), .Range("A:AB"))
End With
rngFilt.Copy

With Sheets("ATLAS")
  .Range("B" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues
End With

End Sub

if the second case is correct use

Sub cpline()

Dim rngFilt As Range

Set rngFilt = Range("A7:AB" & Range("A" & Rows.Count).End(xlUp).Row)
rngFilt.Copy

With Sheets("ATLAS")
  .Range("B" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues
End With

End Sub