Please bare with me on this. I'm looking for some help with a copy paste macro I'm trying to write.
What I'm trying to do is look to see if there is a value in the "Stock Code" column. If there is look at the "Item Type & Action" columns depending on those values.
The "release" would have the cell cell value paste-text into the first blank cell in a different sheet.
If it is "change" the cell value would be paste-text into the first two blank cells in different sheet.
So far this is my code, but I am unsure what to put for the ranges.
Sub CopyStockCodeUpdates()
'
' CopyStockCodeUpdates Macro
Dim scNum, scCount As Integer
Dim scCurrent, sheetCurrent As String
Application.ScreenUpdating = False
Sheets("Engineering Input Form").Select
Range("B6").Select
'scCount = 0
scNum = 1
'Is there a stock code in the current cell? If so, continue.
While ActiveCell.Value <> ""
'Is this line a RELEASE? If so, copy active cell & paste text to "New Stock Code"
If Range("D" & (ActiveCell.Row)).Value = "SC - STOCK CODE" And Range("F" & (ActiveCell.Row)).Value = "RELEASE" Then
Selection.Copy
Sheets("New Stock Code").Select
Range ()
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
'Is this line a CHANGE? If so, copy active cell & paste text to "Stock Code Updates"
If Range("D" & (ActiveCell.Row)).Value = "SC - STOCK CODE" And Range("F" & (ActiveCell.Row)).Value = "RELEASE" Then
Selection.Copy
Sheets("Stock Code Updates").Select
Range()
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End If
End If
scNum = scNum + 1
Sheets("Worksheet").Select
ActiveCell.Offset(1, 0).Select
Wend
Application.ScreenUpdating = True
End Sub
Any help would be greatly appreciated.
What I'm trying to do is look to see if there is a value in the "Stock Code" column. If there is look at the "Item Type & Action" columns depending on those values.
The "release" would have the cell cell value paste-text into the first blank cell in a different sheet.
If it is "change" the cell value would be paste-text into the first two blank cells in different sheet.
So far this is my code, but I am unsure what to put for the ranges.
Sub CopyStockCodeUpdates()
'
' CopyStockCodeUpdates Macro
Dim scNum, scCount As Integer
Dim scCurrent, sheetCurrent As String
Application.ScreenUpdating = False
Sheets("Engineering Input Form").Select
Range("B6").Select
'scCount = 0
scNum = 1
'Is there a stock code in the current cell? If so, continue.
While ActiveCell.Value <> ""
'Is this line a RELEASE? If so, copy active cell & paste text to "New Stock Code"
If Range("D" & (ActiveCell.Row)).Value = "SC - STOCK CODE" And Range("F" & (ActiveCell.Row)).Value = "RELEASE" Then
Selection.Copy
Sheets("New Stock Code").Select
Range ()
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
'Is this line a CHANGE? If so, copy active cell & paste text to "Stock Code Updates"
If Range("D" & (ActiveCell.Row)).Value = "SC - STOCK CODE" And Range("F" & (ActiveCell.Row)).Value = "RELEASE" Then
Selection.Copy
Sheets("Stock Code Updates").Select
Range()
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End If
End If
scNum = scNum + 1
Sheets("Worksheet").Select
ActiveCell.Offset(1, 0).Select
Wend
Application.ScreenUpdating = True
End Sub
Any help would be greatly appreciated.