Button macro from filtered data

  • Thread starter Thread starter alan_bourne
  • Start date Start date
A

alan_bourne

ok heres the jist of it.....
i have sheet 1 which contains all of the data on my spreadsheet. The
data here is auto filtered. after filtering the feilds to get the
product i want i then want to add it to new page under previous orders
like a shoping kart on page 2 using a macro and button. under i have
included some screenshots below to show u what i mean but this is
really puzzeling me big time plus i'm not to used to excel. any help
would be much apreciated

thanks alan bourne

heres the link to what i need doing ......
[image: http://www.you-are-a-huge-nerd.com/public/freesig/excel.jpg]


+----------------------------------------------------------------+
| Attachment filename: caraudio.xls |
|Download attachment: http://www.excelforum.com/attachment.php?postid=356605|
+----------------------------------------------------------------+
 
Sub invoicenum()
Worksheets("Sheet1").Range("A16").AutoFilter _
Field:=1, _
Criteria1:=Range("Sheet2!c8").Value
Worksheets("Sheet1").AutoFilter.Range.Copy _
Destination:=Worksheets("Sheet2").Range("B9")
End Sub

Adjust to fit your actual layout.
 
but will this not just paste the details over each other each time?

in the screenshot sheet 2 are only examples of what i want it to look
like...
e.g 5 sony jhdgfjh 1 £199.99
5 panasonic 789-op 1 £199.67

i need the sony part to come from the sheet 1 page after the filter has
been done and i can see the product
so it may be in row 78 but will be displayed at the top of the sheet
becasue the others are hidden. i then need a button to imput this data
under the last piece of data in the b coloumn on sheet 2
the invoice number eg 5 is repeated because that is the invoice number
and the person buying those products may buy 3 or 4 items so the
number 5 is needed for the next 5 products

please help also thanks for your time
 
for get the fact theres a unique id filter that does not need to be
there it will be gone
 
You only want to copy 1 cell and it is visible?

set rng = Worksheets("Sheet1").Autofilter.Range.Columns(2)
set rng.offset(1,0).Resize(rng.rows.count-1,1)
On Error Resume Next
set rng = rng.specialcells(xlvisible)
On Error goto 0
if not rng is nothing then
worksheets("Sheet2").Cells(rows.count,2).End(xlup)(2).Value = _
rng(1).Value
End if
 
mate you are being verytolerable thanks this is totaly confusing me

if you could just take a last look at this file i have underlined what
is needed and is very clear now trust me :D thank you for your help
once again

alan bourne


+----------------------------------------------------------------+
| Attachment filename: caraudio.xls |
|Download attachment: http://www.excelforum.com/attachment.php?postid=356754|
+----------------------------------------------------------------+
 
Sub Copydata()
Dim rng As Range

Set rng = Worksheets("Sheet1").AutoFilter.Range.Columns(3)
Set rng = rng.Offset(1, 0).Resize(rng.Rows.Count - 1, 1)
On Error Resume Next
Set rng = rng.SpecialCells(xlVisible)
On Error GoTo 0
If Not rng Is Nothing Then
Worksheets("Sheet2").Cells(Rows.Count, 2).End(xlUp)(2).Value = _
rng(1).Value
End If

End Sub
 
Back
Top