TechArena Community

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)

Bhadrak 06-05-2009 10:51 AM

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.

chroma 06-05-2009 12:37 PM

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


Bhadrak 06-05-2009 03:13 PM

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.

Raine 06-05-2009 03:13 PM

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


All times are GMT +5.5. The time now is 06:03 PM.