Macro Excel 2007 help

Closed
Posts
4
Registration date
Sunday July 12, 2009
Status
Member
Last seen
July 15, 2009
-
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
-
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?

7 replies

Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
804
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;.
Posts
4
Registration date
Sunday July 12, 2009
Status
Member
Last seen
July 15, 2009

im sry i mean columns
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
804
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
Posts
4
Registration date
Sunday July 12, 2009
Status
Member
Last seen
July 15, 2009

sry... yea something like that but i want the whole A - I following the copy as well. It is possible? :)
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
804
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
Posts
4
Registration date
Sunday July 12, 2009
Status
Member
Last seen
July 15, 2009

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 :)
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
804
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