Go Back   TechArena Community > Software > Windows Software
Become a Member!
Forgot your username/password?
Tags Active Topics RSS Search Mark Forums Read

Sponsored Links



How to create a macro in Excel

Windows Software


Reply
 
Thread Tools Search this Thread
  #1  
Old 06-05-2009
Members
 
Join Date: Aug 2004
Location: Goa
Posts: 38
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.

Reply With Quote
  #2  
Old 06-05-2009
Member
 
Join Date: Apr 2008
Posts: 2,139
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
Reply With Quote
  #3  
Old 06-05-2009
Members
 
Join Date: Aug 2004
Location: Goa
Posts: 38
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.
Reply With Quote
  #4  
Old 06-05-2009
Member
 
Join Date: May 2008
Posts: 3,313
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
Reply With Quote
Reply

  TechArena Community > Software > Windows Software
Tags: ,



Thread Tools Search this Thread
Search this Thread:

Advanced Search


Similar Threads for: "How to create a macro in Excel"
Thread Thread Starter Forum Replies Last Post
Excel 2003 Macro doesn't work in Excel 2007 jjaw Windows Software 3 03-01-2014 03:28 PM
How to use Excel Macro aSITA Windows Software 5 12-02-2011 10:34 PM
What is a Macro that is used in MS Excel? Common Windows Software 5 19-03-2010 02:08 AM
How to Create Macro in VBA program? Bigga Lexx Software Development 5 23-01-2010 06:22 PM
Procedure to create Auto-Run Macro Bottlenecked Windows Software 5 07-01-2010 02:01 PM


All times are GMT +5.5. The time now is 03:04 AM.