Code Repair - Copy Column to New Worksheet and Delet

Try changing from:

Target.Range(b23, b31)...

to

Target.Range("B23:B31")...

Actually, toward the end of that same line, I think it should be:

Destination:=Sheets(2).Cells(1, Columns.Count).End(xlToLeft).Offset(0, 1)

Looks like this code should run when you select two cells in row 31. It is then copying the cells one column over and 23 through 31 rows down, pasting it to the first open column in Sheet 2 row 1, and deleting what was copied on the original sheet. Is that what you intended? It may help to step through the code using F8. You can place a bookmark on any line of the code by clicking the left margin next to that line of code. A brown dot will appear and the line will be highlighted brown. Then the code will run until it gets to the bookmark, and it will stop. Then you can step through each line using F8.

Ok, I've made some changes. See the code below. I put the trigger to run the code for when a change is made in cell B33, which I assume would be the last cell to change when entering data. I also told it not to run when B33 does not have a value, otherwise it loops over itself whenever you delete the old values.

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Sheets(1).Range("B33")) Is Nothing Then
    If Sheets(1).Range("B33") <> "" Then
        Sheets(1).Range("B23:B33").Copy Destination:=Sheets(2).Cells(1, Columns.Count).End(xlToLeft).Offset(0, 1)
        Sheets(1).Range("B23:B33").Delete shift:=xlUp
    End If
End If

End Sub

I'd also like to encourage you to post a sample workbook right away with each new thread, as it makes it much easier to find code that will work for your purposes.

Let me know if you need any changes and can't figure out how to make them yourself.

Ok, try this:

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Sheets(1).Range("B33")) Is Nothing Then
    If Sheets(1).Range("B33") <> "" Then
        Sheets(1).Range("B25:B33").Copy Destination:=Sheets(2).Cells(1, Columns.Count).End(xlToLeft).Offset(0, 1)
        Sheets(2).Range(Sheets(2).Cells(1, Columns.Count).End(xlToLeft), Sheets(2).Cells(9, Columns.Count).End(xlToLeft)).Interior.ColorIndex = 0
        Sheets(1).Range("B25:B33").ClearContents
    End If
End If

End Sub

The reason you were having issues is because you used the Cells() property instead of the Range() property. I also changed it to grab the whole range of cells. You can just take individual cells, but for your purposes it is easier to just take the whole thing. I added a line to remove the shading, and cleared the cells instead of deleting them. This way you won't have to worry about any cells shifting.