+ Reply to Thread
Results 1 to 5 of 5

VBA to Open Previous Month's Spreadsheet and Pull figures from table using Account, Month

  1. #1
    Registered User
    Join Date
    04-24-2008
    Posts
    85

    VBA to Open Previous Month's Spreadsheet and Pull figures from table using Account, Month

    Hello all,

    I'll be needing to write a little code but before I started, I was wondering if there were any examples already out there that I can borrow and tweak - I'm not the best at coding, but I can get by... code modification suits me much better than creation but I can create from scratch if needed... I just wanted to know if someone else has done the legwork already...

    Requirement:
    - code that will open a previous month's workbook, activate a specific worksheet, based on the month [row 10], and based on the account [column b], will need to bring in the appropriate amount and paste it into the current months spreadsheet. (My immediate thoughts were to use index & match but that's formula based, not sure if there's a better way)
    - then copy for the rest of the months and move onto the next account, and then onto the next worksheet. [I think I can do this last part without a problem]

    Background:
    - This is a forecasting spreadsheet and we're looking to bring in the previous months forecast into the current month's sheet for comparison... ie: go into feb forecast and see how much we thought we'd be spending in march, and for the training account, and for travel, etc...


    Any code snippets or samples that anyone has would be greatly appreciated.
    Thanks for your help!

  2. #2
    Administrator arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,147

    Re: VBA to Open Previous Month's Spreadsheet and Pull figures from table using Account, Mo

    Do you have any sample files that you can upload? It can contain dummy data, 1 for the previous month and 1 for the current.
    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]

  3. #3
    Registered User
    Join Date
    04-24-2008
    Posts
    85

    Re: VBA to Open Previous Month's Spreadsheet and Pull figures from table using Account, Mo

    I've attached an example - if you need more information please let me know.
    Thanks a lot for your help.
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    04-24-2008
    Posts
    85

    Re: VBA to Open Previous Month's Spreadsheet and Pull figures from table using Account, Mo

    bump no response

  5. #5
    Administrator arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,147

    Re: VBA to Open Previous Month's Spreadsheet and Pull figures from table using Account, Mo

    I understand your question. However, in one of my previous organizations, i had worked on a similar file. But the format we used was different. On 1 sheet itself, we had 3 columns per month. 1 for Forecast, 1 for Actual and then Variance (if required).

    Just review the attached and see if it suits you. Or else, we will revert to your version.
    Attached Files Attached Files

+ Reply to 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1