none
Macro for splitting one sheet into multiple sheets with hidden tab for dropdown menu (data validation) RRS feed

  • Question

  • Hello,

    I'm hoping you all can help. I currently am working on a VBA for an excel sheet to be used as a survey for different schools. There is a hidden tab included so surveyors can use a dropdown menu of pre-selected answers to fill out on the sheet as well as an "Instructions" tab that is not hidden. I have VBA to split the table into multiple sheets based on school and to export them into different workbooks based on school, but I am having trouble exporting the hidden tab to every workbook with data validation turned on as well as the "Instructions" tab.

    Is there a way to have a hidden tab used for dropdown menus (data validation) exported to multiple workbooks by school as well as an "Instructions" tab? Thank you very much for your help!

    Thursday, April 14, 2016 5:08 PM

Answers

All replies

  • 'Post edited to correct an error since initial posting.

    Not certain that I am on the same wavelength but see if the following example helps. The idea is to copy both the worksheet containing the validation cells and the worksheet containing the lists together to the new workbook.

    If does resolve your problem then feel free to get back to me because I am not certain that I am understanding the question correctly.

    Sub Macro1()
        Dim wbNew As Workbook
        Dim arrSchools()
        Dim i As Long
       
        'Array of school names created for the demo example
        arrSchools() = Array("School1", "School2", "School3")
       
        For i = LBound(arrSchools) To UBound(arrSchools)
            'Copies the worksheet with validation cells
            'plus worksheet with validation Lists to a new workbook.
            'Where "Sheet1" contains Validation DropDowns and
            '"Sheet2" contains the validation lists.
            ThisWorkbook.Sheets(Array("Sheet1", "Sheet2")).Copy
           
            Set wbNew = ActiveWorkbook  'New workbook becomes the ActiveWorkbook
           
            wbNew.SaveAs arrSchools(i) & ".xlsx"    'Save the workbook
           
            wbNew.Close SaveChanges:=False  'Close the workbook
        Next
        Set wbNew = Nothing
    End Sub


    Regards, OssieMac


    • Edited by OssieMac Friday, April 15, 2016 12:18 AM
    Friday, April 15, 2016 12:17 AM
  • Hi Micahceous,

    From your overall description I understand that you have 1 excel sheet. On that sheet there are 2 tabs. 1 is hidden and other is visible. You are using these tabs with drop down menu.

    But how you are working with tabs and dropdown menu is unclear.

    Then you have mention that you have VBA to split the table into multiple sheets and then you try to export that sheets in some other workbooks

    Here I want to know where is the table you are talking about and you are having a VBA to split the table is a different VBA? Or it’s a same

    You mentioned that you are having trouble to export the hidden tab. So can it be possible that at the time of exporting the tab you make it visible and after exporting it you again make it invisible.

    It is better if you share your code so that we can understand it better and try to help.

    Regards

    Deepak


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Friday, April 15, 2016 2:16 AM
    Moderator
  • Hi Micahceous,

    after seen the suggestion by the OssieMac. I understand that you are not talking about tabs in excel sheet but it is excel sheet it self am I right? correct me if I am wrong.

    and you are having an problem exporting that hidden sheet right?

    Regards

    Deepak


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Friday, April 15, 2016 2:27 AM
    Moderator
  • Hi Micahceous,

    Try the following code to export the hidden sheet.

    Sub demo()
    Set CurrentWorkbook = Application.ActiveWorkbook
    'Create a new Workbook
    Set UsageData = Application.Workbooks.Add
    'copy a hidden sheet in my workbook to the new workbook
    CurrentWorkbook.Worksheets("mydatasheet").Copy after:=UsageData.Worksheets(1)
    'activate my new workbook
    UsageData.Sheets(1).Activate
    UsageData.Sheets(1).Cells(1, 1).Value = "Please save this file."
    Set CurrentWorkbook = Nothing
    Set UsageData = Nothing
    End Sub
    

    Regards

    Deepak


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Friday, April 15, 2016 2:40 AM
    Moderator
  • You should probably start here.

    http://www.rondebruin.nl/win/s3/win006.htm

    Just start with that and get the split one-to-multiple sheets thing working and then work on the next part, which sounds like hiding and un-hiding sheets.

    For that part, see this link. 

    http://www.excelcampus.com/vba/unhide-multiple-sheets-excel/

    Just take it one step at a time.  You will get there.


    MY BOOK

    Saturday, April 16, 2016 1:43 PM
  • Thank you! I'll give it a shot and let you know how it goes. I apologize for not explaining more sooner.

    Essentially I have a worksheet with lots of data and I want to separate the worksheet into multiple books, in this case by the value in Column D. These will be used essentially as a survey, with a hidden tab that allows for drop-down menus in this original sheet in the book. I hope that's more clear. 

    • Edited by Micahceous Monday, April 18, 2016 7:21 PM
    Monday, April 18, 2016 7:18 PM
  • Thanks. Thing is, I'm able to split one-to-multiple sheets and hide and unhide sheets. The problem comes with hiding a sheet that is also used for data validation to create dropdown menus. That's the tricky part I cant' quite wrestle with. I can do each separately, but combining a VBA that can both hide a tab and also use it for data validation has been tricky for me. 
    Monday, April 18, 2016 7:22 PM
  • Hi Micahceous,

    can you able to provide us a simple demo of code that you are working with?

    you mention that you are able to do it separately but when you combine them you are facing a problem?

    did you get any errors or exceptions?

    Regards

    Deepak


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Tuesday, April 19, 2016 1:46 AM
    Moderator
  • Hi there,

    Sure thing. Here's the code I am using that keeps breaking. It exports the hidden sheet (though I removed the hidden feature simply to try to get it to work) and then provides data validation, though again, doesn't appear to be working.

    Public Sub Dropdown()

        Dim sourceSheet As Worksheet
        Dim folder As String, filename As String
        Dim destinationWorkbook As Workbook
        
        'Worksheet in active workbook to be copied as a new sheet to the 160 workbooks
        
        Set sourceSheet = ActiveWorkbook.Worksheets("DROPDOWNS")
        
        'Folder containing the 160 workbooks
        
        folder = "Local C drive\
           
        filename = Dir(folder & "*.xls", vbNormal)
        While Len(filename) <> 0
            Debug.Print folder & filename
            Set destinationWorkbook = Workbooks.Open(folder & filename)
            sourceSheet.Copy before:=destinationWorkbook.Sheets(1)
            Dim sheet As Worksheet

        Set sheet = ActiveWorkbook.Worksheets("DROPDOWNS")


    'This next portion is for data validation

        Range("N2").Select
        With Selection.Validation
            .Delete
            .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
            xlBetween, Formula1:="=DROPDOWNS!A2:A3"
            .IgnoreBlank = False
            .InCellDropdown = True
            .InputTitle = "MANDATORY ENTRY:"
            .ErrorTitle = "Column N:"
            .InputMessage = _
            "REQUIRED: You must select either YES or No from the dropdown menu."
            .ErrorMessage = "You must select either Yes or No from the dropdown menu."
            .ShowInput = True
            .ShowError = True
        End With

    End Sub

    Tuesday, April 19, 2016 3:45 PM
  • Hi Micahceous,

    I try to run your code. but I find that you have forgot to end the while loop. so I want to know at which place you end your loop. can you edit your above mentioned code.

    did you end your loop before data validation part or you have end your loop after data validation part?

    Regards

    Deepak


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Wednesday, April 20, 2016 5:38 AM
    Moderator
  • Thanks, I was able to troubleshoot and it seems to work now. The only problem I have now is that my data validation list is pointing to the main spreadsheet instead of the hidden worksheet inside each workbook, but I think I can find that relatively easily (I hope). Thanks for your help!
    Wednesday, April 20, 2016 1:37 PM
  • Hi Micahceous,

    The only problem I have now is that my data validation list is pointing to the main spreadsheet instead of the hidden worksheet inside each workbook,

    Does your issue get solved?

    Regards

    Deepak


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Thursday, April 21, 2016 7:47 AM
    Moderator
  • Hi Deepak,

    That's my exact issue too. I'm trying to troubleshoot it but not sure what to fix. Any suggestions?


    • Edited by Micahceous Thursday, April 21, 2016 2:08 PM
    Thursday, April 21, 2016 2:08 PM
  • Hi Micahceous,

    did you mean your hidden worksheet exported as main worksheet? instead of hidden?

    Regards

    Deepak


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Monday, April 25, 2016 9:08 AM
    Moderator
  • I was able to hide it and now the data validation no longer points to the main workbook. I moved workbook/save to the end before the Loop and it fixed it. 
    Monday, April 25, 2016 4:40 PM
  • Hi Micahceous,

    so moving the workbook/save to the end before the loop fix your issue finally.

    its good to hear you that your issue has been solved.

    Regards

    Deepak


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Tuesday, April 26, 2016 3:36 AM
    Moderator