TechRepublic|Forums|Peripheral

Peripheral

General discussion

  • Creator
    Topic
  • May 2, 2009 at 1:53 pm #2203907

    How was this macro button created?

    Locked

    by dougasmith5 · about 13 years, 2 months ago

    Sub AddReport()

    ‘
    ‘ AddReport Macro
    ‘ Macro recorded 2/15/2006 by Roy A. Doyle
    ‘
    Dim wSht As Worksheet
    Dim shtName As String
    Dim reportnum As Integer
    Dim formulaprefix As String

    ‘Application.ScreenUpdating = False

    Set wSht = Worksheets(Worksheets.Count – 0)
    wShtname = wSht.Name

    Worksheets(Worksheets.Count – 0).Copy After:=Worksheets(Worksheets.Count – 0)

    If InStr(1, wShtname, ” “, vbTextCompare) > 0 Then
    formulaprefix = “‘” & wShtname & “‘” & “!”
    Else: formulaprefix = “‘” & wShtname & “‘” & “!”
    End If
    Debug.Print formulaprefix

    Worksheets(Worksheets.Count – 0).Range(“H4”).Formula = “=” & formulaprefix & “H4 + 1”
    Worksheets(Worksheets.Count – 0).Range(“H6”).Formula = “=” & formulaprefix & “H6 + 1”

    Worksheets(Worksheets.Count – 0).Range(“F6”).Formula = “=” & formulaprefix & “G72”

    Worksheets(Worksheets.Count – 0).Range(“A14:H14”).ClearContents
    Worksheets(Worksheets.Count – 0).Range(“A17:H21”).ClearContents
    Worksheets(Worksheets.Count – 0).Range(“A24:B37”).ClearContents
    Worksheets(Worksheets.Count – 0).Range(“C24:H37”).ClearContents
    Worksheets(Worksheets.Count – 0).Range(“F43:F71”).ClearContents

    Worksheets(Worksheets.Count – 0).Range(“G43”).Formula = “=” & formulaprefix & “G43” & “+” & “F43”
    Worksheets(Worksheets.Count – 0).Range(“G44”).Formula = “=” & formulaprefix & “G44” & “+” & “F44”
    Worksheets(Worksheets.Count – 0).Range(“G45”).Formula = “=” & formulaprefix & “G45” & “+” & “F45”
    Worksheets(Worksheets.Count – 0).Range(“G46”).Formula = “=” & formulaprefix & “G46” & “+” & “F46”
    Worksheets(Worksheets.Count – 0).Range(“G47”).Formula = “=” & formulaprefix & “G47” & “+” & “F47”
    Worksheets(Worksheets.Count – 0).Range(“G48”).Formula = “=” & formulaprefix & “G48” & “+” & “F48”
    Worksheets(Worksheets.Count – 0).Range(“G49”).Formula = “=” & formulaprefix & “G49” & “+” & “F49”
    Worksheets(Worksheets.Count – 0).Range(“G50”).Formula = “=” & formulaprefix & “G50” & “+” & “F50”
    Worksheets(Worksheets.Count – 0).Range(“G51”).Formula = “=” & formulaprefix & “G51” & “+” & “F51”
    Worksheets(Worksheets.Count – 0).Range(“G52”).Formula = “=” & formulaprefix & “G52” & “+” & “F52”
    Worksheets(Worksheets.Count – 0).Range(“G53”).Formula = “=” & formulaprefix & “G53” & “+” & “F53”
    Worksheets(Worksheets.Count – 0).Range(“G54”).Formula = “=” & formulaprefix & “G54” & “+” & “F54”
    Worksheets(Worksheets.Count – 0).Range(“G55”).Formula = “=” & formulaprefix & “G55” & “+” & “F55”
    Worksheets(Worksheets.Count – 0).Range(“G56”).Formula = “=” & formulaprefix & “G56” & “+” & “F56”
    Worksheets(Worksheets.Count – 0).Range(“G57”).Formula = “=” & formulaprefix & “G57” & “+” & “F57”
    Worksheets(Worksheets.Count – 0).Range(“G58”).Formula = “=” & formulaprefix & “G58” & “+” & “F58”
    Worksheets(Worksheets.Count – 0).Range(“G59”).Formula = “=” & formulaprefix & “G59” & “+” & “F59”
    Worksheets(Worksheets.Count – 0).Range(“G60”).Formula = “=” & formulaprefix & “G60” & “+” & “F60”
    Worksheets(Worksheets.Count – 0).Range(“G61”).Formula = “=” & formulaprefix & “G61” & “+” & “F61”
    Worksheets(Worksheets.Count – 0).Range(“G62”).Formula = “=” & formulaprefix & “G62” & “+” & “F62”
    Worksheets(Worksheets.Count – 0).Range(“G63”).Formula = “=” & formulaprefix & “G63” & “+” & “F63”
    Worksheets(Worksheets.Count – 0).Range(“G64”).Formula = “=” & formulaprefix & “G64” & “+” & “F64”
    Worksheets(Worksheets.Count – 0).Range(“G65”).Formula = “=” & formulaprefix & “G65” & “+” & “F65”
    Worksheets(Worksheets.Count – 0).Range(“G66”).Formula = “=” & formulaprefix & “G66” & “+” & “F66”
    Worksheets(Worksheets.Count – 0).Range(“G67”).Formula = “=” & formulaprefix & “G67” & “+” & “F67”
    Worksheets(Worksheets.Count – 0).Range(“G68”).Formula = “=” & formulaprefix & “G68” & “+” & “F68”
    Worksheets(Worksheets.Count – 0).Range(“G69”).Formula = “=” & formulaprefix & “G69” & “+” & “F69”
    Worksheets(Worksheets.Count – 0).Range(“G70”).Formula = “=” & formulaprefix & “G70” & “+” & “F70”
    Worksheets(Worksheets.Count – 0).Range(“G71”).Formula = “=” & formulaprefix & “G71” & “+” & “F71”

    reportnum = Worksheets(Worksheets.Count).Range(“H4″)

    Worksheets(Worksheets.Count – 0).Name = ” Day ” & reportnum

    End Sub

    Topic is locked This conversation is currently closed to new comments.
  • Creator
    Topic

All Comments