Automatic selection of data from drop down menu ( Macros )

December 15, 2018 at 20:35:00
Specs: Windows 10
Good morning all.
Would anybody be able to help me with below. I have created drop down list of projects which i am getting data based on project number. I created macro which will save separated excel sheet based on project in designated folder and my problem is next:
I am not able to set automatic change in drop down list for any project, for example if i have 5 projects i will get 5 same files even though i selected different project number before i save separated sheet.

Thank you

Ivan


See More: Automatic selection of data from drop down menu ( Macros )

Reply ↓  Report •

#1
December 16, 2018 at 07:25:55
Please keep in mind that we can't see your workbook from where we are sitting nor to we know anything about your work processes.

Without more detail as to how you use this workbook, your data layout, and the macro itself, we just don't have enough information to be of any assistance. If you can provide more detail, we'll see what we can do.

If you are going to post example data and your macro, please be sure to click on the following link and read the instructions found there.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Reply ↓  Report •

#2
December 16, 2018 at 20:12:40
Good morning @DerbyDad03, thank you for your reply.
I Will try to explain below, thing is that i am not writing my codes i am recording macros. Maybe you can
give me some like where i can learn more about it ? Problem below:

Basically I start with recording of macro, going to the sheet " Department costing - Departmentwise",
selecting from drop down menu project number, then this tab has been copied as single tab in different
sheet and then saved under project name in designated folder. And so on for all projects. In example
below there are three, but when i open each one of them i see that i have all same project numbers
saved with same figures for them. Like i am unable to save when i am changing project number from
drop down menu.

Thank you for your time


Sub Macro1()
'
' Macro1 Macro
'

'
    Sheets("Dep Costing - Departmentwise ").Select
    Sheets("Dep Costing - Departmentwise ").Copy
    ChDir "F:\Cost Contol\CO DEPT\IVAN\Department project costing\Working folder"
    ActiveWorkbook.SaveAs Filename:= _
        "F:\Cost Contol\CO DEPT\IVAN\Department project costing\Working folder\19001 Admin Nov 2018.xlsx" _
        , FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
    ActiveWindow.Close
    Sheets("Dep Costing - Departmentwise ").Select
    Sheets("Dep Costing - Departmentwise ").Copy
    ActiveWorkbook.SaveAs Filename:= _
        "F:\Cost Contol\CO DEPT\IVAN\Department project costing\Working folder\19003 PR Nov 2018.xlsx" _
        , FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
    ActiveWindow.Close
    Sheets("Dep Costing - Departmentwise ").Select
    Sheets("Dep Costing - Departmentwise ").Copy
    ActiveWorkbook.SaveAs Filename:= _
        "F:\Cost Contol\CO DEPT\IVAN\Department project costing\Working folder\19002 Accounts Nov 2018.xlsx" _
        , FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
    ActiveWindow.Close
End Sub


Reply ↓  Report •

#3
December 17, 2018 at 10:19:29
First, let me address the following statement, just to be sure that we get the terminology correct. If we use the wrong terminology, things can get messy quickly.

You said: "...then this tab has been copied as single tab in different sheet and then saved ..."

A "sheet" is Worksheet within a Workbook. There is a Tab for each Sheet which contains the Worksheet name.You can't copy a Tab to a Sheet, because they are essentially the same thing. If you look at the terms used when you select Move or Copy by right-clicking a sheet tab you'll that the words are Sheets and Book.

Therefore, I believe what you trying to say is this:

"...then this Sheet has been copied as single Sheet to a new Workbook and then saved ..."

To keep things straight, I am only going to use the terms sheet and Workbook.

Second, I see from your code that there is a trailing space after the name of the sheet that you are copying.

Sheets("Dep Costing - Departmentwise ").Copy

Unless that space is needed, I would eliminate it and then fix your code to read:

Sheets("Dep Costing - Departmentwise").Copy

Leading or trailing spaces in sheet names have the potential to cause problems, especially with VBA code.

OK, as far as your macro goes, the results that you describe are exactly would I expect from your code. My assumption is that you are selecting a value from the drop down and then running the code. When you run the code it does the following:

1 - Copy the Active sheet to a new workbook
2 - Save the new Workbook to the specified folder
3 - Close the new Workbook.
4 - Copy the Active sheet to a new workbook
5 - Save the new Workbook to the specified folder
6 - Close the new Workbook.
7 - Copy the Active sheet to a new workbook
8 - Save the new Workbook to the specified folder
9 - Close the new Workbook.

Nowhere in that code does it wait for you to select a different value from the drop down. Each time you run it, it simply Copies the same sheet to a new workbook and saves it, then does the exact same thing 2 more times. The only difference between the 3 saves is that they occur in 3 different folders because you specified different locations for each save when you recorded the macro.

