Code Repair - Copy Column to New Worksheet and Delet

**RobGair** · 08-09-2010, 03:25 PM

I am trying to use some code to copy a column range that is in WKSHT 1 to WKSHT 2, using the trigger of the last value input - then delete the data in WKSHT 1 so that the next set of values can be input.

The values posted to WKSHT 2 will be in column 2, and then the next set of values in column 3, and then 4 and so on.

Here is the code that I am trying to use:

Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Row = 31 And Target.Cells.Count = 2 Then
    Target.Range(b23, b31).Copy Destination:=Sheets(2).Cells(1, Column.Count).End(xlUp).Offset(0, 1)
    Target.Range(b23, b31).Delete shift:=xlUp
  End If
End Sub

I copied this from a similar program which did the same function for "rows", but copied the entire row instead of a range. When I run it I get an error '424' in the instruction starting with Target.R...... and ending ....Offset(0,1)

**davegugg** · 08-09-2010, 05:59 PM

Try changing from:

Target.Range(b23, b31)...

to

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

**RobGair** · 08-09-2010, 07:16 PM

I did this and still there was no change - except now I don't receive an error.

That being said the instructions of the program to copy and delete don't happen.

I have compiled the new code, enabled macros and vb object - and run a debug

Am I missing anything?

**davegugg** · 08-10-2010, 10:11 AM

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.

**RobGair** · 08-10-2010, 11:14 AM

davegugg

you've been really helpful - and thanks for that

i am a relative newbie to VB and for some reasone I can't get this code to do exactly what u outlined in your text to me. i believe that it is probably something to do with the trigger - which is the filling in of the a dollar amount in line 31 column 2

i stepped through the code but still need to figure out how this works on the debugging side

it is important that i get this done today - any chance of you looking at the file and giving me a hand? i have attached it for reference

thanks

**davegugg** · 08-10-2010, 11:26 AM

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.

**RobGair** · 08-10-2010, 10:10 PM

dave

thanks once again

things worked a treat until i started to muck with it

i wanted the input to be more functional so i moved things around, and added shading to cells

i have attached the workbook for you to look at

two problems:

1. it no longer deletes the cells
2. when it copies it copies the cell shading - which isn't a problem
3. the bigger problem is that when it deletes (i did manage to get it to delete once) it deletes all the shading as well = and now that I have formulae in lines below
4. if i get what is happening it is creating the loop by moving the cells from below up?

i have sent you the new file to see if there is a solution to this. if not i'll go back to the simple spreadsheet with no colours. this wasn't intended to be a lenghty piece of work, but i would like to make it easy for those filling in the data

**davegugg** · 08-11-2010, 11:02 AM

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.