+ Reply to Thread
Results 1 to 4 of 4

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

Hybrid View

evakotsi Copy/Pasting Values from... 07-03-2012, 05:04 PM
evakotsi Re: Copy/Pasting Values from... 07-03-2012, 05:19 PM
evakotsi Re: Copy/Pasting Values from... 07-06-2012, 02:52 AM
evakotsi Re: Copy/Pasting Values from... 07-23-2012, 07:49 AM
  1. #1
    Registered User
    Join Date
    06-30-2012
    Location
    Athens, Greece
    MS-Off Ver
    Excel 2003
    Posts
    10

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

    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!
    Attached Files Attached Files
    Last edited by evakotsi; 07-03-2012 at 05:19 PM. Reason: Sorry, wrong code!

  2. #2
    Registered User
    Join Date
    06-30-2012
    Location
    Athens, Greece
    MS-Off Ver
    Excel 2003
    Posts
    10

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

    Attachment of file
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    06-30-2012
    Location
    Athens, Greece
    MS-Off Ver
    Excel 2003
    Posts
    10

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

    Guys anyone?

  4. #4
    Registered User
    Join Date
    06-30-2012
    Location
    Athens, Greece
    MS-Off Ver
    Excel 2003
    Posts
    10

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

    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!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1