• 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.
0
Jenedge73
Asked:
Jenedge73
  • 5
  • 4
1 Solution
 
SteveCommented:
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.
0
 
Jenedge73Author Commented:
It is a very simplistic example.  I hope this is enough.
Example.xlsx
0
 
SteveCommented:
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?
0
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.
0
 
SteveCommented:
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.
Example.xlsx
Example.docx
0
 
Jenedge73Author Commented:
Thanks.  I will have to try it.  will I save and print with the VBA
0
 
Jenedge73Author Commented:
Can you help me with the VBA?
0
 
SteveCommented:
I will do the vba too. Will be done some time today (providing nothing comes up).
Unless someone else does it :)
0
 
SteveCommented:
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)
Application.Calculate
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

Next

End Sub

Open in new window

SaveAsCopies.xlsm
0

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