+ Reply to Thread
Results 1 to 12 of 12

VBA code for raw data + account code + dept id

  1. 06-03-2010, 11:36 AM #1
    mingali
    mingali is offline
    Forum Contributor
    Join Date
    07-24-2009
    Location
    Singapore
    MS-Off Ver
    Excel 2010 (office) 2013 (home)
    Posts
    256

    VBA code for raw data + account code + dept id

    Hi,

    Would some experts here be able to provide me with a VBA code based on the data from attached file?

    Basically, wks 'raw data' is extracted from our accounting system. i need to pull info together based on dept id (col c) for each worksheet. and the name of each worksheet is to be the same as the dept id.

    wks 'dept id' need to be shown across the workbook also regardless whether it has transactions shown in wks 'raw data'. it is to ensure all dept id are captured in the workbook.

    In addition, for each worksheet data, i need to ensure that all account code (as shown in wks 'account code') will be shown in each wks regardless whether the account code is shown in wks 'raw data'.

    Thanks a lot in advance.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 06-03-2010, 12:30 PM #2
    jaslake
    jaslake is offline
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: VBA code for raw data + account code + dept id

    Hi mingali

    To make sure I understand, you want a new workbook that has a worksheet for each department (a total of 90 worksheets). Is this the case?

    John
    John

    If you have issues with Code I've provided, I appreciate your feedback.

    In the event Code provided resolves your issue, please mark your Thread as SOLVED.

    If you're satisfied by any members response to your issue please use the star icon at the lower left of their post.
    Reply With QuoteRegister To Reply
  3. 06-03-2010, 12:33 PM #3
    mingali
    mingali is offline
    Forum Contributor
    Join Date
    07-24-2009
    Location
    Singapore
    MS-Off Ver
    Excel 2010 (office) 2013 (home)
    Posts
    256

    Re: VBA code for raw data + account code + dept id

    Hi John,

    Yes, it is correct. because i have another file that using vlookup to link the data. If there is no worksheet for one particular dept id, then #ref# will show in another file.

    Thanks alot for your assistance. Really appreciate it.
    Reply With QuoteRegister To Reply
  4. 06-03-2010, 12:44 PM #4
    jaslake
    jaslake is offline
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: VBA code for raw data + account code + dept id

    Hi mingali

    In looking at your data, the Account Code ws and the Dept ID ws both contain duplicate items. Should the duplicates be filtered out? You can't have three worksheets called "1904".

    John
    Reply With QuoteRegister To Reply
  5. 06-03-2010, 12:48 PM #5
    mingali
    mingali is offline
    Forum Contributor
    Join Date
    07-24-2009
    Location
    Singapore
    MS-Off Ver
    Excel 2010 (office) 2013 (home)
    Posts
    256

    Re: VBA code for raw data + account code + dept id

    Hi John,

    Yes please, it can be filtered out.

    Thanks
    Reply With QuoteRegister To Reply
  6. 06-03-2010, 03:24 PM #6
    jaslake
    jaslake is offline
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: VBA code for raw data + account code + dept id

    Hi mingali

    This'll take a little time. I'll get back to you.

    John
    Reply With QuoteRegister To Reply
  7. 06-03-2010, 09:42 PM #7
    jaslake
    jaslake is offline
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: VBA code for raw data + account code + dept id

    Hi mingali

    Have the issue mostly solved. I've got my grandson tomorrow so I'll not have a lot of time to devote to the issue but should have something for you to look at either tomorrow or early Saturday.

    John
    Reply With QuoteRegister To Reply
  8. 06-05-2010, 02:07 PM #8
    jaslake
    jaslake is offline
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: VBA code for raw data + account code + dept id

    Hi mingali

    Attached is a file named TestExtraction(1).xls. Place this workbook and your data file to be extracted (Extraction(1).xls) into the same folder.

    Run the procedure from the button in TestExtraction(1). The procedure will prompt you for your source file Extraction(1) and open it. The procedure copies your raw data sheets to TestExtraction(1) and processes the data.

    Shortly into the procedure, you'll get a screen that looks like the attached screen image.jpg. This is coming from your side and I don't know how to get rid of it. Simply cancel the screen and the procedure continues.

    The file is saved at the end of the procedure to a file called Output.

    I haven't done any sorting or sizing of columns, etc in Output and haven't filled in any blank cells with 0.00 .

    I've run this several times and it appears to work. Let me know how this works for you and we can clean it up.

    John
    Attached Images Attached Images
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  9. 06-05-2010, 02:32 PM #9
    jaslake
    jaslake is offline
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: VBA code for raw data + account code + dept id

    Hi mingali

    I discovered that there's a link in Extraction(1). If you break that link, the screen image I attached no longer appears.

    John
    Reply With QuoteRegister To Reply
  10. 06-06-2010, 12:04 PM #10
    mingali
    mingali is offline
    Forum Contributor
    Join Date
    07-24-2009
    Location
    Singapore
    MS-Off Ver
    Excel 2010 (office) 2013 (home)
    Posts
    256

    Re: VBA code for raw data + account code + dept id

    Hi John

    Thanks for your input. It is brilliant!

    Currently i am rushing for a deadline job and will look into it in details next week as there is some changes required and may need your further assistance.

    Thanks again for your spending time to work out my requirement. I really appreciate it.

    Mingali
    Reply With QuoteRegister To Reply
  11. 06-06-2010, 12:19 PM #11
    jaslake
    jaslake is offline
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: VBA code for raw data + account code + dept id

    Hi mingali

    Glad you're pleased. I assumed there would be some cleaning up to do. Let me know when you're ready.

    John
    Reply With QuoteRegister To Reply
  12. 06-06-2010, 07:29 PM #12
    jaslake
    jaslake is offline
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: VBA code for raw data + account code + dept id

    Hi mingali

    I've been playing around with your project trying to anticipate what needs to be done and found that the worksheet "Account code" is not complete. There are account code records in worksheet "Raw Data" that are not in worksheet "Account codes". These records

    665096 DCR-Utility Recharge
    670001 Cost Allocation Offset-Dept
    670110 PPC-Others

    appear in "Raw Data" and not in "Account codes". Is "Account code" a master list? Is it complete?

    What about worksheet "Dept ID"? Is it a master list? Is it complete?

    Just trying to anticipate problems.

    John
    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