Search range and split into different columns based on a cell value

[Craig1]

Hi guys,

Help needed again. What I need to do is:-

Using VBA I have one set of data which can change between each down load, it starts in $A$7 to column $E but the row number can differ.
For this example say $A$7 to $D100 is my range and based on the value in column E I want to transfer the data from say A7 to E7 into a new set of columns starting H7.
The value in column E will be Line 1, Line 2 etc so I want the code to loop around the range and all Line 1's transfer to a new set of columns starting in H7 then all Line 2's transfer to M7 and so on.

I have tried searching for an answer but my search criteria must be off a little.

i hope I have explained it enough for you guys to understand, all info is in the same worksheet.

Thanks in advance

BUVAA70005</SPAN>	06-Feb-13 16:17:08</SPAN>	07-Feb-13 05:50:01</SPAN>	Line 1</SPAN>
BEAMA00024</SPAN>	06-Feb-13 22:39:17</SPAN>	07-Feb-13 01:14:01</SPAN>	Line 1</SPAN>
BEG80078</SPAN>	06-Feb-13 22:57:43</SPAN>	07-Feb-13 00:06:26</SPAN>	Line 7</SPAN>
BEAAA30048</SPAN>	06-Feb-13 23:15:15</SPAN>	07-Feb-13 00:21:10</SPAN>	Line 5</SPAN>
CQZBA50006</SPAN>	06-Feb-13 23:36:06</SPAN>	07-Feb-13 00:44:20</SPAN>	Line 1</SPAN>
BEG80079</SPAN>	07-Feb-13 00:06:26</SPAN>	07-Feb-13 01:05:34</SPAN>	Line 5</SPAN>
BEAAA30049</SPAN>	07-Feb-13 00:21:10</SPAN>	07-Feb-13 01:17:34</SPAN>	Line 5</SPAN>
CQZBA50007</SPAN>	07-Feb-13 00:44:20</SPAN>	07-Feb-13 01:32:43</SPAN>	Line 5</SPAN>
BEG80080</SPAN>	07-Feb-13 01:05:34</SPAN>	07-Feb-13 02:13:54</SPAN>	Line 1

<TBODY>
</TBODY><COLGROUP><COL><COL span=2><COL></COLGROUP>

 

[Craig1]

Any ideas guys
thanks again

Craig

 

[Craig1]

Has anybody got any ideas at all, I have tried the macro recorder but not much use at the moment because the range can change from day to day, the cell where I want it pasting will always be the same.
Not sure if the best way is to auto filter the range and then copy or use a looping system until it picks out all the "Line 1" rows.
Have I explained exactly what I need, I think I have but thats easy for me say when I'm looking at it.

Craig.

Not sure if this last post was viewable on the forum so edited and tried again.

 

[itsmefeddi]

Craig your explanation does not suffice............ try to elaborate in a simple mode

 

[Craig1]

Thanks for the feedback.

As shown in post #1, the range of data is always starting in A7 and always to column E (one column hidden), the amount of rows can be anything from 10 rows down to 100 rows. All this info is generated from a report giving me all processes from the last 24hrs for all production lines.
What I need to do using vba is separate all the line in the respective individual lines, like Line 1 rows or loop through all the Line 1 rows (using column E to identify the Line 1,2,3,etc), so in the example above i would want rows 7,8,11 & 15 copying from A:E and pasting into a range of their own on the same sheet, say starting at H7, but before pasting into H7 i would need to clear the contents of the previous days information, again the previous days info for Line 1 could be 4 or 40 rows.

Hopefully a better explanation of what i'm trying to achieve.

Thanks in advance.

Craig.

 

[antthorne]

Hi Craig1

From my understanding you could use autofilter to get the right data and then copy/paste to where you would like.

I probably have a macro that I can adapt to help you but just need to understand some things -

Are there set Line numbers e.g 1-10 as an example??

And does the split information have to be on the same sheet?

Thanks
Ant

 

[Craig1]

Thanks for the reply Ant,

All lines are specific, they are Line 1 to Line 8

