Printing reports for multiple Business unit

Posted on 2012-08-14
Last Modified: 2012-08-29
I have a list of 200 Business Units #’s with descriptions on one sheet and on the other is the Income statement.  I Vlookup a BU number and replace the name at the top of the I/S with the corresponding description.  I want to cycle through all BU numbers printing and saving a report for each BU with the description on it.  In order to do this I have to find a vba the next number in the list in a particular cell, save the worksheet with the BU name and print a copy.   I’m really not good with VBA but I’m trying to learn.
Question by:Jenedge73
    LVL 24

    Expert Comment

    Is it possible to provide example.

    Off the top of my head it may be easier and provide a nicer appearance to use Word and MailMerge.

    Either way a sample file will help enormously.
    Dummy data is fine.

    Author Comment

    It is a very simplistic example.  I hope this is enough.
    LVL 24

    Expert Comment

    It is plenty, these Statements are for customers/Suppliers (i.e for outside the bussiness)?

    If this is an internal requirement then how they look is not  a "biggie"
    but if these go out from the bussiness I would use mailmerge based upon the excel file.
    You would be able to do exactly what you like with a greater ability for making a shiney output.

    Would you like me to demonstate the MailMerge method or is Excel & VBA sufficient?

    Author Comment

    If i could do the VBA it would be better.  I didn't know MailMerge would do that though.
    LVL 24

    Expert Comment

    I can happily do the VBA, a few others here are more than capable of that too.

    Attached is a very basic Word document and related Excel file do (hopefully) demonstrate the MailMerge.
    In essence create the sheet of BU data and then follow the mailmerge wizard to fill a Word Document.

    Author Comment

    Thanks.  I will have to try it.  will I save and print with the VBA

    Author Comment

    Can you help me with the VBA?
    LVL 24

    Expert Comment

    I will do the vba too. Will be done some time today (providing nothing comes up).
    Unless someone else does it :)
    LVL 24

    Accepted Solution

    Here is some code to do what you require (I hope)...
    Let me know if anything needs changing.
    See attached example return for demo.
    '''''''''''''--- LASTROW FUNCTION ---'''''''''''''
    Private Function LastRow(TheWorksheet As Worksheet) As Long
    If WorksheetFunction.CountA(TheWorksheet.Cells) > 0 Then
        LastRow = Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    End If
    End Function
    Sub CreateFilesAndPrint()
    Dim wb As Workbook
    Dim wsL As Worksheet
    Dim wsI As Worksheet
    Dim NewFolder As String
    Dim ShouldPrint As Integer
    Set wb = ThisWorkbook
    Set wsL = wb.Sheets("BU Listing")
    Set wsI = wb.Sheets("Income Statement")
    NewFolder = Format(Now(), "DD-MM-YYYY HHMM")
    On Error Resume Next
    MkDir wb.Path & "\" & NewFolder
    On Error GoTo 0
    ShouldPrint = MsgBox("Would you like to print the sheets?", vbYesNo, "Print sheets")
    For x = 2 To LastRow(wsL)
    wsI.Range("A1") = wsL.Cells(x, 1)
    If ShouldPrint = vbYes Then
    wsI.PrintOut Copies:=1, Collate:=True, IgnorePrintAreas:=False
    End If
    Filename = wb.Path & "\" & NewFolder & "\" & wsI.Range("A1").Value & " - " & wsI.Range("F5").Value & ".xlsm"
    wb.SaveCopyAs Filename:=Filename
    End Sub

    Open in new window


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
    INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
    The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
    This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

    760 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now