Lisa_King_3
New Member
- Joined
- Jan 26, 2004
- Messages
- 39
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
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