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.

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" <MINALZUNKE@discussions.microsoft.com> wrote in message
news:D4B5D5E9-DA76-4BD1-8760-AC10446C6A7B@microsoft.com...
> 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.
>
>
>
>
>

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" <MINALZUNKE@discussions.microsoft.com> wrote in message
> news:D4B5D5E9-DA76-4BD1-8760-AC10446C6A7B@microsoft.com...
> > 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.
> >
> >
> >
> >
> >
>
>
>

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" <MINALZUNKE@discussions.microsoft.com> wrote in message
news:D11081D1-6028-42A2-A133-BFA85712FD99@microsoft.com...
> 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" <MINALZUNKE@discussions.microsoft.com> wrote in message
> > news:D4B5D5E9-DA76-4BD1-8760-AC10446C6A7B@microsoft.com...
> > > 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.
> > >
> > >
> > >
> > >
> > >
> >
> >
> >

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" <MINALZUNKE@discussions.microsoft.com> wrote in message
> news:D11081D1-6028-42A2-A133-BFA85712FD99@microsoft.com...
> > 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" <MINALZUNKE@discussions.microsoft.com> wrote in message
> > > news:D4B5D5E9-DA76-4BD1-8760-AC10446C6A7B@microsoft.com...
> > > > 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.
> > > >
> > > >
> > > >
> > > >
> > > >
> > >
> > >
> > >
>
>
>

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" <MINALZUNKE@discussions.microsoft.com> wrote in message
news:55684D59-6059-4183-8B86-714A597B9FEB@microsoft.com...
> 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" <MINALZUNKE@discussions.microsoft.com> wrote in message
> > news:D11081D1-6028-42A2-A133-BFA85712FD99@microsoft.com...
> > > 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" <MINALZUNKE@discussions.microsoft.com> wrote in
message
> > > > news:D4B5D5E9-DA76-4BD1-8760-AC10446C6A7B@microsoft.com...
> > > > > 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.
> > > > >
> > > > >
> > > > >
> > > > >
> > > > >
> > > >
> > > >
> > > >
> >
> >
> >