+ Reply to Thread
Results 1 to 7 of 7

Macro to copy row, insert below, invert amt, and insert blank row between each new tx

  1. 03-04-2013, 03:14 PM #1
    raquel1227
    raquel1227 is offline
    Registered User
    Join Date
    03-04-2013
    Location
    Kissimmee
    MS-Off Ver
    Excel 2010
    Posts
    3

    Macro to copy row, insert below, invert amt, and insert blank row between each new tx

    I'm trying to convert a downloaded csv file to an iif for Quickbooks. I need a macro that will copy the entire row of a transaction, invert the amount, and insert the copied row right below. Then, insert a blank row to separate each transaction.

    I'd be going from this:


    Posted Date Payee Account Amount
    1/10/2013 X CC Acct -16.11
    1/10/2013 Y CC Acct -35
    1/10/2013 Z CC Acct -1


    to this:

    Posted Date Payee Account Amount
    1/10/2013 X CC Acct -16.11
    1/10/2013 X Exp Acct 16.11

    1/10/2013 Y CC Acct -35
    1/10/2013 Y Exp Acct 35

    1/10/2013 Z CC Acct -1
    1/10/2013 Z Exp Acct 1

    I can manually change the expense account once formatted.

    I'm very new to VBA so any help would be greatly appreciated.

    Many thanks,

    Raquel
    Reply With QuoteRegister To Reply
  2. 03-04-2013, 03:57 PM #2
    raquel1227
    raquel1227 is offline
    Registered User
    Join Date
    03-04-2013
    Location
    Kissimmee
    MS-Off Ver
    Excel 2010
    Posts
    3

    Re: Macro to copy row, insert below, invert amt, and insert blank row between each new tx

    Currently I'm using the following to insert two blank rows and manually copying and pasting information.

    Please Login or Register  to view this content.
    Last edited by arlu1201; 03-05-2013 at 01:53 AM.
    Reply With QuoteRegister To Reply
  3. 03-05-2013, 01:53 AM #3
    arlu1201
    arlu1201 is offline
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Macro to copy row, insert below, invert amt, and insert blank row between each new tx

    Welcome to the forum.

    I have added code tags to your post. As per forum rule 3, you need to use them whenever you put any code in your post. Please add them in future. If you need more information on how to use them, check my signature below this post.
    If I have helped, Don't forget to add to my reputation (click on the star below the post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
    Use code tags when posting your VBA code: [code] Your code here [/code]
    Reply With QuoteRegister To Reply
  4. 03-05-2013, 02:02 AM #4
    arlu1201
    arlu1201 is offline
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Macro to copy row, insert below, invert amt, and insert blank row between each new tx

    Try this code
    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  5. 03-05-2013, 10:55 AM #5
    raquel1227
    raquel1227 is offline
    Registered User
    Join Date
    03-04-2013
    Location
    Kissimmee
    MS-Off Ver
    Excel 2010
    Posts
    3

    Re: Macro to copy row, insert below, invert amt, and insert blank row between each new tx

    Thank you, Arlu1201.

    Your code worked like a charm!

    Many thanks,

    Raquel
    Reply With QuoteRegister To Reply
  6. 03-05-2013, 10:59 AM #6
    arlu1201
    arlu1201 is offline
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Macro to copy row, insert below, invert amt, and insert blank row between each new tx

    Am glad it worked.
    Reply With QuoteRegister To Reply
  7. 03-07-2013, 12:56 PM #7
    sam sam
    sam sam is offline
    Registered User
    Join Date
    04-04-2012
    Location
    London
    MS-Off Ver
    Excel 2016
    Posts
    13

    Macro to Insert Line

    Hi,
    Please need help on macro,
    I want to insert a line after every change in order number.
    macro should insert the blank line and should copy the same order number and in column STOCK_CODE it should type "DELIVERY"

    Thanks in advance four your kind help.


    ORDER_DATE ACCOUNT_REF ORDER_TYPE ORDER_REF STOCK_CODE qty_order
    02/02/2013 ZENMART SO 101556 R922003-AZ 1
    02/02/2013 ZENMART SO 101557 I200054-AZ 2
    02/02/2013 ZENMART SO 101557 E300012-AZ 1
    02/02/2013 ZENMART SO 101558 C111317-AZ 1
    02/02/2013 ZENMART SO 101558 J130138-AZ 1 BEFORE
    02/02/2013 ZENMART SO 101558 C110113-AZ 1
    02/02/2013 ZENMART SO 101559 P800113-AZ 1
    02/02/2013 ZENMART SO 101560 R922000-AZ 1
    03/02/2013 ZENMART SO 101561 C111330-AZ 2

    ORDER_DATE ACCOUNT_REF ORDER_TYPE ORDER_REF STOCK_CODE qty_order
    02/02/2013 ZENMART SO 101556 R922003-AZ 1
    02/02/2013 ZENMART SO 101556 DELIVERY 1
    02/02/2013 ZENMART SO 101557 I200054-AZ 2
    02/02/2013 ZENMART SO 101557 E300012-AZ 1
    02/02/2013 ZENMART SO 101557 DELIVERY 1
    02/02/2013 ZENMART SO 101558 C111317-AZ 1
    02/02/2013 ZENMART SO 101558 J130138-AZ 1
    02/02/2013 ZENMART SO 101558 C110113-AZ 1 AFTER
    02/02/2013 ZENMART SO 101558 DELIVERY 1
    02/02/2013 ZENMART SO 101559 P800113-AZ 1
    02/02/2013 ZENMART SO 101559 DELIVERY 1
    02/02/2013 ZENMART SO 101560 R922000-AZ 1
    02/02/2013 ZENMART SO 101560 DELIVERY 1
    03/02/2013 ZENMART SO 101561 C111330-AZ 2
    03/02/2013 ZENMART SO 101561 DELIVERY 1
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1