It can go it a dfferent sheet, it's not essential it's in the same sheet, the only thing I would ask is that once all the data is filtered into the separate sheet would it be possible to keep all this info together on that one separate sheet.
So if Line 1 info starts at A1, Line 2 info starts at G1 and so on.

Thanks again

Craig.

 

[antthorne]

Hi Craig

Let me work something out and I will post it up in a few minutes

Thanks

Ant

 

[Craig1]

Thanks Ant

 

[antthorne]

Hi Craig

This should work (I'm sure someone would have a better solution!)

I have placed in the code sheet1 as where you would have the data and sheet2 where the data will be presented (just change these to your sheets names)

Sub split_data()
application.screenupdating = false
sheets("Sheet1").select
range("a7").select
range(selection, selection.end(xltoright)).select
selection.autofilter
activesheet.range("a7:d100").autofilter field:=5, criteria1:="Line 1"
range("a7").select
range(selection, selection.end(xltoright)).select
range(selection, selection.end(xldown)).select
selection.copy
sheets("sheet2").select
range("a2").select
activesheet.paste
sheets("sheet1").select
range("a7").select
range(selection, selection.end(xltoright)).select
selection.autofilter
activesheet.range("a7:d100").autofilter field:=5, criteria1:="Line 2"
range("a7").select
range(selection, selection.end(xltoright)).select
range(selection, selection.end(xldown)).select
selection.copy
sheets("sheet2").select
range("g2").select
activesheet.paste
sheets("sheet1").select
range("a7").select
range(selection, selection.end(xltoright)).select
selection.autofilter
activesheet.range("a7:d100").autofilter field:=5, criteria1:="Line 3"
range("a7").select
range(selection, selection.end(xltoright)).select
range(selection, selection.end(xldown)).select
selection.copy
sheets("sheet2").select
range("m2").select
activesheet.paste
sheets("sheet1").select
range("a7").select
range(selection, selection.end(xltoright)).select
selection.autofilter
activesheet.range("a7:d100").autofilter field:=5, criteria1:="Line 4"
range("a7").select
range(selection, selection.end(xltoright)).select
range(selection, selection.end(xldown)).select
selection.copy
sheets("sheet2").select
range("s2").select
activesheet.paste
sheets("sheet1").select
range("a7").select
range(selection, selection.end(xltoright)).select
selection.autofilter
activesheet.range("a7:d100").autofilter field:=5, criteria1:="Line 5"
range("a7").select
range(selection, selection.end(xltoright)).select
range(selection, selection.end(xldown)).select
selection.copy
sheets("sheet2").select
range("Y2").select
activesheet.paste
sheets("sheet1").select
range("a7").select
range(selection, selection.end(xltoright)).select
selection.autofilter
activesheet.range("a7:d100").autofilter field:=5, criteria1:="Line 6"
range("a7").select
range(selection, selection.end(xltoright)).select
range(selection, selection.end(xldown)).select
selection.copy
sheets("sheet2").select
range("AE2").select
activesheet.paste
sheets("sheet1").select
range("a7").select
range(selection, selection.end(xltoright)).select
selection.autofilter
activesheet.range("a7:d100").autofilter field:=5, criteria1:="Line 7"
range("a7").select
range(selection, selection.end(xltoright)).select
range(selection, selection.end(xldown)).select
selection.copy
sheets("sheet2").select
range("ak2").select
activesheet.paste
sheets("sheet1").select
range("a7").select
range(selection, selection.end(xltoright)).select
selection.autofilter
activesheet.range("a7:d100").autofilter field:=5, criteria1:="Line 8"
range("a7").select
range(selection, selection.end(xltoright)).select
range(selection, selection.end(xldown)).select
selection.copy
sheets("sheet2").select
range("AQ2").select
activesheet.paste
sheets("sheet1").select
range("a7").select
range(selection, selection.end(xltoright)).select
selection.autofilter
activesheet.range("a7:d100").autofilter field:=5
application.screenupdating = true
MSGBOX("TASK COMPLETE")

This presumes you will only have 92 rows of data if you have more then I can change the macro to be more dynamic.

Hope it works
Thanks
Ant