+ Reply to Thread
Results 1 to 5 of 5

Modifying Advanced Filter VBA code

  1. #1
    Registered User
    Join Date
    06-27-2013
    Location
    Reading, England
    MS-Off Ver
    Excel 2013
    Posts
    4

    Modifying Advanced Filter VBA code

    Hello all,

    Hopefully this is just a quick one for someone with a working knowledge of VBA coding.

    I am looking to modify the Advanced Filter VBA code so that rather than the actual cell contents being copied, instead the cell reference is copied over, thus allowing a modification to be made to to the original data which is then subsequently automatically updated in the advanced filter copied cells.

    Range("A1:E5").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range( _
    "G1:G2"), CopyToRange:=Range("J1:L1"), Unique:=False

    Hopefully the above makes sense and it is an easy modification?

    Also a brief explanation as to how to change VBA code to cell references rather than actual copying of cells would be a great too.

    Best Regards

    Dan

  2. #2
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643
    Dan

    As far as I know it's not possible to do what you want.
    If posting code please use code tags, see here.

  3. #3
    Registered User
    Join Date
    06-27-2013
    Location
    Reading, England
    MS-Off Ver
    Excel 2013
    Posts
    4

    Re: Modifying Advanced Filter VBA code

    Thanks for the response Norie,

    I did think it was asking a little much from the pre-existing advanced filter code. I was hoping it would be a case of just modifying the "Action:=xlFilterCopy".

    Would you know if it is possible to carry out a similar operation using different code? In short, copying multiple rows of data based upon a certain criteria in one of the columns, but rather than copying the cell contents I jut want to copy the link to the original cell?

    For example, a spreadsheet contains data up-to column D with 11 rows of data (Including the heading), Based upon data entered into column C only lines which satisfy the requirement (for example 4 out of 10 lines detail "Yes") are copied to another location (Only copying the link to the original cell and not the contents).

    Best Regards

  4. #4
    Registered User
    Join Date
    06-27-2013
    Location
    Reading, England
    MS-Off Ver
    Excel 2013
    Posts
    4

    Re: Modifying Advanced Filter VBA code

    I think I have managed to get close to what I would like to do using the below code:

    Sub FindPaste()

    x = 2
    Do While Cells(x, 4) <> ""
    If Cells(x, 4) = "Red" Then
    Worksheets("Sheet1").Rows(x).Copy
    Worksheets("sheet2").Activate
    erow = Sheet2.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
    ActiveSheet.Paste Destination:=Worksheets("Sheet2").Rows(erow)
    End If
    Worksheets("Sheet1").Activate
    x = x + 1
    Loop

    End Sub

    The above copies specific lines based upon a criteria in column 4, in this case "Red", then pastes to Sheet2 under any existing data which is great.

    In this case is it possible to replace the "Paste", with "Paste Link" instead? Therefore copying over the link rather than the cell contents?

    Thanks in advance,

  5. #5
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: Modifying Advanced Filter VBA code

    Try adding Link:=True to the Paste.

    PS Can you use code tags when posting code?

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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