Excel Help - Save Files incrementally

  • Thread starter Thread starter r.brydges
  • Start date Start date
R

r.brydges

hello,

i have a loop moving through a folder of files. i have 130 files and
each opens incrementally, dumps into a template file and then I want to
save the newly filled template as trial 1....trial 130. however, the
save function keeps saving to trial 1 for each trial ( i.e., the save
as function does not change the name incrementally). Do you happen to
know how to do this?


Here is my code:

Sub ryan_macro()
Application.ScreenUpdating = False
Dim i As Integer
Dim wbResults As Workbook
With Application.FileSearch
.NewSearch
.LookIn = "C:\Documents and Settings\RyanB\My
Documents\Thesis\Test"
.SearchSubFolders = False
.Filename = "*.txt"
If .Execute > 0 Then
For i = 1 To .FoundFiles.Count
Set wbResults = Workbooks.Open(Filename:=.FoundFiles(i))
ActiveCell.SpecialCells(xlLastCell).Select
Range(Selection, Selection.End(xlUp)).Select
Selection.Copy
ChDir "C:\Documents and Settings\RyanB\My Documents\Thesis"

Workbooks.Open Filename:= _
"C:\Documents and Settings\RyanB\My
Documents\Thesis\thesis - bump analysis1.xls"
Range("D1").Select
ActiveSheet.Paste
ActiveWorkbook.SaveAs Filename:= _ "MY PROBLEM AREA
"C:\Documents and Settings\RyanB\My
Documents\Thesis\Test\trial(i).xls", _
FileFormat:=xlNormal, Password:="",
WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
ActiveWindow.Close
ActiveWindow.Close
Next i
End If
End With
On Error GoTo 0
Application.ScreenUpdating = True
End Sub


Help the VBA Newbie!!!

Thanks :)
 
Without testing
"C:\Documents and Settings\RyanB\My
Documents\Thesis\Test\trial(i).xls", _
try
Documents\Thesis\Test\trial" & i & ".xls", -

Don Guillett
SalesAid Software
(e-mail address removed)
 
Back
Top