macro not working as expected, need someone good at debugging macro's

No error message as the routine runs through the paces normallly. so no error's at all. which is why i included the workbook, its the only way to see what is not happening. when you look at exhibit A (fixture 001) it correctly puts all the info on big master, but then it fails on the sheets listed in the OP.

so can anyone help me?

ok, ill wait. i just cant figure out why its not working. most of the tabs are good. but the ones that dont work. i just dont know.

thanks for all your help.

so one place the macro fails is during this code:

With Sheets("master")
    lastrow = .Cells(Rows.Count, 1).End(xlUp).Row
    lastcolumn = .Cells(5, Columns.Count).End(xlToLeft).Column
    .Range(.Cells(2, "A"), .Cells(lastrow, lastcolumn)).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

what this section of the code does is to take all of the rows from Master and insert it into BigMaster. but it only copies one column of data, instead of the whole range.

any ideas?

so one place the macro fails is during this code:

With Sheets("master")
    lastrow = .Cells(Rows.Count, 1).End(xlUp).Row
    lastcolumn = .Cells(5, Columns.Count).End(xlToLeft).Column
    .Range(.Cells(2, "A"), .Cells(lastrow, lastcolumn)).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

what this section of the code does is to take all of the rows from Master and insert it into BigMaster. but it only copies one column of data (this shows up in certain pages), instead of the whole range. i can list the pages that fail this way if necessary.

any ideas?

I made the change that you requested and still the same problem with this part of the macro. it only copies one column and not the entire range.

Hi dmcgov,
try this

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

ok so the next part of the macro needs to copy formulas correctly.

