Copy/Pasting Values from visible cells (using autofilter) to visible cells

**evakotsi** · 07-03-2012, 05:04 PM

Hello everyone,

My second question to this forum and again completely new to VBA so please bare with me..

I have a spreadsheet that I have formulas in multiple cells of a column. What I want to do is the following:

I use an autofilter to hide some of the rows and I want to copy/paste values on the same cells (only on the visible rows)!

I have found some code which does copy/paste from one column to another (only visible cells) but I don't know how to customize it to copy/paste on the same column...

Can someone please help???

I have attached a sample file so that you understand what I'm talking about.

I used the following code:

Sub Copy_Filtered_Cells()
Dim myArea As Range
Range("F2:F" & Cells(Rows.Count, "A").End(xlUp).Row).ClearContents
If ActiveSheet.FilterMode = True Then
    With Range("F2:F" & Cells(Rows.Count, "J").End(xlUp).Row).SpecialCells(12)
        For Each myArea In .Areas
            myArea.FormulaR1C1 = "=rc[4]"
            myArea.Value = myArea.Value
        Next
    End With
End If
End Sub

Which copies the visible cells from column J to the respective visible cells on column F.
This works fine and pastes the values.
Can someone please revise it so that the values are pasted on row J again instead of row F?

Your help is pretty much appreciated!

Thanx

Edit: Sorry, wrong code!

**evakotsi** · 07-03-2012, 05:19 PM

Attachment of file

**evakotsi** · 07-06-2012, 02:52 AM

Guys anyone?

**evakotsi** · 07-23-2012, 07:49 AM

Just to help anyone else looking for this I used the following code:

Sub VisbleCellsPasting() 
    On Error Resume Next 
    Dim aRng As Range 
    Dim aC As Long 
    Dim i As Long 
    Dim aRn As Range 
    Dim bRng As Range 
    Dim brn As Range 
    Dim tRn As Range 
    Dim wk As Worksheet 
    Dim ak As Worksheet 
    Application.DisplayAlerts = False 
    Set ak = ActiveSheet 
     
    Set aRng = ak.Range("O2:O6").SpecialCells(xlCellTypeVisible) 
    Range("c7").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

Cheers!