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

 

[praveenlal]

Hi Joe, If I leave the formulas it won't show to customers as all these formulas are coming from different files which are save in my computer. It shows #REF when opened in some other computer

 

[Joe4]

OK, that makes sense.
What are your thoughts/comments on splitting up the Macro and Master file into separate files?

 

[praveenlal]

That's a great idea... anyhow I just want to automate this process in few clicks. As said earlier, everyday I have to prepare and send 40-50 such emails with same format excel file attachments.

 

[Joe4]

So, you say that your workbook have two sheets worth of formulas that need to be changed to hard-coded values?
What cells exactly are populated with formulas on each sheet?

 

[praveenlal]

So, you say that your workbook have two sheets worth of formulas that need to be changed to hard-coded values? WE CAN LEAVE 2ND SHEET AS WE'VE ONLY SUMIFS & COUNTIFS FORMULAS. WE CAN KEEP THIS
What cells exactly are populated with formulas on each sheet? A6 & A8 FROM "SUMMARY" SHEET (THESE FORMULA CONTAINS VLOOKUP WHERE WE'VE TO CHANGE THESE FORMULAS TO VALUES) ONLY 2 CELLS

 

[Joe4]

OK, here is VBA code that should create all the files and hard-code those values.
Note that at the top of the code are two variables ("tmp" and "opt") that you will need to update file paths and names to reflect your environment.
I added lots of documentation to my code so that you can follow along and see what each part is doing.

Sub CreateFiles()

    Dim msWB As Workbook
    Dim tmWB As Workbook
    Dim rng As Range
    Dim cell As Range
    Dim fname As String
    Dim tmp As String
    Dim opt As String
    Dim sh As Worksheets
       
'***BE SURE TO UPDATE/SET THE FOLLOWING VALUES!!!***
'   Set full file path and name of template file
    tmp = "C:\TEMP\Joe\Template.xlsx"
'   Set path of where to save output files
    opt = "C:\TEMP\Joe\Output\"
'***************************************************
   
    Application.ScreenUpdating = False
   
'   Capture this workbook as the master workbook
    Set msWB = ActiveWorkbook
   
'   Capture range of names to create from column A
    Set rng = Range("A1:A" & Cells(Rows.Count, "A").End(xlUp).Row)
   
'   Loop through each cell in range
    For Each cell In rng
'       Capture name
        fname = cell.Value
'       Open template workbook and select the "Summary" sheet
        Workbooks.Open tmp
        Sheets("Summary").Activate
'       Assign workbook variable
        Set tmWB = ActiveWorkbook
'       Populate cell A1 in template with name
        Range("A1") = fname
'       Hard-code cells A6:A8 on the "Summary" sheet
        Range("A6:A8").Value = Range("A6:A8").Value
'       Save workbook
        tmWB.SaveAs opt & fname & ".xlsx"
'       Close template workbook
        tmWB.Close
    Next cell
   
    MsgBox "Macro complete"
   
    Application.ScreenUpdating = True
   
End Sub

The email piece of it is a whole separate other question, and should be asked in a new thread.
I do not have much experience in automating emails, so I probably cannot offer much advice there.

 

[praveenlal]

Thanks a lot Joe !!! its working fine, Much appreciated.

2nd part, I'm able to do it myself to send emails with attachments.

You saved my 3-4 hrs everyday of my life by helping me.

Thanks again

 

[Joe4]

You are welcome.
Glad it hear that it worked out for you.