If you want a different set of data copied to a new Workbook after each drop down selection is made, then you need to shorten the macro to a single "copy/save" operation and run it after each drop down selection is made. Of course, the issue there is that you will also need to tell the macro where to save it each time.

I see from your code that you give the Workbook a name when you save it, e.g. "19001 Admin Nov 2018.xlsx". Where does that come from? Is it the same value that was selected in the drop down? If so, then that value can be captured and used to build the filename.

For example, let's say your drop down is in A1 of "Dep Costing - Departmentwise ". This code will build the filename from the value in A1 and save the file with that name:

Sub SaveCopy()
    Sheets("Dep Costing - Departmentwise ").Copy
      ChDir "F:\Cost Contol\CO DEPT\IVAN\Department project costing\Working folder"
         ActiveWorkbook.SaveAs Filename:= _
             "F:\Cost Contol\CO DEPT\IVAN\Department project costing\Working folder\" & _
              Sheets("Dep Costing - Departmentwise ").Range("A1").Value & ".xlsx" _
              , FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
         ActiveWindow.Close
End Sub

Let me know if that helps.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code

message edited by DerbyDad03


Reply ↓  Report •

Related Solutions

#4
December 19, 2018 at 01:20:47
Good afternoon @DerbyDad03.

Your clarifications are right, thank you so much for that. I will pay more attention to words i am using in order to make it easier.

Macros is working perfectly, i just added month in your line ( i am so proud of my self man :D :D), since range C4 is only Department name&Dep Number mentioned up ( 19001 Admin ), and range C2 is a month&year, so now i have saved workbook "19001 Admin November 2018"

Sheets("Dep Costing - Departmentwise ").Range("C4").Value & _
 Range("C2").Value & ".xlsx" _

Next questions:

1. When i open saved workbook created by Macros i see that i still have formulas there ( Since worksheet " Dep Costing - Departmentvise" is pulling data from another worksheet with formulas ) but i dont need them. Can we just paste values there before macros is done ?

