TechArena Community - How to create a macro in Excel

TechArena Community (http://forums.techarena.in/)

- Windows Software (http://forums.techarena.in/windows-software/)

- - How to create a macro in Excel (http://forums.techarena.in/windows-software/1174617.htm)

How to create a macro in Excel

Hi friends,

I need to create a macro which performs an automatic transfer of multiple data in a different sheet.Following are the details of which i need to create a macro.
If in Sheet 'JAN' C32:C90 is equal to "Cash Details" 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.

Can anyone help me out with this issue.

Re: How to create a macro in Excel

Sure i will help you out try to copy paste the following code in excel.

Code:

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 Details" 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

Re: How to create a macro in Excel

Thanks for replying me i try to use the above code and run macro but it's not working for me.

Re: How to create a macro in Excel

I have found a solution for you try to cut the code from Worksheet_Change subroutine and then delete the Worksheet_Change sub.Now create a new code and add the code of your and also add the following code too.

Code:

If Target.Column = 3 And Target.Row > 31 And Target.Row < 91 Then