[VBA] Add 1 To Cell Then Save As

[AllDay]

Goal:

Start with 1 file in a folder. "C:\FL\Test1.xlsx"

In Cell A1 of that file is the value 1.

I need to open that file. Add 1 to Cell A1 then save as a new file. "C:\FL\Test2.xlsx"


I already have the VBA for the ActiveWorkbook.SaveAs to read off of the Range. I am just in need of what to add to add 1 after it's saved off the previous file.


TIA

 

[Joe4]

Please post the code that you have.

 

[AllDay]

Please post the code that you have.

Sub SaveAsB()

Dim FolderPath As String
Dim FileName As String


'Remember time when macro starts
  StartTime = Timer


'Input Location Of Files Being Updated


FolderPath = "C:\FL"


If Right(FolderPath, 1) <> "\" Then FolderPath = FolderPath + "\"


FileName = Dir(FolderPath & "*.xlsx")
Do While FileName <> ""
Application.ScreenUpdating = False
Set WB = Workbooks.Open(FolderPath & FileName)
FileName = Dir


Call B


ActiveWorkbook.Close SaveChanges:=True


Loop


Application.ScreenUpdating = True


'Determine how many seconds code took to run
  MinutesElapsed = Format((Timer - StartTime) / 86400, "hh:mm:ss")


'Notify user in seconds
  MsgBox "This code ran successfully in " & MinutesElapsed & " minutes", vbInformation


End Sub

Sub B()

Dim FilePath As String
Dim FileName As String
FilePath = Sheets("Instructions").Range("A1")
FileName = Sheets("Instructions").Range("A2").Value
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs FileName:=FilePath & "\" & FileName & ".xlsm", FileFormat:=xlOpenXMLWorkbookMacroEnabled
Application.DisplayAlerts = False
ActiveWorkbook.Close SaveChanges:=True




End Sub

 

[Joe4]

If you want to add one, simply add a line like:

Sheets("Instructions").Range("A1") = Sheets("Instructions").Range("A1") + 1

Just be sure to save the file after this code runs, or else those changes won't be saved!

 

[AllDay]

So I had tried using that but if I start with 1. I need to get to 1000. It doesn't keep generating new files I don't think. I just runs through whatever amount of files I have in there and changes that one.

Are you saying to save then move the file and repeat 1000 times?

I guess what I'm trying to do it start with 1 and get to 1000 by saving as

 

[Joe4]

What exactly is in cells A1 and A2 of your instruction sheet?
Is it cell A1 or A2 you are looking to update?

I would use a loop to do what you want, something like:

Sub B()

Dim FilePath As String
Dim FileName As String
Dim x as Long

FilePath = Sheets("Instructions").Range("A1")

For x = 1 to 1000
    Sheets("Instructions").Range("A2").Value = x
    Application.DisplayAlerts = False
    ActiveWorkbook.SaveAs FileName:=FilePath & "\" & x & ".xlsm", FileFormat:=xlOpenXMLWorkbookMacroEnabled
    Application.DisplayAlerts = False
    ActiveWorkbook.Close SaveChanges:=True
Next x

End Sub