VBA to move rows to another sheet based on criteria

[JSR1306]

Hi all,

I am sorry if this has been explained already but I cant find anything that quite does what I want.

Basically I have a table as follows with about 1000 lines of data.

[TABLE="width: 100, align: center"]
<tbody>[TR]
[TD]Reference[/TD]
[TD]Priority[/TD]
[TD]status[/TD]
[TD]description[/TD]
[TD]due date[/TD]
[TD]date submitted[/TD]
[/TR]
[TR]
[TD]123[/TD]
[TD]2[/TD]
[TD]Open[/TD]
[TD]abc[/TD]
[TD]01/03/13[/TD]
[TD]11/02/13 [/TD]
[/TR]
[TR]
[TD]134[/TD]
[TD]2[/TD]
[TD]Open[/TD]
[TD]jhg[/TD]
[TD]02/03/13[/TD]
[TD]11/02/13[/TD]
[/TR]
[TR]
[TD]124[/TD]
[TD]1[/TD]
[TD]AWC [/TD]
[TD]kli[/TD]
[TD]01/03/13[/TD]
[TD]11/02/13[/TD]
[/TR]
[TR]
[TD]321[/TD]
[TD]3[/TD]
[TD]AWC[/TD]
[TD]ijk[/TD]
[TD]01/03/13[/TD]
[TD]11/02/13[/TD]
[/TR]
[TR]
[TD]526[/TD]
[TD]4[/TD]
[TD]Escalated[/TD]
[TD]yhu[/TD]
[TD]02/03/13[/TD]
[TD]11/02/13[/TD]
[/TR]
[TR]
[TD]981[/TD]
[TD]4[/TD]
[TD]Escalated[/TD]
[TD]jhy[/TD]
[TD]01/03/13[/TD]
[TD]11/02/13[/TD]
[/TR]
[TR]
[TD]852[/TD]
[TD]2[/TD]
[TD]Open[/TD]
[TD]abd[/TD]
[TD]01/03/13[/TD]
[TD]11/02/13[/TD]
[/TR]
</tbody>[/TABLE]

Basically I want to sort these into separate sheets under the headings of the status column. I have tried writing macros however this is very messy and I am sure there is a better way of coding it.

I have also tried some code such as the following:

Sub go()


Dim StsCol As String


Sheets("Report").Select


StsCol = Application.Range("A1000").End(xlUp).Row


a = 1




For i = 1 To StsCol

Sheets("Report").Select
If Range("C" & i).Value = "Open" Then
Range("C" & i).EntireRow.Copy
Sheets("T1 Open").Select
ActiveSheet.Range("A" & a).Select
Selection.PasteSpecial (xlValues)
a = a + 1
End If
Next

MsgBox "Done!"


End Sub


This does work on sorting the Open into a separate sheet, however, it is very slow and you the screen just blinks rapidly as it finds a row with Open and copies, then pastes it to the correct sheet.

I am not great a VB so any help is greatly appreciated. Hopefully I will learn something in the process

Many Thanks

John

 

[hiker95]

Matheson843,

Welcome to the MrExcel forum.

I would think that your raw data structure is different.

Please do not post your questions in threads started by others - - this is known as thread hijacking.
Always start a new thread for your questions and, if you think it helps, provide a link to any other thread as a reference.
Start a new thread for your question and be sure to give it a search friendly title that accurately describes your need.


After you create a new thread, please send me a Private Message with a link to your new thread, and, I will have a look.


In your new thread:

What version of Excel are you using?

Can you post a screenshot of the raw data worksheet, and, post a screenshot of the worksheet results (manually formatted by you) that you are looking for?

To post your data, you can download and install one of the following two programs:
Excel Jeanie
MrExcel HTML Maker


If you are not able to give us screenshots:
You can upload your workbook to Box Net,
sensitive data changed
mark the workbook for sharing
and provide us with a link to your workbook.

 

[Matheson843]

Hi Texaslynn,

I understand I should be starting a new thread for my own question. However, my question directly relates to the one already posed here and refers specifically to the code you posted.

The code you have posted works perfectly. However, I already have a tab with a name that matches one of the data names in the column that is being broken out. When the macro runs, the already created tab is overwritten with the data that is being broken out from the selected column.

My question: Is there a way to break out data based on what is in column "A". While naming the new sheet the same as the data but with a variant such as (2) or (a) at the end of the name.


Example: I already have a tab for "Ford" however I am trying to break out the data into new sheets based on column A "Cars." When I run your macro the pre-existing "Ford" tab is overwritten by the data broken out from column A "Cars." I would like to be able to break the data in column A "Cars" into tabs such as "Ford(a)" or "Ford(2)" so that the data doesn't overwrite. Is this possible? Thanks in advance!!!


