• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 285
  • Last Modified:

Printing reports for multiple Business unit

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.
  • 5
  • 4
1 Solution
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.
Jenedge73Author Commented:
It is a very simplistic example.  I hope this is enough.
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?
2018 Annual Membership Survey

Here at Experts Exchange, we strive to give members the best experience. Help us improve the site by taking this survey today! (Bonus: Be entered to win a great tech prize for participating!)

Jenedge73Author Commented:
If i could do the VBA it would be better.  I didn't know MailMerge would do that though.
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.
Jenedge73Author Commented:
Thanks.  I will have to try it.  will I save and print with the VBA
Jenedge73Author Commented:
Can you help me with the VBA?
I will do the vba too. Will be done some time today (providing nothing comes up).
Unless someone else does it :)
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


Featured Post

Learn to develop an Android App

Want to increase your earning potential in 2018? Pad your resume with app building experience. Learn how with this hands-on course.

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now