katiekatiesue
New Member
- Joined
- Aug 1, 2007
- Messages
- 3
Hello,
I am looking for help with a macro. Here's the basics (what I want it to do):
-The macro only works out of one workbook, with 3 worksheets, WIP_Watch Old, WIP_Watch New, and Stocked. Column headings for these worksheets include order number, part number, quantity, description, etc... but I'm only concerned with order number
-I want the macro to find and compare order numbers from WIP_Watch Old to WIP_Watch New. When a match is found, nothing needs to happen but when an order from WIP_Watch Old is NOT found on WIP_Watch New, then I want to copy the row containing the order number (columns A through W) from WIP_Watch Old.
-Next, I want to go to the worksheet called Stocked and insert a row at the top (under the column headings, so row 2), and paste the information copied from WIP_Watch Old. I then want to go to column W of this row and insert the date the copy is made.
-I need the above process to loop through the entire list of order numbers.
I have a macro started, but I've gotten stuck. Anyone think they can offer some help? The macro (called ToStock) is below.
Thanks.
Sub ToStock()
Dim Order As String 'Order Number
Dim OrderCol As Integer 'PO Column Number
Dim PrevRow As Integer 'Row number of the previous file
Dim CurrRow As Integer 'Row number of the current file
'Find Column locations in file
Cells(1, 1).Select
For i = 1 To 50
ColHeaders = Cells(1, i)
Select Case ColHeaders
Case "Order"
OrderCol = i
End Select
Next
CurrRow = 2
Do
Worksheets("WIP_Watch New").Activate
'Get the Order Number to compare to the previous WIP
Order = Cells(CurrRow, OrderCol)
PrevRow = 2
'This loop keeps going until the Order Number and Work Center are equal
Do
Worksheets("WIP_Watch Old").Activate
'Does the check to see if the Order Number matches. When a match is not found, the entire row (A-W) containing that order number is copied.
If Order <> Cells(PrevRow, OrderCol) Then
Worksheets("WIP_Watch Old").PrevRow.Copy
'Pastes Stocked Order Numbers to Stocked Worksheet
Worksheets("Stocked").Activate
ActiveCell.Offset(-20, 0).Rows("1:1").EntireRow.Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromRightOrBelow
ActiveCell.Range("A1:W1").Select
Application.CutCopyMode = False
Selection.Copy
ActiveCell.Offset(0, 22).Range("A1").Select
ActiveCell.FormulaR1C1 = "=TODAY()"
End If
'Increment the row
PrevRow = PrevRow + 1
Loop While Cells(PrevRow, OrderCol) <> ""
Worksheets("WIP_Watch New").Activate
'Increment the row number for the current file
CurrRow = CurrRow + 1
Loop While Cells(CurrRow, OrderCol) <> ""
End Sub
I am looking for help with a macro. Here's the basics (what I want it to do):
-The macro only works out of one workbook, with 3 worksheets, WIP_Watch Old, WIP_Watch New, and Stocked. Column headings for these worksheets include order number, part number, quantity, description, etc... but I'm only concerned with order number
-I want the macro to find and compare order numbers from WIP_Watch Old to WIP_Watch New. When a match is found, nothing needs to happen but when an order from WIP_Watch Old is NOT found on WIP_Watch New, then I want to copy the row containing the order number (columns A through W) from WIP_Watch Old.
-Next, I want to go to the worksheet called Stocked and insert a row at the top (under the column headings, so row 2), and paste the information copied from WIP_Watch Old. I then want to go to column W of this row and insert the date the copy is made.
-I need the above process to loop through the entire list of order numbers.
I have a macro started, but I've gotten stuck. Anyone think they can offer some help? The macro (called ToStock) is below.
Thanks.
Sub ToStock()
Dim Order As String 'Order Number
Dim OrderCol As Integer 'PO Column Number
Dim PrevRow As Integer 'Row number of the previous file
Dim CurrRow As Integer 'Row number of the current file
'Find Column locations in file
Cells(1, 1).Select
For i = 1 To 50
ColHeaders = Cells(1, i)
Select Case ColHeaders
Case "Order"
OrderCol = i
End Select
Next
CurrRow = 2
Do
Worksheets("WIP_Watch New").Activate
'Get the Order Number to compare to the previous WIP
Order = Cells(CurrRow, OrderCol)
PrevRow = 2
'This loop keeps going until the Order Number and Work Center are equal
Do
Worksheets("WIP_Watch Old").Activate
'Does the check to see if the Order Number matches. When a match is not found, the entire row (A-W) containing that order number is copied.
If Order <> Cells(PrevRow, OrderCol) Then
Worksheets("WIP_Watch Old").PrevRow.Copy
'Pastes Stocked Order Numbers to Stocked Worksheet
Worksheets("Stocked").Activate
ActiveCell.Offset(-20, 0).Rows("1:1").EntireRow.Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromRightOrBelow
ActiveCell.Range("A1:W1").Select
Application.CutCopyMode = False
Selection.Copy
ActiveCell.Offset(0, 22).Range("A1").Select
ActiveCell.FormulaR1C1 = "=TODAY()"
End If
'Increment the row
PrevRow = PrevRow + 1
Loop While Cells(PrevRow, OrderCol) <> ""
Worksheets("WIP_Watch New").Activate
'Increment the row number for the current file
CurrRow = CurrRow + 1
Loop While Cells(CurrRow, OrderCol) <> ""
End Sub