Dear all,
I'd like to copy data from "Data Pur" to two different sheets ie the Master(Pur) and Fix Asset+Eva when column 7 >0 and column 6 =2, then clear the content in the Data Pur after that. but it appears that the subscript is out of range. Wonder if anyone can help.
Sub Copyrowfromdatainputtopurchase()
Sheets("Master(Pur)").Unprotect
Sheets("Data Pur").Select
Range("A2:K25").Copy
Sheets(".").Select
Range("A2").Select
ActiveSheet.Paste
Application.CutCopyMode = False
'
' autofitPO Macro
'
'
Rows("2:21").Select
Selection.Rows.AutoFit
a = Worksheets("Data Pur").Cells(Rows.Count, 1).End(xlUp).Row
For i = 2 To a
If Worksheets("Data Pur").Cells(i, 7).Value > "0" Then
Worksheets("Data Pur").Rows(i).Copy
Worksheets("Master(Pur)").Activate
c = Worksheets("Master(Pur)").Cells(Rows.Count, 1).End(xlUp).Row
Worksheets("Master(Pur)").Cells(c + 1, 1).Select
ElseIf Worksheets("Data Pur").Cells(i, 6).Value > 2 Then
Worksheets("Data Pur").Rows(i).Copy
Worksheets("Fix Assets+Eva").Activate
d = Worksheets("Fix Assets+Eva)").Cells(Rows.Count, 1).End(xlUp).Row
Worksheets("Fix Assets+Eva").Cells(d + 1, 1).Select
ActiveCell.PasteSpecial Paste:=xlValues, operation:=xlPasteSpecialOperationNone
Worksheets("Data Pur").Activate
End If
Next
Application.CutCopyMode = False
ThisWorkbook.Worksheets("Data Pur").Cells(1, 1).Select
Worksheets("Data Pur").Range("A2:K24").SpecialCells(xlCellTypeConstants).ClearContents
Sheets("Master(Pur)").Protect
End Sub
I'd like to copy data from "Data Pur" to two different sheets ie the Master(Pur) and Fix Asset+Eva when column 7 >0 and column 6 =2, then clear the content in the Data Pur after that. but it appears that the subscript is out of range. Wonder if anyone can help.
Sub Copyrowfromdatainputtopurchase()
Sheets("Master(Pur)").Unprotect
Sheets("Data Pur").Select
Range("A2:K25").Copy
Sheets(".").Select
Range("A2").Select
ActiveSheet.Paste
Application.CutCopyMode = False
'
' autofitPO Macro
'
'
Rows("2:21").Select
Selection.Rows.AutoFit
a = Worksheets("Data Pur").Cells(Rows.Count, 1).End(xlUp).Row
For i = 2 To a
If Worksheets("Data Pur").Cells(i, 7).Value > "0" Then
Worksheets("Data Pur").Rows(i).Copy
Worksheets("Master(Pur)").Activate
c = Worksheets("Master(Pur)").Cells(Rows.Count, 1).End(xlUp).Row
Worksheets("Master(Pur)").Cells(c + 1, 1).Select
ElseIf Worksheets("Data Pur").Cells(i, 6).Value > 2 Then
Worksheets("Data Pur").Rows(i).Copy
Worksheets("Fix Assets+Eva").Activate
d = Worksheets("Fix Assets+Eva)").Cells(Rows.Count, 1).End(xlUp).Row
Worksheets("Fix Assets+Eva").Cells(d + 1, 1).Select
ActiveCell.PasteSpecial Paste:=xlValues, operation:=xlPasteSpecialOperationNone
Worksheets("Data Pur").Activate
End If
Next
Application.CutCopyMode = False
ThisWorkbook.Worksheets("Data Pur").Cells(1, 1).Select
Worksheets("Data Pur").Range("A2:K24").SpecialCells(xlCellTypeConstants).ClearContents
Sheets("Master(Pur)").Protect
End Sub