Copy Paste Macro

[NPandelos]

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.

 

[NPandelos]

That work. Thank you.

So I didn't need to put in a while loop. I just needed to declare the stock code (SC) as a range, then swap out active cell w/ SC.

 

[mumps]

You are very welcome. I used the SC variable to loop through the range.

 

[NPandelos]

I have reworked the code you helped me with to put everything onto one sheet. Now in addition to the copy/paste I want to change the cell color (see image below). The cell fill only needs to change when "release" is in the one column. How would I do that? I've searched and everything I've found doesn't seem to work.

Sub Copy_Data()
    Application.ScreenUpdating = False
    Dim SC As Range
    For Each SC In Range("B5", Range("B" & Rows.Count).End(xlUp))
        If SC.Offset(, 2) = "SC - STOCK CODE" And SC.Offset(, 4) = "RELEASE" Then
            With Sheets("Stock Code Updates")
                .Cells(.Rows.Count, "B").End(xlUp).Offset(1).Resize(2) = SC
            End With
        ElseIf SC.Offset(, 2) = "SC - STOCK CODE" And SC.Offset(, 4) = "CHANGE" Then
            With Sheets("Stock Code Updates")
                .Cells(.Rows.Count, "B").End(xlUp).Offset(1).Resize(2) = SC
            End With
        End If
    Next SC
    Application.ScreenUpdating = True
    
    MsgBox ("The stock codes have been copied over to the Stock Code Update worksheet.")
    
End Sub

Proposed MDM.xlsm
	A	B	C	D
2		Stock Code	AbcAnalysisReq	AbcCostingReq
3	From	507610-00	Y	N
4	To	507610-00
5	From	TP.1234.567	?	?
6	To	TP.1234.567
7	From	507611-00	Y	N
8	To	507611-00
9	From	507612-00	Y	N
10	To	507612-00
11	From	507613-00	Y	N
12	To	507613-00
13	From	TP.5678.900	?	?
14	To	TP.5678.900
15	From	TP.6789.999	?	?
16	To	TP.6789.999
Stock Code Updates

 

[mumps]

In the picture you posted, there are three different colors. Also, there is no distinct pattern as to which rows receive which color. Could you clarify in detail which color you want to use and the criteria used to determine which rows receive that color.

 

[NPandelos]

Please ignore the last post. I ended up using conditional formatting to achieve what I wanted.