here is the offending code (there is a lot of it). i will detail what fails during the macro running.

        Set c = Sheets("formulas").Range("j19:j148").Find(ws.Name, lookat:=xlWhole)
        If Not c Is Nothing Then
            Sheets(c.Value).Select
            Range("b2:h329").Select
            Selection.Copy
            Sheets("estimating1").Select
            Range("c2").Select
            'Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            ':=False, Transpose:=False
            ActiveSheet.Paste
            'Application.Run "'Estimate Test.xlsm'!dummy1"
            'put dummy1 here
                Sheets("Estimating1").Select
                ActiveWindow.SmallScroll Down:=-189
                Range("C58:D232").Select
                Selection.Copy
                Sheets("Master").Select
                Range("C2").Select
                Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                    :=False, Transpose:=False
                Sheets("Estimating1").Select
                ActiveWindow.SmallScroll Down:=-165
                Range("G58:I232").Select
                Application.CutCopyMode = False
                Selection.Copy
                Sheets("Master").Select
                Range("H2").Select
                Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                    :=False, Transpose:=False
                Sheets("Formulas").Select
                ActiveWindow.SmallScroll Down:=-48
                Range("O8").Select
                Application.CutCopyMode = False
                Selection.Copy
                Sheets("Master").Select
                Range("G2").Select
                ActiveSheet.Paste
                ActiveSheet.Paste
                Application.CutCopyMode = False
                ActiveCell.FormulaR1C1 = "=IF(RC[-4]=0,0,IF(RC[1]=0,0,IF(RC[3]=0,0,RC[1])))"
                Range("G3").Select
                ActiveWindow.SmallScroll Down:=-6
                Range("G2").Select
                Selection.AutoFill Destination:=Range("G2:G180"), Type:=xlFillDefault
                Range("G2:G180").Select
                ActiveWindow.SmallScroll Down:=-171
                Selection.Copy
                Range("A2").Select
                Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                    :=False, Transpose:=False
                Application.CutCopyMode = False
            'Application.Run "'Estimate Test.xlsm'!removeGRows"
            'put removeGRows here
                'Application.ScreenUpdating = False
                filtercolumn = "G"
                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 "'Estimate Test.xlsm'!dummy2"
            'put dummy2 here
                Selection.ClearContents
                Range("G2:G241").Select
                Selection.ClearContents
                Sheets("Formulas").Select
                Range("K11:O15").Select
                Selection.Copy
                Sheets("Master").Select
                ActiveWindow.SmallScroll Down:=174
                Range("C196").Select
                ActiveSheet.Paste
                Range("G196").Select
                Application.CutCopyMode = False
                ActiveCell.FormulaR1C1 = "='Estimate Test.xlsm'!sheetgoods"
                Range("G197").Select
                ActiveCell.FormulaR1C1 = "='Estimate Test.xlsm'!solidstock"
                Range("G198").Select
                ActiveCell.FormulaR1C1 = "='Estimate Test.xlsm'!preorderedwood"
                Range("G199").Select
                ActiveCell.FormulaR1C1 = "='Estimate Test.xlsm'!hardware"
                Range("G200").Select
                ActiveCell.FormulaR1C1 = "='Estimate Test.xlsm'!finishing"
                Range("G201").Select
                ActiveWindow.SmallScroll Down:=-183
                Sheets("Formulas").Select
                Range("O19").Select
                Sheets("Master").Select
                Range("P2").Select
                ActiveCell.FormulaR1C1 = "=Estimating1!R[3]C[-10]"
                Range("P3").Select
                ActiveWindow.SmallScroll Down:=-87
                Range("P2").Select
                Selection.Copy
                Range("P2:P200").Select
                Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                    :=False, Transpose:=False
                Sheets("Formulas").Select
                ActiveWindow.SmallScroll Down:=3
                Range("O19").Select
                Application.CutCopyMode = False
                Selection.Copy
                Sheets("Master").Select
                Range("A2").Select
                ActiveSheet.Paste
                ActiveSheet.Paste
                Application.CutCopyMode = False
                ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[15],lookupABC123,3,FALSE)"
                Range("A2").Select
                Selection.AutoFill Destination:=Range("A2:A200"), Type:=xlFillDefault
                Range("A2:A200").Select
                ActiveWindow.SmallScroll Down:=-252
                Sheets("Formulas").Select
                Range("O20").Select
                Selection.Copy
                Sheets("Master").Select
                Range("B2").Select
                ActiveSheet.Paste
                ActiveSheet.Paste
                Application.CutCopyMode = False
                ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-1],lookupROOMS,2,FALSE)"
                Range("B2").Select
                Selection.AutoFill Destination:=Range("B2:B200"), Type:=xlFillDefault
                Range("B2:B200").Select
                ActiveWindow.SmallScroll Down:=-222
                Sheets("Formulas").Select
                Range("O22").Select
                Selection.Copy
                Sheets("Master").Select
                Range("L2").Select
                ActiveSheet.Paste
                ActiveSheet.Paste
                Application.CutCopyMode = False
                ActiveCell.FormulaR1C1 = "=IF(RC[-3]>0,1,3)"
                Range("L2").Select
                Selection.AutoFill Destination:=Range("L2:L200"), Type:=xlFillDefault
                Range("L2:L200").Select
                ActiveWindow.SmallScroll Down:=-285
                Sheets("Master").Select
                Range("O2").Select
                ActiveCell.FormulaR1C1 = "=SUM(C[-5])"
                Range("O2").Select
                Selection.Copy
                Range("O3:O200").Select
                ActiveSheet.Paste
                Sheets("Formulas").Select
                ActiveWindow.SmallScroll Down:=6
                Range("O27").Select
                Application.CutCopyMode = False
                Selection.Copy
                Sheets("Master").Select
                Range("K2").Select
                ActiveSheet.Paste
                ActiveSheet.Paste
                Application.CutCopyMode = False
                ActiveCell.FormulaR1C1 = "=RC[-8]&"".""&RC[-10]"
                Range("K2").Select
                Selection.AutoFill Destination:=Range("K2:K200"), Type:=xlFillDefault
                Range("K2:K200").Select
                ActiveWindow.SmallScroll Down:=-228
                Sheets("Formulas").Select
                Range("O29").Select
                Selection.Copy
                Sheets("Master").Select
                Range("N2").Select
                ActiveSheet.Paste
                ActiveSheet.Paste
                Application.CutCopyMode = False
                ActiveCell.FormulaR1C1 = "=RC[-10]&"".""&RC[-12]"
                Range("N2").Select
                Selection.AutoFill Destination:=Range("N2:N200"), Type:=xlFillDefault
                Range("N2:N200").Select
                ActiveWindow.SmallScroll Down:=-309
                Sheets("Formulas").Select
                ActiveWindow.SmallScroll Down:=6
                Range("O31").Select
                Selection.Copy
                Sheets("Master").Select
                Range("J2").Select
                ActiveSheet.Paste
                ActiveSheet.Paste
                Application.CutCopyMode = False
                ActiveCell.FormulaR1C1 = "=(IF(RC[-3]>0,RC[-3],RC[-1]*RC[-2]))"
                Range("J2").Select
                Selection.AutoFill Destination:=Range("J2:J200"), Type:=xlFillDefault
                Range("J2:J200").Select
                ActiveWindow.SmallScroll Down:=-174
                Range("M2").Select
                ActiveCell.FormulaR1C1 = "=RC[-12]"
                Range("M2").Select
                Selection.AutoFill Destination:=Range("M2:M200"), Type:=xlFillDefault
                Range("M2:M200").Select
                ActiveWindow.SmallScroll Down:=-228
                Range("E2").Select
                ActiveCell.FormulaR1C1 = "1"
                Range("F2").Select
                ActiveCell.FormulaR1C1 = "EA"
                Range("E2:F2").Select
                Selection.Copy
                Range("E3:E200").Select
                ActiveWindow.SmallScroll Down:=-168
                ActiveSheet.Paste
                Application.CutCopyMode = False

