Solved Hi, I am trying to create and save reports based on drop dow

February 5, 2016 at 03:24:16
Specs: Windows 7
I have to create and save as reports to a path based on the drop down in a cell. For eg. I need to prepare the reports for all the Marketing Cost Centre. I will select marketing in sheet1 in Cell a2 and all the corresponding Cost Centers within Marketing will get showed as a drop down in cell a4. Now I want to run a macro to create reports based on the list in the cell d4. what i mean is suppose a4 includes 4 cost centers 600, 601, 602 & 603. As we start a macro cell a4 will show CC 600, file is refreshed and then paste value the file contents and save it at a path with the name provided in cell a6, then the cell will show CC 601, again file is refresed, paste value the contents and save it with the name based on cell value in a6 and so on

See More: Hi, I am trying to create and save reports based on drop dow

Report •

February 5, 2016 at 05:55:30
ok so, when you select your cost centre drom a4, how does the 'list' in d4 appear?

is it a list in the form of a dropdown menu?
is it a list in ther form of a column in which case it will span from d4 to dx?

also in what format do you want the report to be saved, as an excel file, text file etc?

Report •

February 7, 2016 at 23:11:21
Yes the list is in the form of a drop down menu
I want to save the file in xlsx format

Report •

February 8, 2016 at 04:10:48
✔ Best Answer
Ok here is some code for you to test

Open you Excel file
Press and hold Alt and press F11
From the project explorer on the left hand side select the sheet you are working with

paste the following

Dim objExcel As Object
Dim RowCounter As Integer

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$A$4" Then
    End If
End Sub

Sub SaveListItems()
    Dim inputRange As Range
    Dim c As Range
    Set inputRange = Evaluate(Range("D4").Validation.Formula1)

    Set objExcel = CreateObject("Excel.Application")
    Let objExcel.Visible = False
    RowCounter = 1
    For Each c In inputRange
        objExcel.Sheets("Sheet1").Range("A" & RowCounter).Value = c.Value
        RowCounter = RowCounter + 1
    Next c
    objExcel.ActiveWorkbook.SaveAs Filename:="C:\" & Range("A6").Value, FileFormat:=xlNormal
    Set objExcel = Nothing
End Sub

On my workbook i have the following setup

A4 has some cost centres in there
D4 will change accoring to the value selected in A4

The dropdown list in D4 will be saved in a new excel workbook and saved with the filename specified in A6.


The default path i have set for saving the workbook is 'C:\' please change this to where ever you wish to save.

Report •

Related Solutions

February 10, 2016 at 05:53:23
Hi Puja1977

Did this solution work for you?

if not let is know what changes are needed

Report •

Ask Question