Macro Excel 2007 help
Closed
UrbanStory
Posts
4
Registration date
Sunday July 12, 2009
Status
Member
Last seen
July 15, 2009
-
Jul 13, 2009 at 10:36 AM
venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 - Jul 15, 2009 at 08:26 PM
venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 - Jul 15, 2009 at 08:26 PM
Related:
- Macro Excel 2007 help
- Save as pdf office 2007 - Download - Other
- Spell number in excel without macro - Guide
- Excel marksheet - Guide
- Excel free download - Download - Spreadsheets
- Macros in excel download - Download - Spreadsheets
7 responses
venkat1926
Posts
1863
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
811
Jul 13, 2009 at 08:08 PM
Jul 13, 2009 at 08:08 PM
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;.
so clarify
in the row or colmn in which cell copying is to be done. rephrase your question;.
UrbanStory
Posts
4
Registration date
Sunday July 12, 2009
Status
Member
Last seen
July 15, 2009
Jul 14, 2009 at 04:11 AM
Jul 14, 2009 at 04:11 AM
im sry i mean columns
venkat1926
Posts
1863
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
811
Jul 14, 2009 at 08:07 PM
Jul 14, 2009 at 08:07 PM
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
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
Posts
4
Registration date
Sunday July 12, 2009
Status
Member
Last seen
July 15, 2009
Jul 14, 2009 at 08:58 PM
Jul 14, 2009 at 08:58 PM
sry... yea something like that but i want the whole A - I following the copy as well. It is possible? :)
Didn't find the answer you are looking for?
Ask a question
venkat1926
Posts
1863
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
811
Jul 15, 2009 at 06:35 AM
Jul 15, 2009 at 06:35 AM
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
Posts
4
Registration date
Sunday July 12, 2009
Status
Member
Last seen
July 15, 2009
Jul 15, 2009 at 11:53 AM
Jul 15, 2009 at 11:53 AM
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
Posts
1863
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
811
Jul 15, 2009 at 08:26 PM
Jul 15, 2009 at 08:26 PM
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
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