jason

I am looking for help with a macro.  I have written to make a new day for our log sheets.  the macro works fine sets up a new day saves the old day and new day.  What I am trying to do is then disable, turn off or delete the button from the old day so that it can not be used again to overwrite the new days data.  this is a shared file as well which makes it a little more difficult because I have done it with the file sharing turned off.

 

this bellow cuts and pastes all of the midnight numbers to the bottom of the sheets and makes a new day workbook based off the date

 

Sub Macro1()

‘ Macro1 Macro

 

    ActiveWorkbook.Save

    Sheets(“Generation Sheet “).Select

    Range(“B37”).Select

    Selection.Copy

    Range(“B38”).Select

    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

        :=False, Transpose:=False

    Range(“D37”).Select

    Application.CutCopyMode = False

    Selection.Copy

    Range(“D38”).Select

    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

        :=False, Transpose:=False

    Range(“G37”).Select

    Application.CutCopyMode = False

    Selection.Copy

    Range(“G38”).Select

    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

        :=False, Transpose:=False

    Range(“I37”).Select

    Application.CutCopyMode = False

    Selection.Copy

    Range(“I38”).Select

    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

        :=False, Transpose:=False

    Range(“L37”).Select

    Application.CutCopyMode = False

    Selection.Copy

    Range(“L38”).Select

    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

        :=False, Transpose:=False

    Range(“N37”).Select

    Application.CutCopyMode = False

    Selection.Copy

    Range(“N38”).Select

    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

        :=False, Transpose:=False

    Range(“P37”).Select

    Application.CutCopyMode = False

    Selection.Copy

    Range(“P38”).Select

    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

        :=False, Transpose:=False

    Range(“S37”).Select

    Application.CutCopyMode = False

    Selection.Copy

    Range(“S38”).Select

    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

        :=False, Transpose:=False

    Range(“U37”).Select

    Application.CutCopyMode = False

    Selection.Copy

    Range(“U38”).Select

    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

        :=False, Transpose:=False

    Range(“Y37”).Select

    Application.CutCopyMode = False

    Selection.Copy

    Range(“Y38”).Select

    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

        :=False, Transpose:=False

    Range(“AA37”).Select

    Application.CutCopyMode = False

    Selection.Copy

    Range(“AA38”).Select

    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

        :=False, Transpose:=False

    Range(“B10:B17”).Select

    Application.CutCopyMode = False

    Selection.ClearContents

    Range(“B19:B26”).Select

    Selection.ClearContents

    Range(“B28:B35”).Select

    Selection.ClearContents

    Range(“D10:D17”).Select

    Selection.ClearContents

    Range(“D19:D26”).Select

    Selection.ClearContents

    Range(“D28:D35”).Select

    Selection.ClearContents

    Range(“G10:G17”).Select

    Selection.ClearContents

    Range(“G19:G26”).Select

    Selection.ClearContents

    Range(“G28:G35”).Select

    Selection.ClearContents

    Range(“I10:I17”).Select

    Selection.ClearContents

    Range(“I19:I26”).Select

    Selection.ClearContents

    Range(“I28:I35”).Select

    Selection.ClearContents

    Range(“L10:L17”).Select

    Selection.ClearContents

    Range(“L19:L26”).Select

    Selection.ClearContents

    Range(“L28:L35”).Select

    Selection.ClearContents

    Range(“N10:N17”).Select

    Selection.ClearContents

    Range(“N19:N26”).Select

    Selection.ClearContents

    Range(“N28:N35”).Select

    Selection.ClearContents

    Range(“P10:P17”).Select

    Selection.ClearContents

    Range(“P19:P26”).Select

    Selection.ClearContents

    Range(“P28:P35”).Select

    Selection.ClearContents

    Range(“S10:S17”).Select

    Selection.ClearContents

    Range(“S19:S26”).Select

    Selection.ClearContents

    Range(“S28:S35”).Select

    Selection.ClearContents

    Range(“U10:U17”).Select

    Selection.ClearContents

    Range(“U19:U26”).Select

    Selection.ClearContents

    Range(“U28:U35”).Select

    Selection.ClearContents

    Range(“Y10:Y17”).Select

    Selection.ClearContents

    Range(“Y19:Y26”).Select

    Selection.ClearContents

    Range(“Y28:Y35”).Select

    Selection.ClearContents

    Range(“AA10:AA17”).Select

    Selection.ClearContents

    Range(“AA19:AA26”).Select

    Selection.ClearContents

    Range(“AA28:AA35”).Select

    Selection.ClearContents

    Range(“X41:Z42”).Select

    ActiveCell.FormulaR1C1 = “=R[-3]C[-2]+1”

    Range(“X41:Z42”).Select

    Selection.Copy

    Range(“V38:X39”).Select

    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

        :=False, Transpose:=False

    Range(“X41:Z42”).Select

    Application.CutCopyMode = False

    Selection.ClearContents

    Sheets(“Unit RMVAs”).Select

    Range(“B5:D28”).Select

    Selection.ClearContents

    Sheets(“Unit 2 Int Readings”).Select

    Range(“B4”).Select

    Selection.Copy

    Range(“B7”).Select

    ActiveSheet.Paste

    Range(“B4:B6”).Select

    Application.CutCopyMode = False

    Selection.ClearContents

    Range(“E4”).Select

    Selection.Copy

    Range(“E7”).Select

    ActiveSheet.Paste

    Range(“E4:E6”).Select

    Application.CutCopyMode = False

    Selection.ClearContents

    Range(“H4”).Select

    Selection.Copy

    Range(“H7”).Select

    ActiveSheet.Paste

    Range(“H4:H6”).Select

    Application.CutCopyMode = False

    Selection.ClearContents

    Range(“B12”).Select

    Selection.Copy

    Range(“B16”).Select

    ActiveSheet.Paste

    Range(“B12:B15”).Select

    Application.CutCopyMode = False

    Selection.ClearContents

    Range(“D12”).Select

    Selection.Copy

    Range(“D16”).Select

    ActiveSheet.Paste

    Range(“D12:D13”).Select

    Application.CutCopyMode = False

    Selection.ClearContents

    Range(“D15”).Select

    Selection.ClearContents

    Range(“H20:H25”).Select

    Selection.Copy

    Range(“E20”).Select

    ActiveSheet.Paste

    Range(“F20:H25”).Select

    Application.CutCopyMode = False

    Selection.ClearContents

    Range(“B21”).Select

    Selection.Copy

    Range(“B24”).Select

    ActiveSheet.Paste

    Range(“B21:B23”).Select

    Application.CutCopyMode = False

    Selection.ClearContents

    Sheets(“Unit 3 Int Readings”).Select

    Range(“B4”).Select

    Selection.Copy

    Range(“B9”).Select

    ActiveSheet.Paste

    Range(“B4”).Select

    Application.CutCopyMode = False

    Selection.ClearContents

    Range(“B5”).Select

    Selection.ClearContents

    Range(“B7”).Select

    Selection.ClearContents

    Range(“C4”).Select

    Selection.Copy

    Range(“C9”).Select

    ActiveSheet.Paste

    Range(“C4”).Select

    Application.CutCopyMode = False

    Selection.ClearContents

    Range(“C5”).Select

    Selection.ClearContents

    Range(“C7”).Select

    Selection.ClearContents

    Range(“D4”).Select

    Selection.Copy

    Range(“D9”).Select

    ActiveSheet.Paste

    Range(“D4”).Select

    Application.CutCopyMode = False

    Selection.ClearContents

    Range(“D5”).Select

    Selection.ClearContents

    Range(“D7”).Select

    Selection.ClearContents

    Range(“E4”).Select

    Selection.Copy

    Range(“E9”).Select

    ActiveSheet.Paste

    Range(“E4”).Select

    Application.CutCopyMode = False

    Selection.ClearContents

    Range(“E5”).Select

    Selection.ClearContents

    Range(“E7”).Select

    Selection.ClearContents

    Range(“F4”).Select

    Selection.Copy

    Range(“F9”).Select

    ActiveSheet.Paste

    Range(“F4”).Select

    Application.CutCopyMode = False

    Selection.ClearContents

    Range(“F5”).Select

    Selection.ClearContents

    Range(“F7”).Select

    Selection.ClearContents

    Range(“G4”).Select

    Selection.Copy

    Range(“G9”).Select

    ActiveSheet.Paste

    Range(“G4”).Select

    Application.CutCopyMode = False

    Selection.ClearContents

    Range(“G5”).Select

    Selection.ClearContents

    Range(“G7”).Select

    Selection.ClearContents

    Range(“B15”).Select

    Selection.Copy

    Range(“B19”).Select

    ActiveSheet.Paste

    Range(“B15:B18”).Select

    Application.CutCopyMode = False

    Selection.ClearContents

    Range(“D15”).Select

    Selection.Copy

    Range(“D19”).Select

    ActiveSheet.Paste

    Range(“D15”).Select

    Application.CutCopyMode = False

    Selection.ClearContents

    Range(“D16”).Select

    Selection.ClearContents

    Range(“D18”).Select

    Selection.ClearContents

    Sheets(“Unit 4 Int Readings”).Select

    Range(“B5”).Select

    Selection.Copy

    Range(“B8”).Select

    ActiveSheet.Paste

    Range(“B5:B7”).Select

    Application.CutCopyMode = False

    Selection.ClearContents

    Range(“D5”).Select

    Selection.Copy

    Range(“D8”).Select

    ActiveSheet.Paste

    Range(“D5:D7”).Select

    Application.CutCopyMode = False

    Selection.ClearContents

    Range(“B14”).Select

    Selection.Copy

    Range(“B18”).Select

    ActiveSheet.Paste

    Range(“B14:B17”).Select

    Application.CutCopyMode = False

    Selection.ClearContents

    Range(“D14”).Select

    Selection.Copy

    Range(“D18”).Select

    ActiveSheet.Paste

    Range(“D14:D15”).Select

    Application.CutCopyMode = False

    Selection.ClearContents

    Range(“D17”).Select

    Selection.ClearContents

    Range(“G14”).Select

    Selection.Copy

    Range(“G18”).Select

    ActiveSheet.Paste

    Range(“G14:G15”).Select

    Application.CutCopyMode = False

    Selection.ClearContents

    Range(“G17”).Select

    Selection.ClearContents

    Sheets(“GenConn Gen”).Select

    Range(“F13”).Select

    Selection.Copy

    Range(“F14”).Select

    ActiveSheet.Paste

    Range(“F13”).Select

    Application.CutCopyMode = False

    Selection.ClearContents

    Range(“F18”).Select

    Selection.Copy

    Range(“F19”).Select

    ActiveSheet.Paste

    Range(“F18”).Select

    Application.CutCopyMode = False

    Selection.ClearContents

    Range(“F23”).Select

    Selection.Copy

    Range(“F24”).Select

    ActiveSheet.Paste

    Range(“F23”).Select

    Application.CutCopyMode = False

    Selection.ClearContents

    Range(“F30”).Select

    Selection.Copy

    Range(“F31”).Select

    ActiveSheet.Paste

    Range(“F30”).Select

    Application.CutCopyMode = False

    Selection.ClearContents

    Range(“F35”).Select

    Selection.Copy

    Range(“F36”).Select

    ActiveSheet.Paste

    Range(“F35”).Select

    Application.CutCopyMode = False

    Selection.ClearContents

    Range(“F40”).Select

    Selection.Copy

    Range(“F41”).Select

    ActiveSheet.Paste

    Range(“F40”).Select

    Application.CutCopyMode = False

    Selection.ClearContents

    Sheets(“Generation Sheet “).Select

        On Error Resume Next

    Dim wbk As Workbook

    Dim wbkName As String

    Dim path As String

    path = “\MIDDLETOWNProjectsOperationsGeneration Sheettest logs”

    Set wbk = Workbooks(1)

    wbkName = wkb.Name

    wkbName = Sheet1.Range(“v38”).Text

    wbk.SaveAs Filename:=path & wkbName

 

End Sub

 

 

By: jason e krummel

Leave a Reply

Your email address will not be published. Required fields are marked *