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]

That means that it is not finding the word Sold in row 1 of the sheet that is active. Is Sheets("Count sheet") the Active sheet when you run the code?

No the inventory management is

 

[MARK858]

Then you need to append the sheet name in the code

mycol = Sheets("Count sheet").Rows(1).Find("Sold", , xlValues, , xlByColumns, xlPrevious).Column

 

[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).Insert Shift:=xlToRight, Copyorigin:=xlFormatFromLeftOrAbove
   
    Sheets("Inventory management").Range("E2").ClearContents

End Sub

if i want to add the insert column to the same location can i do that in the same line because the one i have in this code is not working it is putting the column in E2 of count sheet

 

[MARK858]

I can't see how it can be putting it in Column E, If you said Column M then that makes sense. If you run the code below what does the msgbox say?

Sub Sold2()
    Dim cell As Range, RngB
    Dim mycol As Long
    
    mycol = Sheets("Count sheet").Rows(1).Find("Sold", , xlValues, , xlByColumns, xlPrevious).Column

    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

   MsgBox mycol
    
    Sheets("Count sheet").Columns(mycol).Insert Shift:=xlToRight, Copyorigin:=xlFormatFromLeftOrAbove
   
    Sheets("Inventory management").Range("E2").ClearContents

End Sub

 

[svis89]

I can't see how it can be putting it in Column E, If you said Column M then that makes sense. If you run the code below what does the msgbox say?

Sub Sold2()
    Dim cell As Range, RngB
    Dim mycol As Long
   
    mycol = Sheets("Count sheet").Rows(1).Find("Sold", , xlValues, , xlByColumns, xlPrevious).Column

    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

   MsgBox mycol
   
    Sheets("Count sheet").Columns(mycol).Insert Shift:=xlToRight, Copyorigin:=xlFormatFromLeftOrAbove
  
    Sheets("Inventory management").Range("E2").ClearContents

End Sub

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).Insert Shift:=xlToRight, Copyorigin:=xlFormatFromLeftOrAbove
   
    Sheets("Inventory management").Range("E2").ClearContents

End Sub

 

[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).Insert Shift:=xlToRight, Copyorigin:=xlFormatFromLeftOrAbove
  
    Sheets("Inventory management").Range("E2").ClearContents

End Sub

now it is not inserting rows at all

 

[MARK858]

So what did the Msgbox say when you ran the code I posted (please answer the questions I asked or we we will here forever)

 

[svis89]

So what did the Msgbox say when you ran the code I posted (please answer the questions I asked or we we will here forever)

i thought i did sorry 18, 19, 20 i ran it three times

 

[svis89]

i thought i did sorry 18, 19, 20 i ran it three times

I solved it i forgot the +2 offset on the insert row

 

[svis89]

Thank you so much for Helping me so much I think I vastly overestimated my abilities in VBA based on something I learned on a whim 14 years ago