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

 

[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

[/CODESub 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 .Select
    Offset(0,8)
    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

[CODE=vba]

so thats what i have been trying to get to work so far but i'm not sure about how to set the selection from above the offset so i dont have anything to use the offset on

 

[MARK858]

i could not think of how it could go back to the "Inventory management" copy D4 and then return to the correct cell

You do it by not activating the sheets/cells, as a basic example (and assuming that RngB is on Sheets("Count sheet"))...

Sub Bought_1test()
    Dim cell As Range, RngB

    RngB = Sheets("Inventory management").Range("B2").Value


    For Each cell In Sheets("Count sheet").Range("A2:A23")
    
        If cell.Value = RngB Then cell.Offset(, 9).Value = _
                Sheets("Inventory management").Range("D2").Value
  
    Next
    
    Sheets("Count sheet").Columns("J:J").Insert Shift:=xlToRight, Copyorigin:=xlFormatFromLeftOrAbove
   
    Sheets("Inventory management").Range("D2").ClearContents

End Sub

 

[svis89]

You do it by not activating the sheets/cells, as a basic example (and assuming that RngB is on Sheets("Count sheet"))...

Sub Bought_1test()
    Dim cell As Range, RngB

    RngB = Sheets("Inventory management").Range("B2").Value


    For Each cell In Sheets("Count sheet").Range("A2:A23")
   
        If cell.Value = RngB Then cell.Offset(, 9).Value = _
                Sheets("Inventory management").Range("D2").Value
 
    Next
   
    Sheets("Count sheet").Columns("J:J").Insert Shift:=xlToRight, Copyorigin:=xlFormatFromLeftOrAbove
  
    Sheets("Inventory management").Range("D2").ClearContents

End Sub

Thank you so much i did not understand how to do this part at all and i love how much you cleaned up the code
If cell.Value = RngB Then cell.Offset(, 9).Value = _
Sheets("Inventory management").Range("D2").Value

 

[MARK858]

You're welcome

 

[svis89]

So i ran into a slight problem i was going to simply implement this into each macro im running for bought, sold and defects on this inventory screen put each time it is adding a column which it needs to, So can i implement a search function like i did before to find RngB to find the correct column so im going from inventory management to the count sheet...i.e. RngB find the row and CngB finds column

IF i need to start a new thread let me know and i will

 

[MARK858]

Define exactly what you would do to find CngB manually please.
Can you also put up a screenshot of your spreadsheet using the boards XL2BB addin rather than an image so we can copy it into Excel.

 

[svis89]

Define exactly what you would do to find CngB manually please

i would check row one to find Bought, Sold, Defect then offset two columns to the right and down till i found the Row that RngB is on

 

[svis89]

i would check row one to find Bought, Sold, Defect then offset two columns to the right and down till i found the Row that RngB is on

i can copy the Bought, Sold, Defect down the entire column so that it can offset RngB until it finds Bought, Sold, Defect then Offset to the right twice if that would be easier

 

[MARK858]

Which of Bought, Sold, Defect is the column for CngB? also is it the first, last or other instance of the word and also...
Can you also put up a screenshot of your spreadsheet using the boards XL2BB addin rather than an image so we can copy it into Excel please