meu código funciona bem como uma macro, mas falha quando eu o executo em um botão

0

Até agora, uma linha está falhando enquanto eu tento separar a macro que funciona bem como uma macro, mas falha quando copio o código para um botão de comando, clique em ...

Sheets(c.Value).Range("a1:i330").Copy Destination:=Sheets("estimating1").Range("a1")

o que estou fazendo errado? Eu tenho as folhas totalmente definidas para a cópia e destino

é uma questão de sintaxe, o comando acima está correto?

aqui está todo o código:

Private Sub CommandButton1_Click()
    'Sub grand10()
    ' grand10 Macro
    'this is a button macro that adds Grand10, SubGrand10, StartTime, EndTime and CheckBalance
    'but it doesnt work
    '
    Dim wkb As Workbook
    Dim wks As Worksheet
    Dim lastcolumn As Long
    Dim lastrow As Long

    MsgBox ("this should take 2-3 seconds per fixture")

    'set timer variables
    Dim StartTime As Double
    Dim SecondsElapsed As Double

    'start the timer
    StartTime = Timer

    'run starttime which put time in a cell on bigmaster
    'Application.Run "starttime" - begin

    'Sheets("bigmaster").Select
    'Sheets("bigmaster").Range("U1").Select
    Sheets("bigmaster").Range("U1").FormulaR1C1 = "=NOW()"
    Sheets("bigmaster").Range("U1").Copy
    'Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

    'Application.Run "starttime" - end

    'clear the temp sheets
    'Sheets("estimating1").Select
    Sheets("estimating1").Range("a1:i330").ClearContents
    'Selection.ClearContents
    'Sheets("Master").Select
    Sheets("Master").Range("A2:P39").ClearContents
    'Selection.ClearContents
    'Sheets("bigmaster").Select
    Sheets("bigmaster").Columns("A:P").ClearContents
    'Selection.ClearContents


    For Each wks In ThisWorkbook.Worksheets

        Set wkb = ThisWorkbook
        Application.DisplayAlerts = False
        Set c = Sheets("formulas").Range("j19:j148").Find(wks.Name, lookat:=xlWhole)
        If Not c Is Nothing Then

            'Pick sheets A,B,C...
            'Sheets(c.Value).Select

            'get the whole range of the estimating sheet excluding change orders
            Sheets(c.Value).Range("a1:i330").Copy 'Destination:=Sheets("estimating1").Range("a1")
            'Selection.Copy
            'Sheets("estimating1").Select

            'paste to A1 as an anchor
            'Sheets("estimating1").Range("a1").Paste
            Sheets("estimating1").Range("a1").Paste
            'Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            ':=False, Transpose:=False
            'ActiveSheet.Paste
            'Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            '    :=False, Transpose:=False

            If Sheets("estimating1").Range("B38") = 0 Then GoTo skiptonext:

            'begin sub macro here, this macro is the steps on the estimating sheet, no looping
            'Application.Run "SubGrand10" - begin
            'Sub SubGrand10()
            '
            ' SubGrand10 Macro
            '
                'Copy Phase and Description
                'Sheets("Formulas").Select
                Sheets("Formulas").Range("D33:E69").Copy
                'Selection.Copy

                'paste to Master C2
                'Sheets("Master").Select
                Sheets("Master").Range("C2").Select
                Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                    :=False, Transpose:=False

                'Copy Hours from Estimating1
                'Sheets("Estimating1").Select
                Sheets("Estimating1").Range("B1:B37").Select
                Application.CutCopyMode = False
                Selection.Copy

                'Paste to Master H2 - Man Hours
                'Sheets("Master").Select
                Sheets("Master").Range("H2").Select
                Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                    :=False, Transpose:=False

                'now move materials to Unit Cost
                Sheets("Master").Range("H33:H38").Select
                Application.CutCopyMode = False
                Selection.Cut Destination:=Range("G33:G38")

                'put hourly rate ($35) in column I
                Sheets("Master").Range("I2").Select
                ActiveCell.FormulaR1C1 = "35"
                Selection.AutoFill Destination:=Range("I2:I32"), Type:=xlFillDefault

                'now paste formula in J2
                Sheets("Master").Range("J2").Select
                ActiveCell.FormulaR1C1 = "=RC[-1]*RC[-2]"
                Selection.AutoFill Destination:=Range("J2:J38"), Type:=xlFillDefault

                'Now paste cost type 1 in labor hours E2
                Sheets("Master").Range("E2").Select
                ActiveCell.FormulaR1C1 = "1"
                Selection.AutoFill Destination:=Range("E2:E38"), Type:=xlFillDefault

                'paste formula for materials
                Sheets("Master").Range("J33").Select
                ActiveCell.FormulaR1C1 = "=RC[-3]*RC[-5]"
                Selection.AutoFill Destination:=Range("J33:J38"), Type:=xlFillDefault

                'now put EA in column F
                Sheets("Master").Range("F2").Select
                ActiveCell.FormulaR1C1 = "EA"
                Selection.AutoFill Destination:=Range("F2:F38"), Type:=xlFillDefault

                'now put exhibit (A,B,C...) in P2
                Sheets("Master").Range("P2").Select
                ActiveCell.Formula = "=Estimating1!F5"
                Selection.Copy
                Range("P2:P38").Select
                Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                    :=False, Transpose:=False

                'now get vlookup from formulas sheet
                'Sheets("Formulas").Select
                Sheets("Formulas").Range("O19").Select
                Application.CutCopyMode = False
                Selection.Copy

                'paste to A2
                'Sheets("Master").Select
                Sheets("Master").Range("A2").Select
                ActiveSheet.Paste
                Application.CutCopyMode = False
                ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[15],lookupABC123,3,FALSE)"
                Selection.AutoFill Destination:=Range("A2:A38"), Type:=xlFillDefault

                'now get name of fixture and put in B2
                Sheets("Master").Range("B2").Select
                ActiveCell.Formula = "=Estimating1!E10"
                Selection.Copy
                Sheets("Master").Range("B2:B38").Select
                Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                    :=False, Transpose:=False

                'put cost type in...
                'Sheets("Formulas").Select
                Sheets("Formulas").Range("O22").Select
                Application.CutCopyMode = False
                Selection.Copy

                'and paste it to L2
                'Sheets("Master").Select
                Sheets("Master").Range("L2").Select
                ActiveSheet.Paste
                Application.CutCopyMode = False
                ActiveCell.FormulaR1C1 = "=IF(RC[-3]>0,1,3)"
                Selection.AutoFill Destination:=Range("L2:L38"), Type:=xlFillDefault

                'now get formula for Grand Total
                'Sheets("Formulas").Select
                Sheets("Formulas").Range("O25").Select

                'paste it to O2
                Selection.Copy
                'Sheets("Master").Select
                Sheets("Master").Range("O2").Select
                ActiveSheet.Paste
                Application.CutCopyMode = False
                ActiveCell.FormulaR1C1 = "=SUM(C[-5])"
                Sheets("Master").Range("O2").Select
                Selection.Copy
                Sheets("Master").Range("O2:O38").Select
                Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                    :=False, Transpose:=False

                'get formula for Phase codes
                'Sheets("Formulas").Select
                Sheets("Formulas").Range("O27").Select
                Application.CutCopyMode = False
                Selection.Copy

                'and paste it to K2
                'Sheets("Master").Select
                Sheets("Master").Range("K2").Select
                ActiveSheet.Paste
                Application.CutCopyMode = False
                ActiveCell.FormulaR1C1 = "=RC[-10]&"".""&RC[-8]"
                Selection.AutoFill Destination:=Range("K2:K38"), Type:=xlFillDefault

                'now get descriptin and paste it to N2
                'Sheets("Formulas").Select
                Sheets("Formulas").Range("O29").Select
                Selection.Copy
                'Sheets("Master").Select
                Sheets("Master").Range("N2").Select
                ActiveSheet.Paste
                Application.CutCopyMode = False
                ActiveCell.FormulaR1C1 = "=RC[-10]&"".""&RC[-12]"
                Selection.AutoFill Destination:=Range("N2:N38"), Type:=xlFillDefault

                'add formula for contract item
                Sheets("Master").Range("M2").Select
                ActiveCell.FormulaR1C1 = "=RC[-12]"
                Selection.AutoFill Destination:=Range("M2:M38"), Type:=xlFillDefault
            'End Sub

            'Application.Run "SubGrand10" - end

            'remove 0 from J column
            'Application.Run "removeJRows" - begin

            Application.ScreenUpdating = False
            'Dim wkb As Workbook
            'Dim wks As Worksheet
            Set wkb = ThisWorkbook
            filtercolumn = "J"
            Set wks = wkb.Sheets("master")
            totalrows = wks.Cells(Rows.Count, "A").End(xlUp).Row
            For j = totalrows To 1 Step -1
                If wks.Cells(j, filtercolumn) = 0 Then
                    wks.Rows(j).Delete
                End If
            Next j
            Application.ScreenUpdating = True

            'Application.Run "removeJRows" - begin

            'now copy master and put it on next available row on bigmaster
                With Sheets("master")
                    .Range("A1").CurrentRegion.Offset(1).Copy
                    If Sheets("BigMaster").Range("A1") = "" Then
                        Sheets("BigMaster").Range("A1").PasteSpecial xlPasteValues
                    Else
                        Sheets("BigMaster").Range("A" & Rows.Count).End(3)(2).PasteSpecial xlPasteValues
                    End If
                End With
            'end sub macro here

            'clear master and estimating1
            Sheets("estimating1").Select
            Range("a2:i320").Select
            Selection.ClearContents
            Sheets("Master").Select
            Range("A2:P39").Select
            Selection.ClearContents
        End If

        'label for skipping pages with a 0 balance
    skiptonext:
    Next
        'subGrand10 all done, finish with the sum of J
        'put the sum of column J here
        Sheets("bigmaster").Select

        Range("R1").Select
        ActiveCell.FormulaR1C1 = "=SUM(C[-8])"

        'put the sum of all estimating sheets here
        Range("R3").Select

        'ActiveCell.FormulaR1C1 = "=SUM(A:OOOOO!R[92]C[-16])"
        ActiveCell.Formula = "=SUM(A:OOOOO!B38)"

        'add statement confiming Balanced, if not then Out of Balance
        Range("R5").Select
        ActiveCell.FormulaR1C1 = "=IF(R[-4]C=R[-2]C,""Balanced"",""Out of Balance"")"

        'color balanced with green, out of balanace with red
        If Range("R5") = "Balanced" _
        Then Range("R5").Interior.ColorIndex = 4 _
        Else Range("R5").Interior.ColorIndex = 3

        'check for balance, if so then msgbox the result
        'Application.Run "CheckBalance" - begin

        If Sheets("bigmaster").Range("R5") = "Balanced" Then MsgBox ("Sheets balanced")

        'Application.Run "CheckBalance" - end

        'put time and elapsed in a cell on bigmaster
        'Application.Run "endtime" - begin

        Sheets("bigmaster").Select
        Range("T1").Select
        ActiveCell.FormulaR1C1 = "Start"
        Range("T2").Select
        ActiveCell.FormulaR1C1 = "End"
        Range("T3").Select
        ActiveCell.FormulaR1C1 = "Elapsed"
        Range("U2").Select
        ActiveCell.FormulaR1C1 = "=NOW()"
        Range("U2").Select
        Selection.Copy
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        Range("U3").Select
        Application.CutCopyMode = False
        ActiveCell.FormulaR1C1 = "=R[-1]C-R[-2]C"

        'Application.Run "endtime" - end

        'prepare msgbox for elapsed time in seconds
        SecondsElapsed = Round(Timer - StartTime, 2)
        MsgBox "This macro ran successfully in " & SecondsElapsed & " seconds", vbInformation

    'End Sub
    End Sub
    
por DanM 18.03.2016 / 18:32

0 respostas