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