Excel help - Segregating data automatically ?

  ShorN 11:57 13 Jul 2005

Hi all,

Hoping to get some help here.

I have had some help from this forum already regarding this spreadsheet im working on and im hoping to get a few more pointers.

Basically at the moment i have a spreadsheet which contains 16 columns, 6 of these coloums are used to input values which eventually show up a yes/no answer in another column. (the others are just various details)

Basically what i want it to do is:

One i enter a line on the spreadsheet, if the answer to the criteria is "yes" it will then automatically transfer or copy that line of data to a seperate sheet.
I dont know if this can be done with macros or formulas or even at all. (but i have been impressed with what excel can do up to now so im hoping!)

Any help would be greatly appreciated!
If you need further clarification just ask!

Thanks :)

  VoG II 12:10 13 Jul 2005

Presumably Columns A to P? Which column would "Yes" show up in? What are the names of the source and destination sheets?

It would be relatively easy to wrire a macro to transfer the contents of the current (or last) row of the first sheet to the first empty row of a second sheet if "Yes" is present. However, I can't for the moment think of a simple means of automating this.

  Simsy 12:46 13 Jul 2005

the "Selection change" event... as the basis for a macro. When you type something into a cell you then press "Enter", (or whatever method you use, such as right arrow), and the entry is made into that cell and the selection changes to a new cell. This could be used as the way of automating it.

There may be a number of conditions to be met, but proceed with the macro if they all are...

If the cell is in the correct range

If the appropriate cell value is "Yes"


Does this help, or have I missed something?



  ShorN 13:35 13 Jul 2005


Vog - its actually columns, A - Q (sorry miscounted) and the column that has the yes/no is column M.

It would be good if, as you said, I could have this transfered to another worksheet, (in the same workbook, but another sheet)

Simsy - Im really not to hot on macros, Ive got a macro on this sheet at the moment that sorts the rows into decending order when a button is pressed. This allows it to be sorted easily when new data is added to the spreadsheet. I dont really have a clue when it comes to writing them! I try to learn and understand from how there written.

Im hoping that it can remove any rows with Yes in the criteria to the next worksheet, so if anyone can maybe help towards's starting a macro.

I wouldnt mind if you could, say, enter various rows of data, and then press a button which runs a macro and it then automatically removes any lines with Yes in to the next sheet.

Much appreciation for you help so far and in advance for any further help! :)

  VoG II 14:32 13 Jul 2005

Sub MoveData()

Dim iCol As Integer, NextRow As Long, LastRow As Long, ThisRow As Long

Dim InSht As Worksheet, OutSht As Worksheet

Set InSht = Worksheets("Sheet1")

Set OutSht = Worksheets("Sheet2")

LastRow = InSht.Cells(Cells.Rows.Count, 1).End(xlUp).Row

NextRow = OutSht.Cells(Cells.Rows.Count, 1).End(xlUp).Row + 1

Application.ScreenUpdating = False

For ThisRow = 2 To LastRow

If UCase(InSht.Cells(ThisRow, 13).Value) = "YES" Then

For iCol = 1 To 17

OutSht.Cells(NextRow, iCol).Value = InSht.Cells(ThisRow, iCol).Value

Next iCol

NextRow = NextRow + 1

End If

Next ThisRow

For ThisRow = LastRow To 2 Step -1

If UCase(InSht.Cells(ThisRow, 13).Value) = "YES" Then InSht.Cells(ThisRow, 1).EntireRow.Delete

Next ThisRow

Application.ScreenUpdating = True

End Sub


To use this, First make a backup copy of your worksheet. Open your workbook ALT + F11 to open the Visual Basic Editor, Insert | Module and paste in the code. You can delete blank lines - I've added them so that the code formats correctly on this page.

Change "Sheet1" and "Sheet2" to the actual names of your sheets.

I have assumed that Row 1 contains headings, if that is not the case you will need to change the 2 in

For ThisRow = 2 To LastRow


For ThisRow = LastRow To 2 Step -1

Close the VBE to return to the worksheet.

Tools | Macros | Macro, highlight MoveData and click Run. You could add a button to the worksheet (using the Forms toolbar), right click it, select Assign Macro and assign Move Data.

  VoG II 21:18 15 Jul 2005

Did this break your 'puter?

  Snec 22:13 15 Jul 2005


  ShorN 11:20 30 Jul 2005

sorry it took a while to reply!
Ive tryed it out and it works liek a charm!!

You got all the answers!
Thanks for all your excel help so far!

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

AMD Radeon Adrenalin release date, new features, compatible graphics cards

Inside the iMac Pro - Apple's most powerful Mac yet

iMac Pro release date, UK price & specs

Comment nettoyer Windows et optimiser son PC gratuitement ?