Excel Help

[oz032281]

Hello everyone,

I wanted to know if anyone knew how I could use either VBA or some type of query in order to copy a range of cells. Unfortunately I cannot set ranges in my raw data file because they vary from day to day. I need to pull certain groups of rows based on these containing a certain value. In other words is there a way for excel to scan the entire datasheet & automatically find certain rows that contain say the value"9000" and copy this entire row & paste it into a new sheet. Please let me know. Thanks.

Oz

 

[Tazguy37]

Can the value be anywhere on the sheet? Not just certain columns, but any column?

And how is the data laid out? What rows, columns, blanks between records, etc.?

 

[parry]

Hi and welcome to the board.

Perhaps use the macro recorder to first filter the column so that only rows with 9000 are displayed then copy and paste these values to the other book. The code will need amending so that variable numbers of rows can be copied so post the recorded code here and we can alter it to accomodate this.

 

[oz032281]

Hello,

Thanks for the responses.

Ok; the data is already sorted in a way that all 9000 are together; however we receive new data every day so the range will change all the time and that is why I can't set a range at all. Just to give you a better idea the 9000 is a model code and there are several model codes so I will need to use the same method for all model codes. these model codes are all in one columd and I have a macro that takes care of sorting & getting rid of any blanks as well as unneccessary data.

I have several columns one is this model code; and the total number of rows varies depending on the data entered; however should never go above 2500 rows. The total number of columns is 12; so A-L.

 

[parry]

Yes I understand the rows will be variable. To code this you need to know whats the whole data range, the column to be filtered and the book/sheet to be copied to. These things can be ascertained from your recorded macro of filtering then copying.

 

[oz032281]

Ok...actually I was able to do it by recording a macro to just create a new sheet and then filtering the individual data within the new sheet. Works fine for what I need. Thanks a million for the help. Take care.

Oz