I've fallen and I can't get up. The below is attached to a button that leads the user to another worksheet. I want the Macro to check for a newer version of the workbook (might have some programming changes, or some cosmetic changes) and Save the newer verion on the users desktop. So in steps, this is what i want to happen:
1. User opens the Workbook. There is a Query to another Workbook to update the Version number (Cell H47)
2. Checks User's version (cell H46) against H47
3. If H46 is smaller, opens "Master_SSI.xls" from a Network path
4. Close old version (SSI.xls) 'Up to here, all work perfectly
This is where is stops doing it's thing. I don't get an error message
5. At this point, "Master_SSI.xls" is open so I want to rename it "SSI.xls" and put it on the user's Desktop making it the updated version. But "Master_SSI" stays open instead.
What have i missed?
Sub GroupQQ_Click()
Dim ws As Worksheet
'check for newer version
If Range("h46") < Range("h47") Then
MsgBox "New Version available, one moment please"
Workbooks.Open "\\Network path...Forms\SSI_Master.xls"
Workbooks("SSI.xls").Close Savechanges:=True
'this is where is stops. does not give error message, just does not proceed to next step which is renaming newer version onto user's desktop
Dim Fname As String
Application.DisplayAlerts = False
Fname = "C:\Documents and Settings\All Users\Desktop\" & "SSI" & ".xls"
ActiveWorkbook.SaveAs Fname
Application.DisplayAlerts = True
MsgBox ("New Version Saved to your Desktop")
Range("D10").Activate
End If
' this next part works fine
Application.ScreenUpdating = False
'Check for user info
If IsEmpty(Range("D10")) Then
MsgBox "Please enter your information"
Else
If Range("D10") = "" Then
MsgBox "Please enter your information"
ElseIf Range("D10").HasFormula Then
MsgBox "Please enter your information"
End If
If Range("D10") > 1 Then
End If
Sheets("QQ").Visible = True
Sheets("Version").Visible = xlVeryHidden
Sheets("Cover").Visible = xlVeryHidden
Sheets("RR").Visible = xlVeryHidden
Sheets("SOP").Visible = xlVeryHidden
Sheets("TT").Visible = xlVeryHidden
Sheets("OFFCL").Visible = xlVeryHidden
Sheets("EX").Visible = True
Worksheets("EX").Select
Columns("B:B").Select
Range("A1:J39").Sort Key1:=Range("B1"), Order1:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Sheets("EX").Visible = xlVeryHidden
Range("C4").Select
Application.ScreenUpdating = True
End If
End Sub