Saving Files

[Norgbort Machine]

I am trying to write something that will open a series of files in a
folder, identify a certain cell reference, and then resave the file in
a different location with the cell reference as a file name.

Ex: It opens fileA from the source folder, sees that the reference
cell is 001, then saves it in the destination folder as 001.xls. It
then opens fileB, sees the reference of AA3, and saves it as AA3.xls.

Here is what I have written so far. I have 0 experience with VBA but
I am trying to learn by looking at examples online. Something is
wrong here:

Sub SaveFiles()
Dim mybook As Workbook
Dim i As Long
Dim destrange As Range
Application.ScreenUpdating = False
With Application.FileSearch
.NewSearch
.LookIn = "H:\SURVEY STUFF\Returned\Update\"
.SearchSubFolders = False
.FileType = msoFileTypeExcelWorkbooks
If .Execute() > 0 Then
For i = 1 To .FoundFiles.Count
Set mybook = Workbooks.Open(.FoundFiles(i))
Set destrange =
mybook.Worksheets("SiteSummary").Cells("F5")

**** mybook.SaveAs ("H:\SURVEY STUFF\Returned\English\" &
Range(destrange).Value) ****

mybook.Close
Next i
End If
End With
Application.ScreenUpdating = True
End Sub



I believe the problem line is the one I indicated with the ****.

I would appreciate any help.

Thank you.

 

[Dave Peterson]

DestRange is already a range, so you can't use range(destrange).

Untested, but it did compile ok:

Option Explicit

Sub SaveFiles()
Dim mybook As Workbook
Dim i As Long
Dim destrange As Range
Application.ScreenUpdating = False
With Application.FileSearch
.NewSearch
.LookIn = "H:\SURVEY STUFF\Returned\Update\"
.SearchSubFolders = False
.FileType = msoFileTypeExcelWorkbooks
If .Execute() > 0 Then
For i = 1 To .FoundFiles.Count
Set mybook = Workbooks.Open(.FoundFiles(i))
Set destrange = mybook.Worksheets("SiteSummary").Cells("F5")
mybook.SaveAs _
Filename:="H:\SURVEY STUFF\Returned\English\" _
& destrange.Value & ".xls"
mybook.Close savechanges:=false
Next i
End If
End With
Application.ScreenUpdating = True
End Sub