Change a Cell based on a List and Save the Worksheet with that Cell Name

[ricardo9316]

So I haev the following VBA, pretty staright forward code to perfomr a Save As with the name in A1. But I would like for A1 to change "as a data validation/list" based on a list that I have (say: D1:D10) and save the Worksheet with the Cell value Name for each of the 10 records.


Sub SaveAsExample()


Dim FName As String
Dim FPath As String

FPath = "Z:\Ricardo"
FName = Sheets("Sheet1").Range("A1").Text
ThisWorkbook.SaveAs Filename:=FPath & "\" & FName


End Sub

 

[Fishboy]

So I haev the following VBA, pretty staright forward code to perfomr a Save As with the name in A1. But I would like for A1 to change "as a data validation/list" based on a list that I have (say: D1:D10) and save the Worksheet with the Cell value Name for each of the 10 records.


Sub SaveAsExample()


Dim FName As String
Dim FPath As String

FPath = "Z:\Ricardo"
FName = Sheets("Sheet1").Range("A1").Text
ThisWorkbook.SaveAs Filename:=FPath & "\" & FName


End Sub

Hi Ricardo,

If A1 has a list of possible values and you would like to be able to save as any of them (based on which is selected of course), just swap .Text for .Value on the FName line of code.

 

[ricardo9316]

HOw can I create a Loop to execute a Save As for each record within the List

 

[Fishboy]

HOw can I create a Loop to execute a Save As for each record within the List

Try this:

Sub MultiSave()
' Define variables
Dim Cell As Range, cRange As Range, FPath As String
' Set desired file path
FPath = "Z:\Ricardo"
' Set range to check for desired filenames
Set cRange = Range("D1:D10")
' Disable screen updating to reduce flicker
Application.ScreenUpdating = False
' For each cell in check range
For Each Cell In cRange
' Save workbook to specified file path using the cell value as filename
    ThisWorkbook.SaveAs Filename:=FPath & "\" & Cell.Value
' Move to next cell in cRange
Next Cell
' Re-enable screen updating
Application.ScreenUpdating = True
End Sub

 

[Mohammad Basem]

Hi ricardo9316,

May I ask why do you want to change the validation list to the range D1:D10 if you want to use all the values in the range and not the selected one?

You can just have a normal loop through the range D1:D10, pick the value from each cell and save the workbook.

 

[ricardo9316]

This worked awesome. Ok so..we are in the right direction of what I am trying to achive. Now, is there a way that I can apply this EXACT SAME concept but instead of changing "A1" based on a string (D1:D10), what if A1 was the Filter Cell of a PIVOT. How can I Tell the pivot to change through all the names listed under A1's filter and Save As in the same way?

 

[ricardo9316]

I guess thats what I meant to ask

 

[Fishboy]

This worked awesome. Ok so..we are in the right direction of what I am trying to achive. Now, is there a way that I can apply this EXACT SAME concept but instead of changing "A1" based on a string (D1:D10), what if A1 was the Filter Cell of a PIVOT. How can I Tell the pivot to change through all the names listed under A1's filter and Save As in the same way?

OK, so my code above doesn't even take A1 into account, it purely looks at the list in D1:D10. For your newer query however I am a little confused.

What do you mean exactly by "How can I Tell the pivot to change through all the names listed under A1's filter and Save As in the same way?"

Do you mean that column A is filtered and you want to loop through the values in column A starting at A1, but only the ones visible after the filter is applied?

 

[ricardo9316]

Yes A1 is Filtered and yes I do want to look through the values in the list (D1:D10). So in summary, I have a report that will fill data cells based on the the selection of A1. That's why I wanted for A1 to change for each record in the list. That way the Saved file will show the Selection on A1 (like a "Header") and the values retrived based on that selection.

 

[Fishboy]

Yes A1 is Filtered and yes I do want to look through the values in the list (D1:D10). So in summary, I have a report that will fill data cells based on the the selection of A1. That's why I wanted for A1 to change for each record in the list. That way the Saved file will show the Selection on A1 (like a "Header") and the values retrived based on that selection.

Ok, I understand now.

It will take a bit more thinking on my part to put the code together so please leave it with me for a bit and I will get back to you with some (hopefully) more useful code for you.