Using this code, problem is, after the sheet is copied, the original workbook always remains as the activesheet...even though I clearly activate a different sheet. Ive tried several different variations and everytime the activesheet doesnt change as necessary.
Code
Sub Save_TimeSheet()
Dim wb As Workbook, newwb As Workbook
Dim filename As String, Name As String
Dim sheeta As Worksheet
Set wb = ActiveWorkbook
Application.ScreenUpdating = False
Name = wb.Sheets(1).Cells(2, 3).Value 'Gets name
If Name = "" Or Name = "Engineers Name" Then 'Bad Values
MsgBox "Please select valid employee name", vbOKOnly, "Invalid Name"
Exit Sub
End If
wb.Sheets(1).Copy 'Copy Sheet
Set newwb = ActiveWorkbook 'Set for new sheet
newwb.Sheets(1).Activate 'Activate the new sheet
ActiveSheet.Unprotect 'unprotect new sheet
ActiveSheet.Shapes("Button 13").Visible = False 'remove user button from sheet
newwb.Sheets(1).Range("A9", "A17").Copy 'copy range to remove formulas
newwb.Sheets(1).Range("A9", "A17").PasteSpecial Paste:=xlPasteValues 'paste just values
ActiveSheet.Protect 're-protect the sheet
i = InStr(Name, ",") 'create filename out of employee name removing ',' and ' '
Name = Left(Name, i - 1) & "_" & Right(Name, Len(Name) - i - 1)
filename = "\\hsv-datavault\Engineering Time Capture\" & Format(newwb.Sheets(1).Cells(1, 3).Value, "yyyymmdd") & "\" & Name & ".xls" 'name for file to be saved as
Allow_Save (True) 'allow file to be saved
newwb.SaveAs filename ' save
newwb.Close 'close
Allow_Save (False) 'turn off saving
wb.Close False 'close
End Sub
Display More
The new sheet doesnt activate so the button is taken off the original sheet and then the pastespecial fails because the sheet is protected.
Thanks.