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

ricardo9316

New Member
Joined
Apr 17, 2014
Messages
22
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
 
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.
 
Upvote 0
HOw can I create a Loop to execute a Save As for each record within the List
Try this:

Rich (BB code):
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
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top