Macro that loops through drop down and creates a worksheet for each drop down selection

Question

So I have a dashboard sheet named "Business Plans" where I have a dropdown in cell A2 that's a dropdown selection of a range called "Facilities" and all dashboard data are driven off of lookups. What I want to do is First create a new workbook than a new tab for each dropdown selection with the tab in the same format but the data pasted as values. I attempted the following code that I created to save every dropdown selection as PDF but I have been unsuccessful. Any insight on how I can get this code working will be great.

  Sub Worksheet_Generator()

    Dim cell As Range
    Dim wsSummary As Worksheet
    Dim counter As Long

    Set wsSummary = Sheets("Business Plans")

    For Each cell In Worksheets("dd").Range("$C3:$C75")
        If cell.Value = "" Then
            counter = counter + 1
            Application.StatusBar = "Processing file: " & counter & "/1042"
        Else
            counter = counter + 1
            Application.StatusBar = "Processing file: " & counter & "/1042"

            With wsSummary
                .Range("$A$2").Value = cell.Value
                ActiveSheet.Copy After:=Worksheets(Worksheets.Count)
                ActiveSheet.Copy
                With ActiveSheet.UsedRange
                    .Value = .Value
                End With
            End With
        End If
    Next cell

Set wsSummary = Nothing
End Sub

If we could get some sample data of what you are trying to accomplish, that would be extremely helpful for us to be able to help you. Also, there are a few lines of your code that could easily be removed by moving them, it is very helpful when writing to try not to duplicate code. — Histerical, May 17 '16 at 22:52
I'm kind of confused. You want to create new workbooks/sheets for each selected item in the combobox or export as PDF? — NuWin, May 17 '16 at 23:10
@NuWin No What I want is for each selection in my drop down I want to create a tab for it but I want all the data to just be values. Currently each drop down trigger lookup formulas for data manipulation. I will preferably want the newly created tabs in a new workbook but the same workbook will be fine as well. — user3666237, May 18 '16 at 13:46
@Histerical So for example drop down selection number 1, I want to first create a new workbook that's going to contain all of the drop down selection tabs. So as drop down selection 1 is selected, I want to take that data and paste as values in a tab for it. Than loop to selection 2 and take that data and paste as values in a seperate tab for it as well. I want to do this for all drop down selections. — user3666237, May 18 '16 at 13:48

score 0 · Answer 1 · edited May 23 '17 at 11:52

I think you are looking for something like the below (adapted from copying-dynamic-rows-into-new-workbook-and-save-it).

Option Explicit
Sub grabber()
    Dim thisWb As Workbook: Set thisWb = ThisWorkbook
    Dim thisWs As Worksheet: Set thisWs = thisWb.Worksheets("dd") 'replace with relevant name
    Dim newBook As Workbook
    Dim newws As Worksheet
    Dim pathToNewWb As String
    Dim uKeys
    Dim currentPath, columnWithKey, numCols, numRows, uKey, dataStartRow, columnKeyName

    'nobody likes flickering screens
    Application.ScreenUpdating = False
    'remove any filter applied to the data
    thisWs.AutoFilterMode = False

    'get the path of the workbook folder
    currentPath = Application.ThisWorkbook.Path

    'Set the stage
    '###Hardcode###
    columnKeyName = "Facility" 'name of the column with the facility values
    dataStartRow = 4 'this is a pure guess, correct as relevenat. Use the header row index
    pathToNewWb = currentPath & "/Business Plans.xlsx" ' where to put the new excel, if you want a saveas prompt you should google "Application.FileDialog(msoFileDialogSaveAs)"
    uKeys = Range("Facilities").Value
    '###Hardcode End###
    columnWithKey = thisWs.Range(dataStartRow & ":" & dataStartRow).Find(what:=columnKeyName, LookIn:=xlValues).Column
    numCols = thisWs.UsedRange.Columns.Count

    'extract the index of the last used row in the worksheet
    numRows = thisWs.UsedRange.Rows.Count

    'create the new workbook
    Set newBook = Workbooks.Add

    'loop the facilities, and do the work
    For Each uKey In uKeys

        'Filter the keys column for a unique key
        thisWs.Range(thisWs.Cells(dataStartRow, 1), thisWs.Cells(numRows, numCols)).AutoFilter field:=columnWithKey, Criteria1:=uKey

        'copy the sheet
        thisWs.UsedRange.Copy

        'Create a new ws for the facility, and paste as values
        Set newws = newBook.Worksheets.Add
        With newws
            .Name = uKey 'I assume the name of the facility is the relevant sheet name
            .Range("A1").PasteSpecial xlPasteValues
        End With

        'remove autofilter (paranoid parrot)
        thisWs.AutoFilterMode = False

    Next uKey

    'save the new workbook
    newBook.SaveAs pathToNewWb
    newBook.Close

End Sub

EDIT:

As I have not seen your data, I would not be surprised if it requires some revision.

First I try to "frame" the range of the worksheet "dd" that contains the data (the ###Hardcode### bit), define the path for the output, and identify the column that can be filtered for the values corresponding to the named range "Facilities".

I retrieve the values of the named range "Facilities" (into uKeys), and create the output workbook (newBook). Then we go through each value (uKey) from the uKeys in the for loop. Within the loop, I apply an autofilter for the uKey. The filtration is followed by creation of a sheet (newWs) in newBook, and a copy paste of the filtered worksheet "dd" into newWs. we then turn off the autofilter, and the worksheet "dd" is returned to its unfiltered state.

At the end we save newBook to the desired location, and close it.

N thanks for the effort but your code has me loss can you please explain it — user3666237, May 18 '16 at 13:43
I've made an edit with an attempt, but I would be better able to help you if you could let me know where you get lost — Andreas N., May 18 '16 at 14:10
I get an error at the line that say Hardcode. I don't know if I'm suppose to customize it. Just to paraphrase what I want to do is each drop down selection alters the data, and for each drop down selection I want to first create a new workbook and a new tab in that workbook for each drop down selection. — user3666237, May 18 '16 at 14:25
I fear that I am now the lost one. For each drop down, a new workbook should be created (n possible values would give n unique workbooks), each with a tab for every possible value of the drop down? if that is correct, then what should be in each tab? — Andreas N., May 18 '16 at 14:49
I just want one new workbook, and for each dropdown selection I want a separate worksheet or tab with the data as values in that new workbook. The reason why is each drop down selection changes the dashboard and I want to share that snap shot for each dropdown by itself — user3666237, May 18 '16 at 15:02
and that is what the code does. if the data itself is not filtered you can just replace the application of the autofilter within the for loop, with an update to the dropdown that triggers the recalculation. The uKey variable will still be the relevant value — Andreas N., May 19 '16 at 07:21

Macro that loops through drop down and creates a worksheet for each drop down selection

1 Answers1