find specific data in row and select and copy entirerow

Hi Sir,

I am trying to find a row that contains a specific part number in one
worksheet and copy that line into another worksheet. However, the vb debugger
also find error in the line activecellrow.select that disallow it from copy
the selected row and copy it into the destinated worksheet. What shall i do?
How do you write it properly in vba, coz i am using the record macro
available in the excel to create below lines.

Example of mine:


Sub Reschedule()

Range("A1").Select
With Application
.Calculation = xlAutomatic
End With
With Application
.ReferenceStyle = xlA1
End With


Range("A1").Select
NumOfRows = Cells(Rows.Count, 1).End(xlUp).Row

Workbooks.Open Filename:="G:\Asia\Product\Operations\Part
Adjustments\VSJ Reschedule\vsj.xls"
Windows("vsj.xls").Activate

Windows("VSJ Reschedule1.xls").Activate
Sheets.Add

Windows("vsj.xls").Activate
Sheets("vsj").Select
Application.CutCopyMode = False
Sheets("vsj").Copy After:=Workbooks("VSJ Reschedule1.xls").Sheets(2)
ActiveWindow.SmallScroll Down:=-15


Windows("VSJ Reschedule1.xls").Activate

i = ActiveCell.Row


For i = 2 To 100

If Len(Worksheets("Sheet1").Cells(i, 2)) < 30 Then


Windows("VSJ Reschedule1.xls").Activate
Sheets("Sheet1").Select
Range("B2").Select
Selection.Copy

