copy one cell from auto filtered results

Lisa_King_3

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

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
L

Legacy 21301

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

Lisa_King_3

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

Legacy 21301

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

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,160
Messages
5,768,537
Members
425,480
Latest member
br400821

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top