Macro not incrementing

[billfinn]

Good morning!
My apologies for the somewhat cryptic Title but I was unsure just how to phrase this
This macro saves a version of a file in a specific folder and names it filename - version - extension
The version is incremented each time a change is saved.
The new versions are saved with correct version numbers in the designated folder.

The section that increments the version in the Version Control worksheet doesn't work correctly. I put data in the top row of the Version Control worksheet setting cell A1 to 0 so the first saved change should be 1, next saved change should be 2 etc.
When I save the first version the new row gets pasted into the worksheet with a value of 1 in A2. Just what I am looking for. The problem shows up when Version 2 and beyond get saved. Then A2 gets changed to 0 and the new value pasted into A3 is 1. I got this macro from the web and adapted it to my needs but I can't seem to figure out how to make it work the way I want it to. I'm sure the issue is in the line with the comment "Overwrite the version number on the version control sheet".
I tried changing it to read Version = wsVC.Range("A" & iLastRowVC).Value + 1 but that didn't work. I tried Version = wsVC.Range("A" & iLastRowVC+1).Value - still no luck

I'm grateful for any suggestions or input
Thanks much,
Bill

Sub FreezeWorkbook() 'Created by Bill Finn 8/10/2018
'Working in Excel 97-2003
'Permanently Save Workbook as Changed With New Version Number
Dim FileExtStr As String
Dim FileFormatNum As Long
Dim Sourcewb As Workbook
Dim wsVC As Worksheet
Dim Destwb As Workbook
Dim VersionFilePath As String
Dim VersionFileName As String
Dim MasterFilePath As String
Dim MasterFileName As String
Dim VersionDate As String
Dim Version As Integer
Dim CurrentVersion As Integer
Dim iLastRowVC As Long 'LAST ROW IN VERSION CONTROL SHEET
Dim Author As String
Dim Changes As String
Dim AmendRef As String
Dim Originalwb As String




With Application
.ScreenUpdating = False     'Turn off processes that could slow down the macro
.EnableEvents = False
End With






'UPDATE THE VERSION CONTROL DETAILS
ActiveSheet.Unprotect       'Unprotect th worksheet so the ,acro can do it's jonb
Set wsVC = Worksheets("VERSION CONTROL")
iLastRowVC = wsVC.Cells(Rows.Count, "B").End(xlUp).Row


'Current Version
wsVC.Range("a" & iLastRowVC).Value = CurrentVersion
'******************************************************
'Insert version
Version = CurrentVersion + 1
wsVC.Range("A" & iLastRowVC + 1).Value = Version
'need to automate version number - add code for this
'******************************************************


'Insert version date
VersionDate = Format(Now, "mm-dd-yy")
wsVC.Range("B" & iLastRowVC + 1).Value = VersionDate


Author = InputBox("Please enter your name")
wsVC.Range("C" & iLastRowVC + 1) = Author


Changes = InputBox("Please enter a brief description of changes made")
wsVC.Range("D" & iLastRowVC + 1) = Changes
ActiveWorkbook.Save


'Save the master workbook
Application.DisplayAlerts = False
Set Sourcewb = ActiveWorkbook


'Force the file extension to remain as Excel 97-2003
FileExtStr = ".xls": FileFormatNum = 56


'Overwrite the master report or create it if it doesn't yet exist
MasterFilePath = "S:\Estimator\"
MasterFileName = ActiveWorkbook.Name
[B]Version = wsVC.Range("A" & iLastRowVC).Value 'Overwrite the version number on the version control sheet[/B]


With Sourcewb
.SaveAs MasterFilePath & MasterFileName
Application.DisplayAlerts = True
End With
'save the version workbook
Set Sourcewb = ActiveWorkbook
'Force the file extension to remain as Excel 97-2003
FileExtStr = ".xls": FileFormatNum = 56


'Save the new workbook and close it
VersionFilePath = "S:\Estimator\Versions\"
masterwb = Left(ActiveWorkbook.Name, Len(ActiveWorkbook.Name) - 4)


VersionFileName = masterwb & "-" & "V" & iLastRowVC     'Name the Version
ActiveSheet.Protect     'Protect the sheet again


With Sourcewb
.SaveAs VersionFilePath & VersionFileName & FileExtStr
End With




MsgBox "New version saved as " & VersionFilePath & VersionFileName


With Application
.ScreenUpdating = True      'Turn the processes back on
.EnableEvents = True
End With
Close
End Sub

 

[StephenCrump]

Based on just a quick review of your code, it looks like some of your statements are round the wrong way, e.g.

'I think instead of
wsVC.Range("a" & iLastRowVC).Value = CurrentVersion
'you mean
CurrentVersion = wsVC.Range("a" & iLastRowVC).Value
'otherwise you are not setting CurrentVersion and it will always be zero, hence Version will always be 1

'Similarly, it looks like instead of 
Version = wsVC.Range("A" & iLastRowVC).Value 'Overwrite the version number on the version control sheet
'you want
wsVC.Range("A" & iLastRowVC).Value = Version

 

[billfinn]

Based on just a quick review of your code, it looks like some of your statements are round the wrong way, e.g.

'I think instead of
wsVC.Range("a" & iLastRowVC).Value = CurrentVersion
'you mean
CurrentVersion = wsVC.Range("a" & iLastRowVC).Value
'otherwise you are not setting CurrentVersion and it will always be zero, hence Version will always be 1

'Similarly, it looks like instead of 
Version = wsVC.Range("A" & iLastRowVC).Value 'Overwrite the version number on the version control sheet
'you want
wsVC.Range("A" & iLastRowVC).Value = Version

Stephen,
Thanks for the response. I have tried them both ways with no help for the original issue.
thanks,
Bill

 

[daverunt]

Hi,

I think Bill is correct with his changes however because you have column B in this line of code the iLastRowVC doesn't increment as expected. It should be looking at column A

iLastRowVC = wsVC.Cells(Rows.Count, "B").End(xlUp).Row ' change 'B' to 'A'

 

[daverunt]

Sorry - Stephens changes!

 

[billfinn]

DaveRunt,
Thanks very much for your response.
For that particular line of code "B" is correct as that is for the date. In this macro I populate columns A,B, C and D with Version, Date, Name, and brief description of change.
Thanks much,
Bill

 

[billfinn]

StephenCrump,
I went back and tried your suggestion again and it worked! Not sure what I did wrong last time I tried it but thanks much for getting me to try again!
Thanks much,
Bill
RESOLVED!!

 

[Fluff]

I Agree with Stephen that this

wsVC.Range("a" & iLastRowVC).Value = CurrentVersion

should be

CurrentVersion = wsVC.Range("a" & iLastRowVC).Value

This line is doing nothing & can be deleted

Version = wsVC.Range("A" & iLastRowVC).Value 'Overwrite the version number on the version control sheet

And should this

VersionFileName = masterwb & "-" & "V" & iLastRowVC

be

VersionFileName = masterwb & "-" & "V" & Version

 

[billfinn]

Fluff,
I had already changed the first line you referenced and deleted the second line you referenced. I didn't catch the third line you referenced. I will get that one taken care of this afternoon.
Thanks very much for the help!!
Bill

 

[Fluff]

You're welcome