Sheets("vsj").Select
Columns("F:F").Select
Selection.Find(What:="A0699-5327", After:=ActiveCell,
LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate

ActiveCellRow.Select
Application.CutCopyMode = 1
Selection.Copy
Sheets("Sheet2").Select
ActiveSheet.Paste

Sheets("Sheet1").Select
Range("B6").Select
Application.CutCopyMode = False
Selection.Copy

End If

Next i

MsgBox ("Please run Macro2 after filling in all info")

End Sub

It should be ActiveCell.Row.Select
--
- K Dales


"Junior728" wrote:

> Hi Sir,
>
> I am trying to find a row that contains a specific part number in one
> worksheet and copy that line into another worksheet. However, the vb debugger
> also find error in the line activecellrow.select that disallow it from copy
> the selected row and copy it into the destinated worksheet. What shall i do?
> How do you write it properly in vba, coz i am using the record macro
> available in the excel to create below lines.
>
> Example of mine:
>
>
> Sub Reschedule()
>
> Range("A1").Select
> With Application
> .Calculation = xlAutomatic
> End With
> With Application
> .ReferenceStyle = xlA1
> End With
>
>
> Range("A1").Select
> NumOfRows = Cells(Rows.Count, 1).End(xlUp).Row
>
> Workbooks.Open Filename:="G:\Asia\Product\Operations\Part
> Adjustments\VSJ Reschedule\vsj.xls"
> Windows("vsj.xls").Activate
>
> Windows("VSJ Reschedule1.xls").Activate
> Sheets.Add
>
> Windows("vsj.xls").Activate
> Sheets("vsj").Select
> Application.CutCopyMode = False
> Sheets("vsj").Copy After:=Workbooks("VSJ Reschedule1.xls").Sheets(2)
> ActiveWindow.SmallScroll Down:=-15
>
>
> Windows("VSJ Reschedule1.xls").Activate
>
> i = ActiveCell.Row
>
>
> For i = 2 To 100
>
> If Len(Worksheets("Sheet1").Cells(i, 2)) < 30 Then
>
>
> Windows("VSJ Reschedule1.xls").Activate
> Sheets("Sheet1").Select
> Range("B2").Select
> Selection.Copy
>
> Sheets("vsj").Select
> Columns("F:F").Select
> Selection.Find(What:="A0699-5327", After:=ActiveCell,
> LookIn:=xlFormulas, _
> LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
> MatchCase:=False, SearchFormat:=False).Activate
>
> ActiveCellRow.Select
> Application.CutCopyMode = 1
> Selection.Copy
> Sheets("Sheet2").Select
> ActiveSheet.Paste
>
> Sheets("Sheet1").Select
> Range("B6").Select
> Application.CutCopyMode = False
> Selection.Copy
>
> End If
>
> Next i
>
> MsgBox ("Please run Macro2 after filling in all info")
>
> End Sub
>

Probably:
activecell.entirerow.select

K Dales wrote:
>
> It should be ActiveCell.Row.Select
> --
> - K Dales
>
> "Junior728" wrote:
>
> > Hi Sir,
> >
> > I am trying to find a row that contains a specific part number in one
> > worksheet and copy that line into another worksheet. However, the vb debugger
> > also find error in the line activecellrow.select that disallow it from copy
> > the selected row and copy it into the destinated worksheet. What shall i do?
> > How do you write it properly in vba, coz i am using the record macro
> > available in the excel to create below lines.
> >
> > Example of mine:
> >
> >
> > Sub Reschedule()
> >
> > Range("A1").Select
> > With Application
> > .Calculation = xlAutomatic
> > End With
> > With Application
> > .ReferenceStyle = xlA1
> > End With
> >
> >
> > Range("A1").Select
> > NumOfRows = Cells(Rows.Count, 1).End(xlUp).Row
> >
> > Workbooks.Open Filename:="G:\Asia\Product\Operations\Part
> > Adjustments\VSJ Reschedule\vsj.xls"
> > Windows("vsj.xls").Activate
> >
> > Windows("VSJ Reschedule1.xls").Activate
> > Sheets.Add
> >
> > Windows("vsj.xls").Activate
> > Sheets("vsj").Select
> > Application.CutCopyMode = False
> > Sheets("vsj").Copy After:=Workbooks("VSJ Reschedule1.xls").Sheets(2)
> > ActiveWindow.SmallScroll Down:=-15
> >
> >
> > Windows("VSJ Reschedule1.xls").Activate
> >
> > i = ActiveCell.Row
> >
> >
> > For i = 2 To 100
> >
> > If Len(Worksheets("Sheet1").Cells(i, 2)) < 30 Then
> >
> >
> > Windows("VSJ Reschedule1.xls").Activate
> > Sheets("Sheet1").Select
> > Range("B2").Select
> > Selection.Copy
> >
> > Sheets("vsj").Select
> > Columns("F:F").Select
> > Selection.Find(What:="A0699-5327", After:=ActiveCell,
> > LookIn:=xlFormulas, _
> > LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
> > MatchCase:=False, SearchFormat:=False).Activate
> >
> > ActiveCellRow.Select
> > Application.CutCopyMode = 1
> > Selection.Copy
> > Sheets("Sheet2").Select
> > ActiveSheet.Paste
> >
> > Sheets("Sheet1").Select
> > Range("B6").Select
> > Application.CutCopyMode = False
> > Selection.Copy
> >
> > End If
> >
> > Next i
> >
> > MsgBox ("Please run Macro2 after filling in all info")
> >
> > End Sub
> >

--

Dave Peterson

Yes, my mistake!
--
- K Dales


"Dave Peterson" wrote:

> Probably:
> activecell.entirerow.select
>
> K Dales wrote:
> >
> > It should be ActiveCell.Row.Select
> > --
> > - K Dales
> >
> > "Junior728" wrote:
> >
> > > Hi Sir,
> > >
> > > I am trying to find a row that contains a specific part number in one
> > > worksheet and copy that line into another worksheet. However, the vb debugger
> > > also find error in the line activecellrow.select that disallow it from copy
> > > the selected row and copy it into the destinated worksheet. What shall i do?
> > > How do you write it properly in vba, coz i am using the record macro
> > > available in the excel to create below lines.
> > >
> > > Example of mine:
> > >
> > >
> > > Sub Reschedule()
> > >
> > > Range("A1").Select
> > > With Application
> > > .Calculation = xlAutomatic
> > > End With
> > > With Application
> > > .ReferenceStyle = xlA1
> > > End With
> > >
> > >
> > > Range("A1").Select
> > > NumOfRows = Cells(Rows.Count, 1).End(xlUp).Row
> > >
> > > Workbooks.Open Filename:="G:\Asia\Product\Operations\Part
> > > Adjustments\VSJ Reschedule\vsj.xls"
> > > Windows("vsj.xls").Activate
> > >
> > > Windows("VSJ Reschedule1.xls").Activate
> > > Sheets.Add
> > >
> > > Windows("vsj.xls").Activate
> > > Sheets("vsj").Select
> > > Application.CutCopyMode = False
> > > Sheets("vsj").Copy After:=Workbooks("VSJ Reschedule1.xls").Sheets(2)
> > > ActiveWindow.SmallScroll Down:=-15
> > >
> > >
> > > Windows("VSJ Reschedule1.xls").Activate
> > >
> > > i = ActiveCell.Row
> > >
> > >
> > > For i = 2 To 100
> > >
> > > If Len(Worksheets("Sheet1").Cells(i, 2)) < 30 Then
> > >
> > >
> > > Windows("VSJ Reschedule1.xls").Activate
> > > Sheets("Sheet1").Select
> > > Range("B2").Select
> > > Selection.Copy
> > >
> > > Sheets("vsj").Select
> > > Columns("F:F").Select
> > > Selection.Find(What:="A0699-5327", After:=ActiveCell,
> > > LookIn:=xlFormulas, _
> > > LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
> > > MatchCase:=False, SearchFormat:=False).Activate
> > >
> > > ActiveCellRow.Select
> > > Application.CutCopyMode = 1
> > > Selection.Copy
> > > Sheets("Sheet2").Select
> > > ActiveSheet.Paste
> > >
> > > Sheets("Sheet1").Select
> > > Range("B6").Select
> > > Application.CutCopyMode = False
> > > Selection.Copy
> > >
> > > End If
> > >
> > > Next i
> > >
> > > MsgBox ("Please run Macro2 after filling in all info")
> > >
> > > End Sub
> > >
>
> --
>
> Dave Peterson
>