Macro Excel 2007 helpClosed

Question

Hello,
hey guys i need help in excel.... assignment  in my row F I have 2 data Permanent and contract. So i want to make it that when I type it in sheet 1 Row F Permanent it will go and copy  to sheet 2 row F and when i type contract it will copy to sheet 3 row F.... It is possible?

venkat1926 · Answer

there is no row F in my  version of  excel 2007. rows are numbered 1,2,3 etc and columns are lettered A,B.C etc
so clarify 

in the row or colmn in which cell copying is to be done. rephrase your question;.

UrbanStory · Answer

im sry i mean columns

venkat1926 · Answer

you have not answered my second question. I assumed that you want the word permanent or contract if entered in sheet 1 column F this entry will be copied in the column F of sheet 2 or sheet 3 accordingly.
your main sheet is sheet 1

open vb editor (alt+F11)
click control+R
the name of your workbook will be there . Under this name double click sheet1.
you will get an event handler window for sheet 1.
there copy paste this code.
now you enter eithr permanent or contract in column F anyhwere and check sheet 2 or sheet 3

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column <> 6 Then Exit Sub
Target.Copy
If Target = "permanent" Then
With Worksheets("sheet2")
.Cells(Rows.Count, "F").End(xlUp).Offset(1, 0).PasteSpecial
End With
ElseIf Target = "contract" Then
With Worksheets("sheet3")
.Cells(Rows.Count, "F").End(xlUp).Offset(1, 0).PasteSpecial
End With
Else
Exit Sub
End If
application.CutCopyMode=false
End Sub

UrbanStory · Answer

sry... yea something like that but i want the whole A - I following the copy as well. It is possible? :)

venkat1926 · Answer

the macro is slightly modified 

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column <> 6 Then Exit Sub

If Target = "permanent" Then
Range(Cells(Target.Row, "A"), Cells(Target.Row, "I")).Copy
With Worksheets("sheet2")
.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).PasteSpecial
End With
ElseIf Target = "contract" Then
Range(Cells(Target.Row, "A"), Cells(Target.Row, "I")).Copy
With Worksheets("sheet3")
.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).PasteSpecial
End With
Else
Exit Sub
End If
Application.CutCopyMode = False
End Sub

UrbanStory · Answer

Hey thx a lot man.. it help... i ask my lecture she say it is best if that when i change/delete what ever in A to I other then F sheet 2 and sheet 3 will also change as well. Sry that im asking a lot of favor :/ And also thx for helping :)

venkat1926 · Answer

your question is not clear. I am trying to rephrase it. see whether it is ok

At present if the entry in column F is changed to permanent the range A to I of that row copied to sheet2
similarly if entry in F is contract it is copied into sheet 3
now

if any cells in columns A to I changes to permanent /contract the same action is to be done

if so I do not know what you are going to achieve. 

case 1 col. F is changed to "permanent" then it is copied in sheet 2
suppose you change column C to contract it will go to sheet 3 but in that sheet the column F will continue o be permanent unless you do this in another row. 

anyhow I have slightly modified .  see whether it helps 
KEEP YOUR ORIGINAL FILE SAFE SOMEWHERE SO THAT YOU CAN RETRIEVE THE FILE IF THERE IS A MESS UP IN RUNNING THE MACRO 


Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column > 9 Then Exit Sub
if Target="" then exit  sub
If Target = "permanent" Then
Range(Cells(Target.Row, "A"), Cells(Target.Row, "I")).Copy
With Worksheets("sheet2")
.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).PasteSpecial
End With
ElseIf Target = "contract" Then
Range(Cells(Target.Row, "A"), Cells(Target.Row, "I")).Copy
With Worksheets("sheet3")
.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).PasteSpecial
End With
Else
Exit Sub
End If
Application.CutCopyMode = False
End Sub

Macro Excel 2007 help

7 responses

Similar discussions

Subscribe To Our Newsletter!