copy one cell from auto filtered results

Thanks Thanks:  0
Likes Likes:  0
Results 1 to 4 of 4

Thread: copy one cell from auto filtered results

  1. #1
    New Member
    Join Date
    Jan 2004
    Posts
    39
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default copy one cell from auto filtered results

    Hi people. I hope someone can help me.

    I have a vb code to autofilter a table in a worksheet (Membrship Prices)depending on the values of 2 cells in another worksheet (Weekly). That works fine, but what I can't seem to do is copy the last cell in the filtered results in "Membership Prices" to a cell in "Weekly" I have tried a range of codes, but none work, and i don't know why.

    Can anyone shine a light on what I am doing wrong?

    These are two codes i have attempted to use (Red code is the bits that aren't working)

    Sub Macro1()

    Sheets("Membership Prices").Select
    Range("B13").Select
    Selection.AutoFilter
    Selection.AutoFilter Field:=1, Criteria1:=Sheets("Weekly").Range("B9").Text
    Selection.AutoFilter Field:=2, Criteria1:=Sheets("Weekly").Range("C9").Text
    Range("D13").Offset(1, 0).Select
    Selection.Copy
    Sheets("Weekly").Range("E9").PasteSpecial
    Application.CutCopyMode = False
    Sheets("Membership Prices").Range("B13").Select
    Selection.AutoFilter

    End Sub

    Sub Macro2()

    Sheets("Membership Prices").Select
    Range("B13").Select
    Selection.AutoFilter
    Selection.AutoFilter Field:=1, Criteria1:=Sheets("Weekly").Range("B9").Text
    Selection.AutoFilter Field:=2, Criteria1:=Sheets("Weekly").Range("C9").Text
    Range("B13").Select
    Selection.SpecialCells(xlCellTypeLastCell).Select
    Selection.Copy
    Sheets("Weekly").Range("E9").PasteSpecial
    Application.CutCopyMode = False
    Sheets("Membership Prices").Range("B13").Select
    Selection.AutoFilter

    End Sub

    Hope you understand me
    Lisa x

  2. #2

    Join Date
    Sep 2003
    Posts
    994
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: copy one cell from auto filtered results

    Sheets("Membership Prices").[B65536].End(xlUp).Copy Sheets("Weekly").[E9]

  3. #3
    New Member
    Join Date
    Jan 2004
    Posts
    39
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: copy one cell from auto filtered results

    it doesn't work. It does exactly the same as the other formula's i have tried. in the cell E9, where it is copied to, the cell's reference becomes =D2. I think it is because the cell that is copied from the autofilter refers to another cell in that spreadsheet. When the cell that i copy is 1.00 and =B7 in that same sheet, when it is copied to sheet "weekly", the reference in E9 becomes = D2, which is 1 to the left and 7 up? (B7 amking it go up 7, and 1 making it go left one?)

    Is there anyway you can alter that code you gave me so that it only copies the text in the cell, not the formula as well?

    Thanks

    Lisa x

  4. #4

    Join Date
    Sep 2003
    Posts
    994
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: copy one cell from auto filtered results

    Sheets("Weekly").[E9] = Sheets("Membership Prices").[B65536].End(xlUp).Value

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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

 

DMCA.com