Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
macro editting
Hi
Experts I m working on excel sheet. I have used column A to W. Some of the column have VB script and conditional formatting in it. After some of the operations are performed column F has "C" in it. ( which is entered manually) What I am after? As soon as column F has "C" in it , I want the entire information for that row from columnA to column W ( or if in future column number exceeds) get cut and paste over to next available blank row on sheet 3. The blank row on sheet 1 should get filled automatically. What I tried ( while recording macro) If column F has "C" in it. I recorded a macro to select the particular row from column A to W cut that selected row make sheet 3 active find next available blank row on active sheet Paste the entire row from column A to W make sheet 1 active blank row to get moved up automatically( I didn't try this) select save The recorded macro works fine. But somebody has to press either button to do this or any short-cut key If I select while defining macro. This is time consuming in production environment. What I am after? I am trying to write the VB script to get this done aumatically as soon as somebody puts "C" in column F. I tried this way If (Target.Column = 6) Then [A=1, B=2, C=3, .... F=6, ...... Z=26] If Target.Value = "C" Then Range("$A$4:$W$4").Select [check rhis] Selection.Cut Sheets("sheet3").Select Range("$A$4:$W$4").Select [check this, I tried Range("A65536"). End(xlUp)(2).Select ActiveSheet.Paste Sheets("sheet1").Select <<<<< <<<<<< ( fill up the blank row automatically)( don't know code) ActiveWorkbook.Save End If End If Somebody who can go through this code and amend this to get it working. I will be really very thankful. Thanking in anticipation. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
macro editting
If Target.count 0 then exit sub
If Target.Column = 6 Then If Target.Value = "C" Then Cells(Target.Row,1).Resize(1,23).Copy _ Destination:=worksheets("Sheet3").Cells(Rows.count , _ 1).End(xlup)(2) End if ActiveWorkbook.Save End If -- Regards, Tom Ogilvy "MINAL ZUNKE" wrote in message ... Hi Experts I m working on excel sheet. I have used column A to W. Some of the column have VB script and conditional formatting in it. After some of the operations are performed column F has "C" in it. ( which is entered manually) What I am after? As soon as column F has "C" in it , I want the entire information for that row from columnA to column W ( or if in future column number exceeds) get cut and paste over to next available blank row on sheet 3. The blank row on sheet 1 should get filled automatically. What I tried ( while recording macro) If column F has "C" in it. I recorded a macro to select the particular row from column A to W cut that selected row make sheet 3 active find next available blank row on active sheet Paste the entire row from column A to W make sheet 1 active blank row to get moved up automatically( I didn't try this) select save The recorded macro works fine. But somebody has to press either button to do this or any short-cut key If I select while defining macro. This is time consuming in production environment. What I am after? I am trying to write the VB script to get this done aumatically as soon as somebody puts "C" in column F. I tried this way If (Target.Column = 6) Then [A=1, B=2, C=3, .... F=6, ...... Z=26] If Target.Value = "C" Then Range("$A$4:$W$4").Select [check rhis] Selection.Cut Sheets("sheet3").Select Range("$A$4:$W$4").Select [check this, I tried Range("A65536"). End(xlUp)(2).Select ActiveSheet.Paste Sheets("sheet1").Select <<<<< <<<<<< ( fill up the blank row automatically)( don't know code) ActiveWorkbook.Save End If End If Somebody who can go through this code and amend this to get it working. I will be really very thankful. Thanking in anticipation. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
macro editting
hi
Tom Thnx for code It is giving compile error and do I replce it all with the existing code. I am new to VB so don't know much about it. Thnx "Tom Ogilvy" wrote: If Target.count 0 then exit sub If Target.Column = 6 Then If Target.Value = "C" Then Cells(Target.Row,1).Resize(1,23).Copy _ Destination:=worksheets("Sheet3").Cells(Rows.count , _ 1).End(xlup)(2) End if ActiveWorkbook.Save End If -- Regards, Tom Ogilvy "MINAL ZUNKE" wrote in message ... Hi Experts I m working on excel sheet. I have used column A to W. Some of the column have VB script and conditional formatting in it. After some of the operations are performed column F has "C" in it. ( which is entered manually) What I am after? As soon as column F has "C" in it , I want the entire information for that row from columnA to column W ( or if in future column number exceeds) get cut and paste over to next available blank row on sheet 3. The blank row on sheet 1 should get filled automatically. What I tried ( while recording macro) If column F has "C" in it. I recorded a macro to select the particular row from column A to W cut that selected row make sheet 3 active find next available blank row on active sheet Paste the entire row from column A to W make sheet 1 active blank row to get moved up automatically( I didn't try this) select save The recorded macro works fine. But somebody has to press either button to do this or any short-cut key If I select while defining macro. This is time consuming in production environment. What I am after? I am trying to write the VB script to get this done aumatically as soon as somebody puts "C" in column F. I tried this way If (Target.Column = 6) Then [A=1, B=2, C=3, .... F=6, ...... Z=26] If Target.Value = "C" Then Range("$A$4:$W$4").Select [check rhis] Selection.Cut Sheets("sheet3").Select Range("$A$4:$W$4").Select [check this, I tried Range("A65536"). End(xlUp)(2).Select ActiveSheet.Paste Sheets("sheet1").Select <<<<< <<<<<< ( fill up the blank row automatically)( don't know code) ActiveWorkbook.Save End If End If Somebody who can go through this code and amend this to get it working. I will be really very thankful. Thanking in anticipation. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
macro editting
There was a typo. The zero should have been 1.
Right click on the sheet tab where you will be entering the C. Select View Code. Paste in this code: Private Sub Worksheet_Change(ByVal Target As Range) If Target.Count 1 Then Exit Sub If Target.Column = 6 Then If Target.Value = "C" Then Cells(Target.Row, 1).Resize(1, 23).Copy _ Destination:=Worksheets("Sheet3").Cells(Rows.Count , _ 1).End(xlUp)(2) End If ActiveWorkbook.Save End If End Sub There should be no other code associated with the CHANGE event. Works fine for me. -- Regards, Tom Ogilvy "MINAL ZUNKE" wrote in message ... hi Tom Thnx for code It is giving compile error and do I replce it all with the existing code. I am new to VB so don't know much about it. Thnx "Tom Ogilvy" wrote: If Target.count 0 then exit sub If Target.Column = 6 Then If Target.Value = "C" Then Cells(Target.Row,1).Resize(1,23).Copy _ Destination:=worksheets("Sheet3").Cells(Rows.count , _ 1).End(xlup)(2) End if ActiveWorkbook.Save End If -- Regards, Tom Ogilvy "MINAL ZUNKE" wrote in message ... Hi Experts I m working on excel sheet. I have used column A to W. Some of the column have VB script and conditional formatting in it. After some of the operations are performed column F has "C" in it. ( which is entered manually) What I am after? As soon as column F has "C" in it , I want the entire information for that row from columnA to column W ( or if in future column number exceeds) get cut and paste over to next available blank row on sheet 3. The blank row on sheet 1 should get filled automatically. What I tried ( while recording macro) If column F has "C" in it. I recorded a macro to select the particular row from column A to W cut that selected row make sheet 3 active find next available blank row on active sheet Paste the entire row from column A to W make sheet 1 active blank row to get moved up automatically( I didn't try this) select save The recorded macro works fine. But somebody has to press either button to do this or any short-cut key If I select while defining macro. This is time consuming in production environment. What I am after? I am trying to write the VB script to get this done aumatically as soon as somebody puts "C" in column F. I tried this way If (Target.Column = 6) Then [A=1, B=2, C=3, .... F=6, ...... Z=26] If Target.Value = "C" Then Range("$A$4:$W$4").Select [check rhis] Selection.Cut Sheets("sheet3").Select Range("$A$4:$W$4").Select [check this, I tried Range("A65536"). End(xlUp)(2).Select ActiveSheet.Paste Sheets("sheet1").Select <<<<< <<<<<< ( fill up the blank row automatically)( don't know code) ActiveWorkbook.Save End If End If Somebody who can go through this code and amend this to get it working. I will be really very thankful. Thanking in anticipation. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
macro editting
Hi
Tom This is working fine. It is copying the entire row I fI type C in column F. But my main aim is to cut and paste that entire row and not to copy. The blank row in worksheet 1 should also get filled automatically I hope for amendment in the code! Thanking you in advance "Tom Ogilvy" wrote: There was a typo. The zero should have been 1. Right click on the sheet tab where you will be entering the C. Select View Code. Paste in this code: Private Sub Worksheet_Change(ByVal Target As Range) If Target.Count 1 Then Exit Sub If Target.Column = 6 Then If Target.Value = "C" Then Cells(Target.Row, 1).Resize(1, 23).Copy _ Destination:=Worksheets("Sheet3").Cells(Rows.Count , _ 1).End(xlUp)(2) End If ActiveWorkbook.Save End If End Sub There should be no other code associated with the CHANGE event. Works fine for me. -- Regards, Tom Ogilvy "MINAL ZUNKE" wrote in message ... hi Tom Thnx for code It is giving compile error and do I replce it all with the existing code. I am new to VB so don't know much about it. Thnx "Tom Ogilvy" wrote: If Target.count 0 then exit sub If Target.Column = 6 Then If Target.Value = "C" Then Cells(Target.Row,1).Resize(1,23).Copy _ Destination:=worksheets("Sheet3").Cells(Rows.count , _ 1).End(xlup)(2) End if ActiveWorkbook.Save End If -- Regards, Tom Ogilvy "MINAL ZUNKE" wrote in message ... Hi Experts I m working on excel sheet. I have used column A to W. Some of the column have VB script and conditional formatting in it. After some of the operations are performed column F has "C" in it. ( which is entered manually) What I am after? As soon as column F has "C" in it , I want the entire information for that row from columnA to column W ( or if in future column number exceeds) get cut and paste over to next available blank row on sheet 3. The blank row on sheet 1 should get filled automatically. What I tried ( while recording macro) If column F has "C" in it. I recorded a macro to select the particular row from column A to W cut that selected row make sheet 3 active find next available blank row on active sheet Paste the entire row from column A to W make sheet 1 active blank row to get moved up automatically( I didn't try this) select save The recorded macro works fine. But somebody has to press either button to do this or any short-cut key If I select while defining macro. This is time consuming in production environment. What I am after? I am trying to write the VB script to get this done aumatically as soon as somebody puts "C" in column F. I tried this way If (Target.Column = 6) Then [A=1, B=2, C=3, .... F=6, ...... Z=26] If Target.Value = "C" Then Range("$A$4:$W$4").Select [check rhis] Selection.Cut Sheets("sheet3").Select Range("$A$4:$W$4").Select [check this, I tried Range("A65536"). End(xlUp)(2).Select ActiveSheet.Paste Sheets("sheet1").Select <<<<< <<<<<< ( fill up the blank row automatically)( don't know code) ActiveWorkbook.Save End If End If Somebody who can go through this code and amend this to get it working. I will be really very thankful. Thanking in anticipation. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
macro editting
Private Sub Worksheet_Change(ByVal Target As Range)
On Error Goto ErrHandler If Target.Count 1 Then Exit Sub If Target.Column = 6 Then If Target.Value = "C" Then Cells(Target.Row, 1).Resize(1, 23).Copy _ Destination:=Worksheets("Sheet3").Cells(Rows.Count , _ 1).End(xlUp)(2) Application.EnableEvents = False Target.EntireRow.Delete ThisWorkbook.Save End If End If ErrHandler: Application.EnableEvents = True End Sub -- Regards, Tom Ogilvy "MINAL ZUNKE" wrote in message ... Hi Tom This is working fine. It is copying the entire row I fI type C in column F. But my main aim is to cut and paste that entire row and not to copy. The blank row in worksheet 1 should also get filled automatically I hope for amendment in the code! Thanking you in advance "Tom Ogilvy" wrote: There was a typo. The zero should have been 1. Right click on the sheet tab where you will be entering the C. Select View Code. Paste in this code: Private Sub Worksheet_Change(ByVal Target As Range) If Target.Count 1 Then Exit Sub If Target.Column = 6 Then If Target.Value = "C" Then Cells(Target.Row, 1).Resize(1, 23).Copy _ Destination:=Worksheets("Sheet3").Cells(Rows.Count , _ 1).End(xlUp)(2) End If ActiveWorkbook.Save End If End Sub There should be no other code associated with the CHANGE event. Works fine for me. -- Regards, Tom Ogilvy "MINAL ZUNKE" wrote in message ... hi Tom Thnx for code It is giving compile error and do I replce it all with the existing code. I am new to VB so don't know much about it. Thnx "Tom Ogilvy" wrote: If Target.count 0 then exit sub If Target.Column = 6 Then If Target.Value = "C" Then Cells(Target.Row,1).Resize(1,23).Copy _ Destination:=worksheets("Sheet3").Cells(Rows.count , _ 1).End(xlup)(2) End if ActiveWorkbook.Save End If -- Regards, Tom Ogilvy "MINAL ZUNKE" wrote in message ... Hi Experts I m working on excel sheet. I have used column A to W. Some of the column have VB script and conditional formatting in it. After some of the operations are performed column F has "C" in it. ( which is entered manually) What I am after? As soon as column F has "C" in it , I want the entire information for that row from columnA to column W ( or if in future column number exceeds) get cut and paste over to next available blank row on sheet 3. The blank row on sheet 1 should get filled automatically. What I tried ( while recording macro) If column F has "C" in it. I recorded a macro to select the particular row from column A to W cut that selected row make sheet 3 active find next available blank row on active sheet Paste the entire row from column A to W make sheet 1 active blank row to get moved up automatically( I didn't try this) select save The recorded macro works fine. But somebody has to press either button to do this or any short-cut key If I select while defining macro. This is time consuming in production environment. What I am after? I am trying to write the VB script to get this done aumatically as soon as somebody puts "C" in column F. I tried this way If (Target.Column = 6) Then [A=1, B=2, C=3, .... F=6, ...... Z=26] If Target.Value = "C" Then Range("$A$4:$W$4").Select [check rhis] Selection.Cut Sheets("sheet3").Select Range("$A$4:$W$4").Select [check this, I tried Range("A65536"). End(xlUp)(2).Select ActiveSheet.Paste Sheets("sheet1").Select <<<<< <<<<<< ( fill up the blank row automatically)( don't know code) ActiveWorkbook.Save End If End If Somebody who can go through this code and amend this to get it working. I will be really very thankful. Thanking in anticipation. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
I need help with a table formula/mass editting | New Users to Excel | |||
editting a template | New Users to Excel | |||
Hexidecimal Editting | Excel Programming | |||
Possible to see which user is editting/using file? | Excel Programming | |||
Editting Formulas Using a Macro | Excel Programming |