ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   find specific data in row and select and copy entirerow (https://www.excelbanter.com/excel-programming/336651-find-specific-data-row-select-copy-entirerow.html)

Junior728
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

K Dales[_2_]
find specific data in row and select and copy entirerow
 
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
find specific data in row and select and copy entirerow
 
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

K Dales[_2_]
find specific data in row and select and copy entirerow
 
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


All times are GMT +1. The time now is 08:53 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com