I have a workbook (Req_PO_Log_V2.xls) that has all of the expenditures for our repair and maintenance funds from our current FY. The lay out is as follows:

| Date | Req Number | Acct | Vendor # | Vendor Name | Description | Cost |

The data I need to be able to split up into smaller worksheets is the Account (Acct) column.


The end result would be several worksheets with the entire rows of information pertaining to each individual acct name (R & M, Op Sup, Well R & M, etc.) that would update every time I enter in new information or via a button running the script. Preferably all in the same workbook.

I have tried other code that I have found on this site but get instances of multiple workbooks created with only 2 or three lines for each account (the spreadsheet has a few hundred lines) and then being asked if I want to over write the workbook that was created.

I have a mild knowledge of how VB works, enough to edit existing code but not to write my own. Any help would be greatly appreciated.

  • 1
    Why not just filter your data based on the values in column Acct? – DavidPostill Apr 2 '15 at 18:50
  • @DavidPostill has the simple answer, then if you need to, copy & paste – Raystafarian Apr 3 '15 at 13:56

The formula solution would be difficult to implement / debug, prone to errors, and greatly increase file size. The VBA procedure could be broken into steps:

  1. Delete all the sheets created last time the macro ran (this is easiest if the log you're showing is the only sheet in the workbook but you can adjust to anything)
  2. Get a unique list of the Acct names (this can be done by looping through all the used cells in the column and adding new items to a list or through more elegant means)
  3. Add a new worksheet for each Acct (Easy enough with Worksheets.Add(name))
  4. For each Acct name, filter the source sheet
  5. Copy all cells and paste into cell A1 in the respective worksheet.

I can expand on these sections if needed but I'd like to see you take a crack at them. It might be easier to find solutions to each piece on your own instead of the whole thing at once. Use the macro recorder or web searches to piece it together.

Take a crack at it and come back with smaller questions and evidence of what you have so far.

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.