1

Hello i need some help with a abit of code, basically i have a workbook which has a lot of data for product Accessories listed in rows in the columns next to these there are the products with blank fields in all of the cells, basically the point of this is when the user types an "x" in any of these cells the table is able to be filtered.

I have made a summary sheet where i want it to look at these specific columns for example columns E-N and if it contains an "x" it will copy the corresponding row which are A,B,C,D and paste it on this summary sheet one after another as well as copying the product from the row above all the x's

e.g

Column E has an x in E4,5,10,15,53

i want it to copy

ABCD4
ABCD5
ABCD10
ABCD15
ABCD53

as well as the product name e.g "melons" which is located just above the first blank box of each column in this case lets say E4 is the first blank so E3 would be the product name.

and paste it in a sheet called "Summary Sheet" one row after another.

I know this is really confusing but i hope you can help :)

code i have: I now have this code and im almost at where i want to be i need help on the line

    Range("A5").Select

It gives me an error but unless i select this cell it pastes randomly on the sheet

 Private Sub CommandButton9_Click()
 Range("A7:D7").Select
 Range(Selection, Selection.End(xlDown)).Select ' Go to last line

 ' Add a filter behavior
 Selection.AutoFilter Field:=5, Criteria1:="<>"

 Range("A7:D7").Select
 Range(Selection, Selection.End(xlDown)).Select

 Selection.Copy

 ' Paste data where you want
 Sheets ("Summary Sheet")
 Range("A5").Select
 ActiveSheet.Paste
 End Sub
Nindi
  • 37
  • 2
  • 9

1 Answers1

0

The idea could be

  1. Select your set of data

  2. Apply filter to isolate rows with x as value for the filtering column

  3. Select data and do a copy / paste action

Proposed sample of code (should be adapted to your spreadsheets)

     ' Select your range of data - maybe 
     Range("A1:E1").Select 
     Range(Selection, Selection.End(xlDown)).Select ' Go to last line

     ' Add a filter behavior
     Selection.AutoFilter Field:=5, Criteria1:="<>"

     Range("A1:E1").Select 
     Range(Selection, Selection.End(xlDown)).Select

     Selection.Copy

     ' Paste data where you want
     Sheets("Feuil2").Select
     Range("A1").PasteSpecial Paste:=xlPasteValues
Guilhem Hoffmann
  • 962
  • 5
  • 13
  • Thats essentially what i want it to do but how do i write a code for this so it does it at the click of a button, i have around 10 sheets that i need to apply this for. But im glad you understand what im saying :) – Nindi Nov 26 '13 at 11:06
  • ' Add a filter behavior Selection.AutoFilter AutoFilter Field:=5, Criteria1:="<>" this line doesnt work says compile error Expected end of statement – Nindi Nov 26 '13 at 11:24
  • My mistake, -> Selection.AutoFilter Field:=5, Criteria1:="<>" – Guilhem Hoffmann Nov 26 '13 at 11:32
  • Is there a thread to see how Autofilters behave or do i keep that line of code as it is? – Nindi Nov 26 '13 at 11:35
  • you can do : Selection.AutoFilter -> without parameter just add filter – Guilhem Hoffmann Nov 26 '13 at 12:01
  • afterwards Selection.AutoFilter Field:=5, Criteria1:="<>" apply criteria. you can do it in 2 steps to understand what happen – Guilhem Hoffmann Nov 26 '13 at 12:01
  • I now have this code and im almost at where i want to be i need help on the Range("A1").Select line (See Question above). It gives me an error but i need this line otherwise it pastes anywhere on the summary sheet – Nindi Nov 26 '13 at 13:07
  • you wrote Range("A7:D7").Select it should be Range("A7:E7").Select in order to include E column and to get filter applicable (column 5) – Guilhem Hoffmann Nov 26 '13 at 13:26
  • as soon as it works don't forget to check my proposal as a correct answer for your question – Guilhem Hoffmann Nov 26 '13 at 14:24
  • That all working fine but the bit in the code "Range("A5").Select" does not work. This part just enables me to pick the cell to start from – Nindi Nov 26 '13 at 14:52
  • I changed the paste method tx to http://stackoverflow.com/questions/6778759/ms-excel-2010-copying-and-pasting-data-using-vba-code – Guilhem Hoffmann Nov 26 '13 at 16:15