Dropdown list (data validation) loop (select, refresh and save as)

[Mcstefan]

Hi,
I have a master file which has a dropdown list (cell A12 below) and I would like the macro to loop through all the items in the list one by one. After each selection, I need to refresh/retrieve data with F9, then create a new file, copy/paste the refreshed data into the new file and then return to the master file. And keep looping through the items in the drop down list until the last one. The names of the files that are created (Book4 below) could be the item name in the dropdown list (read it from cell A12). Here is the macro that I recorded, so it reflects pretty much what I am looking for. The only thing that I need is to put it into a loop that reads all the items from the dropdown list.
Any help is much appreciated.
Cristian.

 

[DanteAmor]

Try this.

If automatic calculation is active, it is not necessary to update with F9. Or unless you have pivo tables or data connections that require updating.
Anyway I left the line of code.

Update the folder path inside the macro.

Recommendation: use code tag to put the code. That way we can copy it.

Sub Dropdown_List_Loop()
  Dim sList As String, sPath As String
  Dim wb As Workbook, rng As Range, c As Range
 
  Application.Calculation = xlCalculationAutomatic
  Application.ScreenUpdating = False
  Application.DisplayAlerts = False
  'Application.Run Range("cc.F9")

  sPath = "\\Client\M$\Accounting\..."

  With Range("A12")
    sList = Mid(.Validation.Formula1, 2)
    Set rng = Range(sList)
    For Each c In rng
      .Value = c.Value
      Cells.Copy
      Set wb = Workbooks.Add
      wb.Sheets(1).Range("A1").PasteSpecial xlPasteValues
      wb.SaveAs sPath & c.Value & ".xlsx", FileFormat:=xlOpenXMLWorkbook
      wb.Close
    Next
  End With
 
  Application.CutCopyMode = False
End Sub

 

[Mcstefan]

Hi DanteAmor,
Thank you for your reply.
The F9 is very important because after each selection of an item from the dropdown list I need it to connect to the database to retrieve new data. I have tried to put the 'Application.Run Range("cc.F9") after the With Range, but I am getting the run-time 1004 error - method range of object global failed.
May I get your help on this?
Cristian.

 

[DanteAmor]

After this line

.Value = c.Value

Put this:
Application.Run Range("cc.F9")

 

[Mcstefan]

Unfortunately I still getting the same error on the same line - Application.Run Range("cc.F9")
here is my code.

Sub Dropdown_List_Loop()
Dim sList As String, sPath As String
Dim wb As Workbook, rng As Range, c As Range

Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = False
Application.DisplayAlerts = False

sPath = "\\Client\M$\Accounting\Accounting Management\CONSO files\Conso software folder\3_Reports\"
With Range("A12")
sList = Mid(.Validation.Formula1, 2)
Set rng = Range(sList)
For Each c In rng
.Value = c.Value
Application.Run Range("cc.F9")
Cells.Copy
Set wb = Workbooks.Add
wb.Sheets(1).Range("A1").PasteSpecial xlPasteValues
wb.SaveAs sPath & c.Value & ".xlsx", FileFormat:=xlOpenXMLWorkbook
wb.Close
Next
End With

Application.CutCopyMode = False
End Sub

 

[DanteAmor]

Application.Run Range("cc.F9")

That line is part of your code. Do you have any other code?

Deletr this line:

Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = False
Application.DisplayAlerts = False

 

[Mcstefan]

I had other recorded codes that I have just deleted. right now I have only one code which is the below and it still bugs at the same line
Sub Dropdown_List_Loop()
Dim sList As String, sPath As String
Dim wb As Workbook, rng As Range, c As Range

sPath = "\\Client\M$\Accounting\Accounting Management\CONSO files\Conso software folder\3_Reports\"

With Range("A12")

sList = Mid(.Validation.Formula1, 2)
Set rng = Range(sList)
For Each c In rng
.Value = c.Value
Application.Run Range("cc.F9")

Cells.Copy
Set wb = Workbooks.Add
wb.Sheets(1).Range("A1").PasteSpecial xlPasteValues
wb.SaveAs sPath & c.Value & ".xlsx", FileFormat:=xlOpenXMLWorkbook
wb.Close
Next
End With

Application.CutCopyMode = False
End Sub

 

[DanteAmor]

You could try your original macro and tell me if this works:
Application.Run Range("cc.F9")

_____________________________________________________________________________________________

Try this

Sub Dropdown_List_Loop()
  Dim sList As String, sPath As String
  Dim wb As Workbook, rng As Range, c As Range
 
  sPath = "\\Client\M$\Accounting\Accounting Management\CONSO files\Conso software folder\3_Reports\"

  With Range("A12")
    .Select
    sList = Mid(.Validation.Formula1, 2)
    Set rng = Range(sList)
    For Each c In rng
      .Value = c.Value
Application.Run Range("cc.F9")
      Cells.Copy
      Set wb = Workbooks.Add
      wb.Sheets(1).Range("A1").PasteSpecial xlPasteValues
      wb.SaveAs sPath & c.Value & ".xlsx", FileFormat:=xlOpenXMLWorkbook
      wb.Close
    Next
  End With
 
  Application.CutCopyMode = False
End Sub

 

[Mcstefan]

nope, my original recorded macro does not work either.
I don't know if it is important, but probably I should have specified that I am not working with my regular Excel 365.
So I am using Excel 2016 lunched from IBM server through Citrix which has an add-in Cognos Controller.
the formals that the F9 should refresh are something like =cc.fGetVal(C$2,0,C$8,C$1,C$11,C$4,C$7,C$3,$A71,,C$9,C$10,,,C$5,,C$6)
I don't know if it makes any difference, but just in case...

 

[DanteAmor]

nope, my original recorded macro does not work either.
I don't know if it is important, but probably I should have specified that I am not working with my regular Excel 365.
So I am using Excel 2016 lunched from IBM server through Citrix which has an add-in Cognos Controller.
the formals that the F9 should refresh are something like =cc.fGetVal(C$2,0,C$8,C$1,C$11,C$4,C$7,C$3,$A71,,C$9,C$10,,,C$5,,C$6)
I don't know if it makes any difference, but just in case...

I don't have 365 to try.
If the macro is going to work in 365 and the cc.F9 function works in 365 then perform the test on your computer with 365.
For now, it seems good to you if you delete that line and try the macro so you can review the rest of the code.