2. Is there a chance now to set macro to automatically change Department number ( Value in C4) so when macros has been done we have all departments saved in separated workbook ( Which i have now, only thing is i need to change department ( C4 value ) manually and run macros?


Reply ↓  Report •

#5
December 19, 2018 at 09:44:43
re: "1. When i open saved workbook created by Macros i see that i still have formulas there ( Since worksheet " Dep Costing - Departmentvise" is pulling data from another worksheet with formulas ) but i dont need them. Can we just paste values there before macros is done ?"

  Sheets("Dep Costing - Departmentwise ").Copy
        With ActiveSheet.UsedRange
             .Value = .Value
        End With
   'Do your Save stuff  

re: "2. Is there a chance now to set macro to automatically change Department number ( Value in C4) so when macros has been done we have all departments saved in separated workbook ( Which i have now, only thing is i need to change department ( C4 value ) manually and run macros?"

Please keep in mind that we can't see your workbook from where we are sitting nor do we know anything about your work processes.

Your original question was related to using a drop down. Now you are asking about a value in a cell. I'd bet that those 2 items are somehow related, but I do lose bets every now and then. Please explain the change in the question asked.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Reply ↓  Report •

#6
December 19, 2018 at 10:07:44
Good evening.

Apologies again, yes it is about drop down which is located in work sheet "Dep costing - departmentwise" in field C4.
Data from drop down has been taken from another worksheet in the same work book


Reply ↓  Report •

#7
December 19, 2018 at 11:18:12
I don't understand this statement:

Data from drop down has been taken from another worksheet in the same work book

You've used the word "from" twice. How can data from the drop down be taken from some other place?

Is this what you are trying to accomplish?

1 - C4 contains the Drop Down Data Validation list.
2 - The drop down allows you to choose a department
3 - When you choose a department from the list, the sheet recalculates based on the then current value in C4.
4 - You now want to eliminate the need to manually choose a department before saving the sheet as a separate workbook. You basically want steps 2 and 3 to happen automatically, once for each department in the list.

Is that correct?

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code

message edited by DerbyDad03


Reply ↓  Report •

#8
December 19, 2018 at 19:00:27
Yes, this is correct.

Reply ↓  Report •

#9
December 19, 2018 at 19:41:17
Assuming you already have a range of cells that contains the department numbers, you can use that to populate C4 repeatedly.

e.g. Let's say your list is in A1:A10

Sub CopyList()
  For Each dpt In Range("A1:A10")
      Range("C4") = dpt
          Sheets("Dep Costing - Departmentwise ").Copy
              With ActiveSheet.UsedRange
                 .Value = .Value
              End With
       'Do your Save stuff
  Next
End Sub

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Reply ↓  Report •

#10
December 19, 2018 at 20:29:32
Now full macros should be having

1. Sub CopyList ( then everything what you mentioned)
2. Sub SaveCopy (and then all mentioned previously) ?


Reply ↓  Report •

#11
December 20, 2018 at 04:20:07
Derby, sorry buddy, i was unable to log in with same ID so i created a new one.
So my macros should be now looking like this ? With below i didnt notice any change ( like before, i am getting saved one department into folder assigned )

Sub CopyList()
  For Each dpt In Range("H4:M4")
      Range("C4") = dpt
          Sheets("Dep Costing - Departmentwise ").Copy
              With ActiveSheet.UsedRange
                 .Value = .Value
              End With
Sub SaveCopy()
    Sheets("Dep Costing - Departmentwise ").Copy
        With ActiveSheet.UsedRange
             .Value = .Value
        End With
      ChDir "F:\Cost Contol\CO DEPT\IVAN\Department project costing\Working folder"
         ActiveWorkbook.SaveAs Filename:= _
             "F:\Cost Contol\CO DEPT\IVAN\Department project costing\Working folder\" & _
              Sheets("Dep Costing - Departmentwise ").Range("C4").Value & Range("C2").Value & ".xlsx" _
              , FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
         ActiveWindow.Close
End Sub



Reply ↓  Report •

#12
December 20, 2018 at 06:47:54
Sorry for the confusion. All of the code was meant to be combined into a single macro.

re: "With below i didnt notice any change ( like before, i am getting saved one department into folder assigned )"

I seriously doubt that. The only thing that you will get with the code that you posted is an error:

Compile Error: Expected End Sub

You have 2 Sub names and only one End Sub. There is no way that the code you posted is Saving anything. It's not even going to run.

Here is the complete code. I added comments so that you understand what each section is doing. You should use comments in any code that you write or record. You may understand what the code does on the day that you write it, but trust me, as your code gets more and more complicated, it really helps to have comments. A year from now you are not going to remember why the code was written the way it was and the comments will jog your memory. You may not need to use a comment for every instruction as I did below...I was just trying to explain it in detail since you are fairly new at this.

Sub CopyList()
        
'Loop through the Range that contains Department numbers
  For Each dpt In Range("H4:M4")
  
'Place Department number in C4
      Range("C4") = dpt
      
'Copy Worksheet to a new Workbook
          Sheets("Dep Costing - Departmentwise ").Copy
          
'Replace formulas with values
              With ActiveSheet.UsedRange
                 .Value = .Value
              End With
              
'Set Directory path (Note: I'm not sure this is needed. Comment it out, Save and Close 
'everything, reopen Excel and then test the code. If it works, delete this section.)
      ChDir "F:\Cost Contol\CO DEPT\IVAN\Department project costing\Working folder"
      
'Build Workbook name, Save and Close Workbook
         ActiveWorkbook.SaveAs Filename:= _
             "F:\Cost Contol\CO DEPT\IVAN\Department project costing\Working folder\" & _
              Sheets("Dep Costing - Departmentwise ").Range("C4").Value & Range("C2").Value & ".xlsx" _
              , FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
         ActiveWindow.Close
   
   Next
End Sub

Perhaps this tutorial will help. It's not a complete list of VBA Debugging techniques, but it's a start.

https://www.computing.net/howtos/sh...


message edited by DerbyDad03


Reply ↓  Report •

#13
December 22, 2018 at 20:50:38
Good morning.

Using single stepping process in order to debug macros i realize next:

Line #1: Copy sheet " Dep Costing - Departmentwise" in single work book
Line #2: Same as Line #1
Line #3: Same as Line #1
Line #4: Same as Line #1 but i am loosing value in Range C4 ( which is drop down where department should be automatically selected )

Then i am coming to the line where it start to save and i am asked to override Workbooks since dept name is not there, as we set that dept name&date should be workbook name saved ( Range C4 & Range C2 ).

Also first three line are not getting different department selected.

Sub CopyList()
 For Each dpt In Range("H4:M4")
  Range("C4") = dpt
   Sheets("Dep Costing - Departmentwise ").Copy
     With ActiveSheet.UsedRange
                 .Value = .Value
              End With
        
        ChDir "F:\Cost Contol\CO DEPT\IVAN\Department project costing\Working folder"
ActiveWorkbook.SaveAs Filename:= _
            "F:\Cost Contol\CO DEPT\IVAN\Department project costing\Working folder\" & _
              Sheets("Dep Costing - Departmentwise ").Range("C4").Value & Range("C2").Value & ".xlsx" _
              , FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
         ActiveWindow.Close
    Next
End Sub


Reply ↓  Report •

Ask Question