[TABLE="width: 192"]
<COLGROUP><COL style="WIDTH: 48pt" span=4 width=64><TBODY>[TR]
[TD="class: xl65, width: 64, bgcolor: transparent"]Cars[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"]Price[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"]Year[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"]Color[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Ford[/TD]
[TD="bgcolor: transparent"]30K[/TD]
[TD="class: xl66, bgcolor: transparent"]2011[/TD]
[TD="bgcolor: transparent"]Blue[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Chevy[/TD]
[TD="bgcolor: transparent"]38K[/TD]
[TD="class: xl66, bgcolor: transparent"]2013[/TD]
[TD="bgcolor: transparent"]Green[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Ferrari[/TD]
[TD="bgcolor: transparent"]60K[/TD]
[TD="class: xl66, bgcolor: transparent"]2012[/TD]
[TD="bgcolor: transparent"]Red[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Nissan[/TD]
[TD="bgcolor: transparent"]40K[/TD]
[TD="class: xl66, bgcolor: transparent"]2010[/TD]
[TD="bgcolor: transparent"]Red[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Toyota[/TD]
[TD="bgcolor: transparent"]25K[/TD]
[TD="class: xl66, bgcolor: transparent"]2011[/TD]
[TD="bgcolor: transparent"]Black[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Honda[/TD]
[TD="bgcolor: transparent"]26K[/TD]
[TD="class: xl66, bgcolor: transparent"]2010[/TD]
[TD="bgcolor: transparent"]White[/TD]
[/TR]
</TBODY>[/TABLE]

 

[Nina Reid]

Do you know why I am getting stuck here?

ReDim SheetNameArray(1 To Rng2.Cells.Count)
SheetNameArray = fn.Transpose(Rng2)
.Columns(lastCol + 2).Clear

 

[Rick Rothstein]

Do you know why I am getting stuck here?

ReDim SheetNameArray(1 To Rng2.Cells.Count)
SheetNameArray = fn.Transpose(Rng2)
.Columns(lastCol + 2).Clear

What is "fn"? Transpose is a method of the WorksheetFunction object, so unless "fn" references that object, your code line won't work. Try changing "fn" to "WorksheetFunction" (without the quotes, of course) and see if that gets your code working (although I have some concern about that dot in front of the last line given that you did not show a With statement whose object it would be referencing). Note that is usually quite hard to diagnose a problem from a small snippet of code taken from a larger procedure as we cannot see what the intention of the code is nor what has taken place prior to the code snippet being evaluated.

 

[tournapart]

Hello,

I have a similar but different action I am trying to achieve and have run through various other VBA macro's with failed results.

Basically I have a two step process I need to complete, the data set I have to filter and only true values should be copied to the adjacent sheet(s)

Step one: Each data line has an ID number with several values that follow, these lines are presorted by time/date in descending order (oldest to newest). I need to compare duplicate ID's (could be two or three or more) to meet criteria before its considered true. So if my target criteria is ">5" I need to compare duplicate ID's for that value, if the newest (chronological) ID value exceeds the target, it is true

Example of a true (because the newest value is greater than 5):

Date Time ID Value
2/4/2015 17:25 250 4.2
2/5/2015 16:23 250 3.5
2/5/2015 16:25 250 5.1

Example of a false (because the newest value is less than 5):

Date Time ID Value
2/4/2015 17:25 252 6.8
2/5/2015 16:23 252 5.5
2/5/2015 16:25 252 4.3

Step two: true values should carry the entire column of data (just the true column of data, not the false ones) to the adjacent sheet

Any help is greatly appreciated

 

[tournapart]

Hello,

I have a similar but different action I am trying to achieve and have run through various other VBA macro's with failed results.

Basically I have a two step process I need to complete, the data set I have to filter and only true values should be copied to the adjacent sheet(s)

Step one: Each data line has an ID number with several values that follow, these lines are presorted by time/date in descending order (oldest to newest). I need to compare duplicate ID's (could be two or three or more) to meet criteria before its considered true. So if my target criteria is ">5" I need to compare duplicate ID's for that value, if the newest (chronological) ID value exceeds the target, it is true

Example of a true (because the newest value is greater than 5):

Date Time ID Value
2/4/2015 17:25 250 4.2
2/5/2015 16:23 250 3.5
2/5/2015 16:25 250 5.1

Example of a false (because the newest value is less than 5):

Date Time ID Value
2/4/2015 17:25 252 6.8
2/5/2015 16:23 252 5.5
2/5/2015 16:25 252 4.3

Step two: true values should carry the entire column of data (just the true column of data, not the false ones) to the adjacent sheet

Any help is greatly appreciated

Also there are some rows where only one ID data set exists, in that case, they only need to be filtered for being true or false, there does not need to be a comparison of duplicates

 

[erik.van.geit]

Hi, tournapart
Welcome to the MrExcel!

You responded to an older thread; your chances to get response are reduced. i''ve currently no time to take a look at your request. You would better start your own thread; you can refer to this one if you want.

kind regards,
Erik

 

[elsa2583]

Hello,
I have a workbook and I am trying to cut and paste all the data in column A (including whole row), to sheet 2, my criteria is that all data must be over 6 digits.
Can you please help me.
I have attached the file .
Cheers


[TABLE="width: 1651"]
<colgroup><col><col><col><col><col><col><col><col span="5"></colgroup><tbody>[TR]
[TD]Product ID[/TD]
[TD]Product Name[/TD]
[TD]Average Overall
Rating[/TD]
[TD]Total Submitted
Reviews[/TD]
[TD]% Approved[/TD]
[TD]% Rejected[/TD]
[TD]% Pending[/TD]
[TD]% 5-Star[/TD]
[TD]% 4-Star[/TD]
[TD]% 3-Star[/TD]
[TD]% 2-Star[/TD]
[TD]% 1-Star[/TD]
[/TR]
[TR]
[TD]964726[/TD]
[TD]Halfords 9v Battery 5 Pack[/TD]
[TD]5.00[/TD]
[TD]1[/TD]
[TD]100%[/TD]
[TD]0%[/TD]
[TD]0%[/TD]
[TD]100%[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]966077[/TD]
[TD]Castrol Power 1 4T 10W/30 Motorcycle Engine Oil - 1ltr[/TD]
[TD]5.00[/TD]
[TD]14[/TD]
[TD]100%[/TD]
[TD]0%[/TD]
[TD]0%[/TD]
[TD]100%[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]641225[/TD]
[TD]Clarks CP501 V-Brake Block Insert[/TD]
[TD]5.00[/TD]
[TD]1[/TD]
[TD]100%[/TD]
[TD]0%[/TD]
[TD]0%[/TD]
[TD]100%[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]364562[/TD]
[TD]Halfords Advanced Impact Socket 30mm 1/2" Drive[/TD]
[TD]5.00[/TD]
[TD]2[/TD]
[TD]100%[/TD]
[TD]0%[/TD]
[TD]0%[/TD]
[TD]100%[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 1651"]
<colgroup><col><col><col><col><col><col><col><col span="5"></colgroup><tbody>[TR]
[TD]1472659270965[/TD]
[TD]Tog24 Zenon Mens Down Hooded Jacket Navy[/TD]
[TD]1.00[/TD]
[TD]1[/TD]
[TD]0%[/TD]
[TD]100%[/TD]
[TD]0%[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]100%[/TD]
[/TR]
[TR]
[TD]1472659220621[/TD]
[TD]Thh Ts-80 Plain Full Face Motorcycle Helmet S Matt Black[/TD]
[TD]1.00[/TD]
[TD]1[/TD]
[TD]0%[/TD]
[TD]100%[/TD]
[TD]0%[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]100%[/TD]
[/TR]
[TR]
[TD]1472659220991[/TD]
[TD]Spyke Sp004 Owl Wp Motorcycle Boots 42 Black (uk 8)[/TD]
[TD]1.00[/TD]
[TD]1[/TD]
[TD]0%[/TD]
[TD]100%[/TD]
[TD]0%[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]100%[/TD]
[/TR]
[TR]
[TD]1472659220994[/TD]
[TD]Spyke Sp004 Owl Wp Motorcycle Boots 45 Black (uk 11)[/TD]
[TD]1.00[/TD]
[TD]1[/TD]
[TD]0%[/TD]
[TD]100%[/TD]
[TD]0%[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]100%[/TD]
[/TR]
[TR]
[TD]1472659222350[/TD]
[TD]5131-0104 - Black Beacon Waterproof Motorcycle Rainsuit S Black (d-002)[/TD]
[TD]1.00[/TD]
[TD]1[/TD]
[TD]0%[/TD]
[TD]100%[/TD]
[TD]0%[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]100%[/TD]
[/TR]
</tbody>[/TABLE]

 

[hiker95]

elsa2583,

Welcome to the MrExcel forum.


We can not tell where your raw data is located, sheet name, cells, cell formatting, rows, columns, and, we can not tell where the results should be, sheet name, cells, cell formatting, rows, columns.


We would like more information. Please see the Forum Use Guidelines in the following link:

http://www.mrexcel.com/forum/board-announcements/127080-guidelines-forum-use.html


See reply #2 at the next link, if you want to show small screenshots, of the raw data, and, what the results should look like.

http://www.mrexcel.com/forum/about-board/508133-attachments.html#post2507729


Or, you can post your workbook/worksheets to the following free site (sensitive data changed), mark the workbook for sharing, and, provide us with a link to your workbook:

https://dropbox.com