when it copies the formulas from the formulas tab, it fails on these lines of code

ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[15],lookupABC123,3,FALSE)"
and
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-1],lookupROOMS,2,FALSE)"

instead of giving the fixture number and name, it comes in as #N/A for both columns.

any ideas?

nilem, gold star for you. that fixed one of the problems with the macro. now on to the next one. i will detail that in the post below.

What is lookupABC123 and lookupROOMS - the named range? variable?

its found on the formulas sheet. its a named range. as far as i can tell, it should pull the names and numbers over like it does on most of the sheets.

thanks for looking at this.

I tested this code in your file

Sub test()
Sheets("Master").Range("P2") = "Exhibit D"
Sheets("Master").Range("A2").FormulaR1C1 = "=iferror(VLOOKUP(RC[15],lookupABC123,3,FALSE),"""")"
'or
'Sheets("Master").Range("A2").FormulaR1C1 = "=iferror(VLOOKUP(RC[15],lookupABC123,3,FALSE),""Not found"")"
End Sub

and it works for me

my general thinking is that there must be something corrupt on the individual sheets though i cant point out where this would be. the macro runs fine on 75% of the sheets. so if i take sheet z and copy it to zz, then it works just fine. if i restore the old zz, then it fails. not sure of how to proceed.

Hi dmcgov,
I do not really understand the essence of your macro, but try this

Sub GrandFinale()
Dim ws As Worksheet, c As Range, j As Long
Application.ScreenUpdating = False

For Each ws In ThisWorkbook.Worksheets
    Set c = Sheets("formulas").Range("j19:j148").Find(ws.Name, lookat:=xlWhole)
    If Not c Is Nothing Then
        Sheets(c.Value).Range("b2:h329").Copy Sheets("estimating1").Range("c2")

        With Sheets("master")

            Sheets("Estimating1").Range("C58:D232").Copy
            .Range("C2").PasteSpecial Paste:=xlPasteValues

            Sheets("Estimating1").Range("G58:I232").Copy
            .Range("H2").PasteSpecial Paste:=xlPasteValues
                                    
            .Range("G2:G180").FormulaR1C1 = "=IF(RC[-4]=0,0,IF(RC[1]=0,0,IF(RC[3]=0,0,RC[1])))"
            .Range("A2:A180").Value = .Range("G2:G180").Value

            For j = .Cells(Rows.Count, "A").End(xlUp).Row To 1 Step -1
                If .Cells(j, "G") = 0 Then .Rows(j).Delete
            Next j
            .Range("G2:G241").ClearContents

            .Range("P2:P200").Value = Sheets("Estimating1").Range("F5").Value
            .Range("A2:A200").FormulaR1C1 = "=IFERROR(VLOOKUP(RC[15],lookupABC123,3,FALSE),"""")"
            .Range("B2:B200").FormulaR1C1 = "=VLOOKUP(RC[-1],lookupROOMS,2,FALSE)"
            .Range("L2:L200").FormulaR1C1 = "=IF(RC[-3]>0,1,3)"
            .Range("O2:O200").FormulaR1C1 = "=SUM(C[-5])"
            .Range("K2:K200").FormulaR1C1 = "=RC[-8]&"".""&RC[-10]"
            .Range("N2:N200").FormulaR1C1 = "=RC[-10]&"".""&RC[-12]"
            .Range("J2:J200").FormulaR1C1 = "=(IF(RC[-3]>0,RC[-3],RC[-1]*RC[-2]))"
            .Range("M2:M200").FormulaR1C1 = "=RC[-12]"

            .Range("E2:E200").Value = "1"
            .Range("F2:F200").Value = "EA"

            For j = .Cells(Rows.Count, "A").End(xlUp).Row To 1 Step -1
                If .Cells(j, "J") = 0 Then .Rows(j).Delete
            Next j

            .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
            Application.CutCopyMode = False
        End With

    End If
Next ws
Application.ScreenUpdating = True

End Sub

Have you observed Falkon9 landing in Florida? Probably, it is amazing.

@nilem, yup, same problem, either blank in column A or NA in column B. any thoughts?

Look at the Sheet "W" cell D5. There is space (gap) at the end of the word "Exhibit W", remove this space. Do the same thing with a sheet "AA" and "HH"

Wow, glad you have a critical eye! Gold Stars for you... i'll be marking this as solved.