Macro not incrementing

billfinn

Board Regular
Joined
Jun 7, 2018
Messages
114
Office Version
  1. 2016
Platform
  1. Windows
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

Code:
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
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
Based on just a quick review of your code, it looks like some of your statements are round the wrong way, e.g.

Code:
'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
 
Upvote 0
Based on just a quick review of your code, it looks like some of your statements are round the wrong way, e.g.

Code:
'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
 
Upvote 0
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
Code:
iLastRowVC = wsVC.Cells(Rows.Count, "B").End(xlUp).Row ' change 'B' to 'A'
 
Last edited:
Upvote 0
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
 
Upvote 0
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!!
 
Last edited:
Upvote 0
I Agree with Stephen that this
Code:
wsVC.Range("a" & iLastRowVC).Value = CurrentVersion
should be
Code:
CurrentVersion = wsVC.Range("a" & iLastRowVC).Value
This line is doing nothing & can be deleted
Code:
Version = wsVC.Range("A" & iLastRowVC).Value 'Overwrite the version number on the version control sheet
And should this
Code:
VersionFileName = masterwb & "-" & "V" & iLastRowVC
be
Code:
VersionFileName = masterwb & "-" & "V" & Version
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,535
Messages
6,114,198
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top