Sheet Not Becoming Active

  • 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.

    The new sheet doesnt activate so the button is taken off the original sheet and then the pastespecial fails because the sheet is protected.


  • Re: Sheet Not Becoming Active

    Instead of ActiveSheet why not reference the worksheet in the new workbook?
    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
    Set sheeta = newwb.Sheets(1) 'Activate the new sheet

    sheeta.Unprotect 'unprotect new sheet
    sheeta.Shapes("Button 13").Visible = False 'remove user button from sheet
    sheeta.Range("A9", "A17").Copy 'copy range to remove formulas
    sheeta.Range("A9", "A17").PasteSpecial Paste:=xlPasteValues 'paste just values
    sheeta.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[/vba]

  • Re: Sheet Not Becoming Active


    Looks like you are setting wb and newwb to the same workbook. At least I don't see anything changing the active workbook between the two Set commands.

    I thought the same thing, but newwb was referencing the new workbook because the selection for the copy was made on that sheet.


    Instead of ActiveSheet why not reference the worksheet in the new workbook?

    I actually just tried a code very similar to this prior to making this post and it also failed. Only difference was I used With sheeta rather than putting it on the beginning of all 5 commands.

    Also, the shapes command requires ActiveSheet, cant be done sheeta.Shapes like least not on my machine.