MS Office Macros

    Dim dt, waterMark, sheetName As String

    Dim wbNew, wbOrig As Worksheet

    Dim c1, c2, c3 As Range

    Dim password, reportName As String

    'Dont Forget  to assign a password and a report name

    password = "somePassword"

    reportName = "Sample Watermarked Report"

    dt = Format(CStr(Now), "mm_dd_yy_hh_mm")

    'the Watermark that will appear on cell A1

    waterMark = "COPY_Report_Generated_On_" & dt

    With ActiveSheet

        'get last col with data

        lCol = .Cells(1, Columns.Count).End(xlToLeft).Column

        'get last row with data

        lRow = .Cells(Rows.Count, 1).End(xlUp).Row

        'get sheet name

        sheetName = .Name

    End With

    Set wbOrig = ThisWorkbook.Worksheets(sheetName)

    Set c1 = wbOrig.Range(Cells(1, 1), Cells(lRow, lCol))

    Set wbNew = Workbooks.Add.Sheets(1)

    With wbNew

        .Name = sheetName

        With .Cells(1, 1)

            .Value = waterMark

            .Font.Bold = True

        End With

        Set c2 = .Range(Cells(2, 1), Cells(lRow + 1, lCol))

        c1.Copy c2

        c2.Locked = False

        Set c3 = .Range(Cells(2, 1), Cells(2, lCol))

        c3.WrapText = False

        c3.Locked = False

        .Columns.AutoFit

        .Protect password:=password

    End With

    'Name for new workbook

    ActiveWorkbook.SaveAs Filename:=reportName & "_" & waterMark