Exceltips.nl

When you are doing a lot of reporting, often you have the same report for several
products, LOB’s, Business Units etc.

Now most of the time these reports exist and are maintained separate, which
is a nightmare ofcourse. Much easier would be to build one report, and use a macro
to create separate output files.

This macro does that for you. What do you need:

1. A report that changes data by changing a cell value.

In this example cell A1 on the report in sheet1 is the trigger for the data in the report
(so when A1 is “LOB1” the report shows LOB1 data, change it to “LOB2” your report
shows LOB2 data..)

2. A List with possible values for your report

Make a list with LOB1,LOB2,LOB3,LOB4 etc on Sheet2, and name the first value
of this list ReportStart.

This macro will start at the first value of your list, and work it’s way down untill
it encounters a blank cell (end of your list)

Sub CREATE_XLS_FILES()

'============================================================
'SOME SETTINGS
'============================================================

    Dim ReportStart As String
    Dim TargetWB As Workbook
    
    Set TargetWB = ActiveWorkbook

    Application.ScreenUpdating = False
    Application.DisplayAlerts = False

    Application.GoTo Reference:="ReportStart"
    Row = ActiveCell.Row
      
'============================================================
'WORK THROUGH ALL LOB's
'============================================================
    
    Sheets("Sheet1").Select
        
    While ActiveSheet.Cells(Row, 1).Value <> ""
        Title = ActiveSheet.Cells(Row, 1).Value

	'Change the trigger of the report, so all data is for the right LOB        
        Sheets("Sheet1").Range("A1").Value = Title

	'Move the report to a new workbook
        Sheets("Sheet1").Select
        Sheets("Sheet1").Activate
        Sheets("Sheet1").Copy
        
	'Copy / Paste values to remove any annoying links
        Cells.Select
        Selection.Copy
        Selection.PasteSpecial Paste:=xlPasteValues
        Range("A1").Select
    
	'save the new file. This will output as O:\TEMP\Report for LOB1.xls etc
        ActiveWorkbook.SaveAs Filename:= _
        "O:\TEMP\Report for " & Title & ".xls", _
        FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
        ReadOnlyRecommended:=False, CreateBackup:=False
        
	'close the new workbook        
        ActiveWorkbook.Close
        
	'return to the original workbook
        TargetWB.Activate
        
        Sheets("Sheet1").Select
       
	'and repeat it all for the next LOB in your list
        Row = Row + 1
        Wend      
   
    Application.ScreenUpdating = True
    Application.DisplayAlerts = False
    
End Sub