Set range in VBA

[praveenlal]

Sub filenameascell()

Dim filename As String
Dim path As String
Dim rng As Range
Worksheets("Sheet1").Activate
Set rng = Range("A1:A1000")

Application.DisplayAlerts = False
path = "C:\Users\New Folder"
filename = rng("A1:A10000").Value & ".xlsx"
ActiveWorkbook.SaveAs path & filename, xlOpenXMLWorkbook
Application.DisplayAlerts = True
ActiveWorkbook.Close

End Sub

 

[Joe4]

What exactly are you trying to do?
If you want to build a file name of a cell, you need to specify a particular cell.
It does not really make sense to take a thousand or more cells and use that as a single file name.

 

[praveenlal]

I want to set a range. Actually I have a multiple list of files where I have to save them in one folder and file name should be as this range A1:A1000. I'm just a beginner in VBA

 

[Joe4]

You would need to loop through the range, something like this:

Dim filename As String
Dim path As String
Dim rng As Range
Dim cell as Range

Worksheets("Sheet1").Activate
Set rng = Range("A1:A1000")

Application.DisplayAlerts = False
path = "C:\Users\New Folder\"

For Each cell in rng
    filename = rng.Value & ".xlsx"
    ...

But you would also need to correlate each file name with a particular file you want to save.
Otherwise, you are just saving the same file over-and-over again with different names.

 

[praveenlal]

Yes, You got it right, I have to SAVE the same file over and over again with different customer names, file format should be same for all customers, only Cell A1 in each file should be same as file name. Once all files are saved, open those files and copy paste special values in both sheets. Please help if you can, and thanks a lot for all your help till now. Much appreciated

 

[Joe4]

I highly suggest NOT opening 1000 files or more at once on your computer. I doubt your computer would be able to handle that.

Where is this information coming from?

Once all files are saved, open those files and copy paste special values in both sheets.

I suggest we go about this in a different manner. Why don't you show us what you have to start with, and explain in plain English exactly what you want to happen.
Remember, while the problem you are trying to solve is very familiar to you, all we have to go on is what you shared with us, and so far that is just a little bit of code that has some problems with it.

 

[praveenlal]

Actually I have to prepare 40-50 files every month for each customer. (1000 was just an example) I've one master file and every month I use the same file for each customer, only difference is Cell A1 should contain that same customer name as there are many formulas (vlookup and countif) which are coming from customer name, once we change customer name, values automatically change. Now once we've saved files for all customers, open those files (all at once or one by one) and copy paste special values in both sheets. Once the files are ready, send emails to all customers with attached files. Thanks a lot.

 

[Joe4]

Still in need of the details!

What is the name of the master file?
Is this VBA code in the master file or some other file?
If some other file, what is the name of that file?

So then are you wanting to then also copy the same value you are naming the file to cell A1 of that file?
And why are you saving and closing it, only to re-open it again later and copy/paste special values over all the cells?
Why not just do all of that before closing the file the first time?

 

[praveenlal]

What is the name of the master file? - MASTER CONSUMER FILE
Is this VBA code in the master file or some other file? - IN MASTER FILE
If some other file, what is the name of that file?

So then are you wanting to then also copy the same value you are naming the file to cell A1 of that file? - YES CORRECT
And why are you saving and closing it, only to re-open it again later and copy/paste special values over all the cells?
Why not just do all of that before closing the file the first time? - YES WE CAN DO IT THAT WAY ALSO, MY ONLY CONCERN IS THAT FORMULAS SHOULD NOT CHANGE WHILE SAVING FILES.

 

[Joe4]

What is the name of the master file? - MASTER CONSUMER FILE
Is this VBA code in the master file or some other file? - IN MASTER FILE

This part is a little concerning. Macros cannot appear in xlsx files. So you would need to do something to remove all the VBA code from the file before saving it. But if you need to save 40-50 files, that could get a little messy as you are trying to run the code you would need to delete!

I think a much better structure would be to have the "master file" (or template) totally separate of the file that contains the VBA code that you are going to run.
Then you do not need to worry about removing the VBA code because there won't be any in it.

So the "VBA file" should just have the code and the list of customers in column A.
And the "Master" file should not have any VBA code, just your structure and formulas.

By the way, why do you need to copy over all the formulas with their values?
Why can't you just leave the formulas?