| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How do I find and copy rows based on specific criteria? | New Users to Excel | |||
| Select, find in another book, copy, return and paste | Excel Discussion (Misc queries) | |||
| Select specific Data from a cell | Excel Programming | |||
| Auto find a specific row and copy it to another worksheet | Excel Programming | |||
| Auto find a specific row and copy it to another worksheet | Excel Programming | |||
Hybrid Mode
