# Macro auto transfer data to different sheet based on condition



## DoneDidIt (Dec 18, 2008)

I'd like the macro to perform an automatic transfer of multiple cell data to a different multiple cells in a different sheet as new info is entered daily. If in Sheet 'JAN' C32:C90 is equal to "Cash Transaction," transfer the cell data from Sheet 'JAN' (for example) A40, B40, E40, and F40 over to Sheet 'Cash Acct' A9, B9, C9, D9. I need data transferred to the next blank/available row in Sheet 'Cash Account'. The available lines in Sheet 'Cash Acct' begin at line 9 and end at line 145.

Why won't this code work when I paste it into the 'JAN' VB Edit Window?

Private Sub Worksheet_Change(ByVal Target As Range)
Dim LastRow As Integer
For Each Cell In Sheets("JAN").Range("C32:C90")
If Cell.Value <> "Cash Transaction" Then Exit Sub
Next Cell
LastRow = Sheets("Cash Acct") _
.Range("A145").End(xlUp).Row + 1
If LastRow < 9 Then LastRow = 9
If LastRow > 145 Then
x = MsgBox("Can't copy beyond Row 145.")
Exit Sub
End If
Sheets("Cash Acct").Cells(LastRow, 1).Value _
= Sheets("JAN").Range("A9").Value
Sheets("Cash Acct").Cells(LastRow, 2).Value _
= Sheets("JAN").Range("B9").Value
Sheets("Cash Acct").Cells(LastRow, 3).Value _
= Sheets("JAN").Range("E9").Value
Sheets("Cash Acct").Cells(LastRow, 4).Value _
= Sheets("JAN").Range("F9").Value
End Sub


----------



## Jimmy the Hand (Jul 28, 2006)

Hi and welcome to TSG Forums!

What do you mean by "won't work"? Not doing anything at all? Or doing something but not what is expected?
Anyway, my suggestions to you are:

1. Run this code:

```
Sub test()
    MsgBox Application.EnableEvents
End Sub
```
If it returns False, then all Event Handler subroutines are disabled, so the code you wrote is not executed at all. Default value of EnableEvents is True, and it should reset at each restart of Excel.

2. Place a breakpoint at this code line:

```
For Each Cell In Sheets("JAN").Range("C32:C90")
```
Placing a breakpoint: set the cursor on the desired lint then press F9.
When the code is executed for the next time, it will stop at the breakpoint. Hit F8 repeatedly to maintain a step-by-step code execution. This way you'll see what is happening really.

3. Declare all your variables.

```
Dim LastRow As Long, Cell As Range, x As Long
```
4. Variable "x" is, actually, not needed.

```
use
MsgBox "Can't copy beyond Row 145."
instead of
x = MsgBox("Can't copy beyond Row 145.")
```
A simple rule which might have confused you: When calling a function as if it was a procedure, you don't use parentheses. E.g.
myFunction Argument1, Argument2, Argument3
vs.
Variable = myFunction(Argument1, Argument2, Argument3)

5. In the code module of Sheet 'JAN' you don't need to write it out explicitly, you can refer to it a Me, or leave reference altogether. E.g.

```
Sheets("JAN").Range("A9").Value
is equivalent with 
Me.Range("A9").Value
or
Range("A9").Value
```
6. Continuous range of cells can be copied simply in one step.

```
Sheets("JAN").Range("A9:F9").Copy
Sheets("Cash Acct").Cells(LastRow, 1).PasteSpecial xlPasteValues
Application.CutCopyMode = False
```
HTH

Jimmy


----------



## DoneDidIt (Dec 18, 2008)

Thanks Jimmy!


----------

