Excel Excel copying rows automatically

[dean.grimshaw]

Hi there apologies if this is simple stuff but im still a novice...

I have an excel spreadsheet, with 8 tabs,, Long Term is the main tab where i 'enter information'

the other tabs 1,2,3,4,5,Dead and Filled are Locked tabs.

I enter my information in each row on the Long Term tab. i need the spreadsheet to automatically move the rows to the relevant tab. I have set up a validation with a drop down for 1-2-3-4-5-Dead and Filled in one coloumn. is this possible and if so please tell me how.

so if a line is - John Smith , 25/11/2012, 28/5/13, then Dead.. It would need to go to the 'Dead TAB'...

and if it was Guinness, 29/11/2012, 21/12/12 then 2 ... it would need to go to the '2 Tab'

HOWEVER the lines would still need to stay in the Long Term Tab to be edited


Thanks in advance

Dean

 

[Flona2012]

hi
if we cosidered that the number of columns in Long Term Tab(MainTab) is 4
1 is name
2 is date
3 is another date
4 is the key

then it would go like this

First Phase: Getting Data when entering New Info Record in the MainTab
right click on the maintab and then select "ViewCode"
and then enter the following

Private Sub Worksheet_Change(ByVal Target As Range)
'Whenever cell change by entring data this sub will be activated
dim nRowIndex as integer

nRowIndex=CINT(Target.Row) 'To get the row number for the new cell

if bCheckDataCompleted(nRowIndex) then 'This function is to check if all row data entered

vInsertToSheet(nRowIndex) 'This sub is to enter data to the correct sheet

End if
End sub

***************************************************************************
Private Function bCheckDataCompleted (byVal nRowIndex as integer) as Boolean

If Sheets(“LongTerm”).Cells(nRowIndex,1).value <> ”” and Sheets(“LongTerm”).Cells(nRowIndex,2).value <> ”” and Sheets(“LongTerm”).Cells(nRowIndex,3).value <> ”” and Sheets(“LongTerm”).Cells(nRowIndex,4).value <> ”” then

bCheckDataCompleted = True

else

bCheckDataCompleted = False

End if
End Function






*****************************************************************************
Private Sub vInsertToSheet (byVal nRowIndex as integer)
Dim szSheetName as string
Dim nRowIndex2 as integer

szSheetName= cstr(Sheets(“LongTerm”).Cells(nRowIndex,4).value)

nRowIndex2=1
While Sheets(“’” & szSheetName & “’”).Cells(nRowIndex2,1).value <> ””
‘To get the first empty row in the destination sheet
nRowIndex2=nRowIndex2+1
wend

Sheets(“’” & szSheetName & “’”).Cells(nRowIndex2,1).Value=Sheets(“LongTerm”).cells(nRowIndex,1).value
Sheets(“’” & szSheetName & “’”).Cells(nRowIndex2,2).Value=Sheets(“LongTerm”).cells(nRowIndex,2).value
Sheets(“’” & szSheetName & “’”).Cells(nRowIndex2,3).Value=Sheets(“LongTerm”).cells(nRowIndex,3).value

End Sub
************************************************************************************************************

As more details:
If the key in the LongTerm is the SheetName then this should work
Else then you have to do multi if statements
Like
If Key=”Dead” then
szSheetName=”SheetName”
elseif ………
…… etc
End if

Sheets(“LongTerm”).cells(nRowIndex,1) the 1 is the column index

Black is description
Green is code description
Blue is the Code it self
Orange is other Sub or Function inside Sub

Hope you got it J