I.E A2=D2 on another page i need my macro to go from A2 to J column

[svis89]

Sub Bought_1()
Range("D2").Activate
If ActiveCell = vbNullString Then Exit Sub
Selection.Copy
RngB = Range("B2").Value

Sheets("Count sheet").Activate
For Each cell In Range("A2:A23")
If cell.Value = RngB Then
pp = pp + 1
End If
Next

If pp = 1 Then
Sheets("Inventory management").Activate
Range("D2").Activate
Selection.Copy
Sheets("Count sheet").Activate
Range("J2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
Sheets("Inventory management").Activate
Application.CutCopyMode = False
Range("A2").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Count sheet").Select
    Range("J1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Columns("J:J").Select
    Application.CutCopyMode = False
    Selection.Insert Shift:=xlToRight, Copyorigin:=xlFormatFromLeftOrAbove
    Range("J2").Select
    Sheets("Inventory management").Select
    Range("D2").Select
    Selection.ClearContents
Else
MsgBox "Nothing"

End If

End Sub

So if A2=RngB then go to J2
if A2:A23= RngB it needs to go to the J column for that row
i was thinking once it identifies the RngB to offset to the J but i could not think of how it could go back to the "Inventory management" copy D4 and then return to the correct cell

 

[MARK858]

Happy you got there (it still wouldn't have been inserting in Column E but NVM). Is that it all solved now?

 

[svis89]

Happy you got there (it still wouldn't have been inserting in Column E but NVM). Is that it all solved now?

Yes the E column was because of a formula I think (didnt think it would check formula's) changed it and added the offset and that fixed it all

 

[MARK858]

Good to hear, thanks for the feedback

 

[svis89]

Sub Sold2()
    Dim cell As Range, RngB
    Dim mycol As Long, CngB As Range
    
    mycol = Sheets("Count sheet").Rows(1).Find("Sold", , xlValues, , xlByColumns, xlPrevious).Column
    Set CngB = Columns(mycol)
        
    RngB = Sheets("Inventory management").Range("B2").Value


    For Each cell In Sheets("Count sheet").Range("A2:A23")
        If cell.Value = RngB Then Sheets("Count sheet").Cells(cell.Row, mycol).Offset(, 2) = Sheets("Inventory management").Range("E2")
    Next
    
    Sheets("Count sheet").Columns(mycol + 2).Insert Shift:=xlToRight, Copyorigin:=xlFormatFromLeftOrAbove
   
    Sheets("Inventory management").Range("E2").ClearContents
    Sheets("Inventory management").Range("B2:C2").ClearContents

End Sub

Final code in case someone in the future can use it to help solve their problems

 

[MARK858]

You can remove the bits in red as they are obsolete now (don't miss removing the comma in front of CngB As Range)

Sub Sold2()
    Dim cell As Range, RngB
    Dim mycol As Long, CngB As Range
    
    mycol = Sheets("Count sheet").Rows(1).Find("Sold", , xlValues, , xlByColumns, xlPrevious).Column
    Set CngB = Columns(mycol)
        
    RngB = Sheets("Inventory management").Range("B2").Value


    For Each cell In Sheets("Count sheet").Range("A2:A23")
        If cell.Value = RngB Then Sheets("Count sheet").Cells(cell.Row, mycol).Offset(, 2) = Sheets("Inventory management").Range("E2")
    Next
    
    Sheets("Count sheet").Columns(mycol + 2).Insert Shift:=xlToRight, Copyorigin:=xlFormatFromLeftOrAbove
   
    Sheets("Inventory management").Range("E2").ClearContents
    Sheets("Inventory management").Range("B2:C2").ClearContents

End Sub