macro editting

[Guest]

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.

 

[Tom Ogilvy]

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

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.

 

[Guest]

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

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

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.

 

[Tom Ogilvy]

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

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

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

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.

 

[Guest]

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

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

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

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

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.

 

[Tom Ogilvy]

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

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

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

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

:

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

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.