VBA Error

[Lelewiwi]

Hello helpful peeps! I have a massive macro that is run every month on files that are received from an outside source. It worked last month, but this month I received an error. The file received has not changed, nor has the code. I don't get it. Any help is appreciated! The line where the code is red.

    'Clean and set up EB Other Locals Adjustment workbook
        Dim lr8 As Long
        Dim lr9 As Long
        Sourcewb2.Activate
        Columns("D").Delete

    'Filter, copy, paste
        lr8 = Cells(Rows.count, "A").End(xlUp).Row
        Columns("K:K").AutoFilter
        ActiveSheet.Range("$A$1:$P$" & lr8).AutoFilter Field:=11, Criteria1:="<0"
        Range("$A$1:$P$" & Cells(Rows.count, "A").End(xlUp).Row).SpecialCells(xlCellTypeVisible).Copy
        ThisWorkbook.Activate
        Sheets("Eastbay Adjustments").Select
        lr9 = Cells(Rows.count, "A").End(xlUp).Row
        Cells(Rows.count, 1).End(xlUp).Offset(2, 0).PasteSpecial xlPasteValues
        Application.CutCopyMode = False

 

[Lelewiwi]

Everything is correct as far as I can tell, no merged cells, no filters, no protection. This is the entire code for this Macro:

Sub Macro12()

Application.ScreenUpdating = False

'Import Eastbay Local 70 Adjustment data

    Dim EB70 As String
    Dim Sourcewb As Workbook
    Dim Sourcewb2 As Workbook
    Dim Sourcews As Worksheet

    'Choose Eastbay Local 70 Adjustments file
        MsgBox "Please select Eastbay Local 70 Adjustments file.", vbOKOnly
        Dim Dbox As FileDialog
        Dim File_Path As String
        Set Dbox = Application.FileDialog(msoFileDialogFilePicker)
        Dbox.Title = "Choose and Open" & FileType
        Dbox.Filters.Clear
        Dbox.Show
        If Dbox.SelectedItems.count = 1 Then File_Path = Dbox.SelectedItems(1)
        Set Sourcewb = Workbooks.Open(Filename:=File_Path)
        Set Sourcews = Sourcewb.Sheets(1)

    'Create Eastbay Adjustments Consolidator sheet
        ThisWorkbook.Activate
        Sheets("Split Request").Select
        Sheets.Add After:=ActiveSheet
        Sheets("Sheet12").Name = "EB Adj Cons"

    'Set up Consolidator sheet
        Range("A1").FormulaR1C1 = "Account"
        Range("B1").FormulaR1C1 = "Bill period"
        Range("C1").FormulaR1C1 = "Adjustments"
        Range("E1").FormulaR1C1 = "Consolidated Account"
        Range("E2").FormulaR1C1 = "129542"
        Range("E3").FormulaR1C1 = "129543"
        Range("E4").FormulaR1C1 = "129544"
        Range("E6").FormulaR1C1 = "Cons Other Lcl Acct"
        Range("E7").FormulaR1C1 = "129542"
        Range("E8").FormulaR1C1 = "129543"
        Range("E9").FormulaR1C1 = "129544"
        Range("E11").FormulaR1C1 = "Grand Totals"
        Range("E12").FormulaR1C1 = "129542"
        Range("E13").FormulaR1C1 = "129543"
        Range("E14").FormulaR1C1 = "129544"
        Range("F1").FormulaR1C1 = "Consolidated Totals"
        Range("F6").FormulaR1C1 = "Cons Totals"
        Range("H1").FormulaR1C1 = "Account"
        Range("I1").FormulaR1C1 = "Bill Period"
        Range("J1").FormulaR1C1 = "Adjustments"

        Range("A:A,H:H").ColumnWidth = 8
        Range("B:B,I:I").ColumnWidth = 10
        Range("C:C,J:J").ColumnWidth = 13
        Range("D:D,G:G").ColumnWidth = 2
        Range("E:F").ColumnWidth = 20

    'Import Local 70 adjustment amounts
        Sourcewb.Activate
            Sheets(1).Select
        Dim lr As Long

        'Prep Source for import
            Columns("D:D").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
            Range("D2").FormulaR1C1 = "=CONCAT(RC[-2],""/"",RC[-1])"
            Range("D2").AutoFill Destination:=Range("D2:D10000")

        'Copy and paste account numbers
            lr = Cells(Rows.count, "A").End(xlUp).Row
            Range("$A$2:$A$" & Cells(Rows.count, "A").End(xlUp).Row).Copy
            ThisWorkbook.Activate
            Sheets("EB Adj Cons").Select
            ActiveSheet.Range("A2").PasteSpecial xlPasteValues
            Application.CutCopyMode = False

        'Copy and paste bill period
            Sourcewb.Activate
            lr = Cells(Rows.count, "A").End(xlUp).Row
            Range("$D$2:$D$" & Cells(Rows.count, "A").End(xlUp).Row).Copy
            ThisWorkbook.Activate
            Sheets("EB Adj Cons").Select
            ActiveSheet.Range("B2").PasteSpecial xlPasteValues
            Application.CutCopyMode = False

        'Copy and paste contributions
            Sourcewb.Activate
            lr = Cells(Rows.count, "A").End(xlUp).Row
            Range("$L$2:$L$" & Cells(Rows.count, "A").End(xlUp).Row).Copy
            ThisWorkbook.Activate
            Sheets("EB Adj Cons").Select
            ActiveSheet.Range("C2").PasteSpecial xlPasteValues
            Application.CutCopyMode = False

        'Consolidate Local 70 total amounts per account # (first split)
            Range("F2").Formula2R1C1 = "=SUM(IF(EXACT(C[-5],RC[-1]),C[-3],""""))"
            Range("F2").AutoFill Destination:=Range("F2:F4"), Type:=xlFillDefault

    'Choose Eastbay Other Locals Adjustments file
        MsgBox "Please select Eastbay Other Locals Adjustments file.", vbOKOnly
        Dim Dbox2 As FileDialog
        Dim File_Path2 As String
        Set Dbox2 = Application.FileDialog(msoFileDialogFilePicker)
        Dbox2.Title = "Choose and Open" & FileType
        Dbox2.Filters.Clear
        Dbox2.Show
        If Dbox2.SelectedItems.count = 1 Then File_Path2 = Dbox2.SelectedItems(1)
        Set Sourcewb2 = Workbooks.Open(Filename:=File_Path2)
        Set Sourcews2 = Sourcewb2.Sheets(1)

    'Import Other Locals adjustment amounts
        Sourcewb2.Activate
            Sheets(1).Select
        Dim lr2 As Long

        'Prep Source for import
            Columns("D:D").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
            Range("D2").FormulaR1C1 = "=CONCAT(RC[-2],""/"",RC[-1])"
            Range("D2").AutoFill Destination:=Range("D2:D10000")

        'Copy and paste account numbers
            lr2 = Cells(Rows.count, "A").End(xlUp).Row
            Range("$A$2:$A$" & Cells(Rows.count, "A").End(xlUp).Row).Copy
            ThisWorkbook.Activate
            Sheets("EB Adj Cons").Select
            ActiveSheet.Range("H2").PasteSpecial xlPasteValues
            Application.CutCopyMode = False

        'Copy and paste bill period
            Sourcewb2.Activate
            lr2 = Cells(Rows.count, "A").End(xlUp).Row
            Range("$D$2:$D$" & Cells(Rows.count, "A").End(xlUp).Row).Copy
            ThisWorkbook.Activate
            Sheets("EB Adj Cons").Select
            ActiveSheet.Range("I2").PasteSpecial xlPasteValues
            Application.CutCopyMode = False

        'Copy and paste contributions
            Sourcewb2.Activate
            lr2 = Cells(Rows.count, "A").End(xlUp).Row
            Range("$L$2:$L$" & Cells(Rows.count, "A").End(xlUp).Row).Copy
            ThisWorkbook.Activate
            Sheets("EB Adj Cons").Select
            ActiveSheet.Range("J2").PasteSpecial xlPasteValues
            Application.CutCopyMode = False

        'Consolidate Other Locals total amounts per account # (first split)
            Range("F7").Formula2R1C1 = "=SUM(IF(EXACT(C[2],RC[-1]),C[4],""""))"
            Range("F7").AutoFill Destination:=Range("F7:F9"), Type:=xlFillDefault

    'Consolidate First splits

        Range("F12").FormulaR1C1 = "=SUM(R[-10]C,R[-5]C)"
        Range("F13").FormulaR1C1 = "=SUM(R[-10]C,R[-5]C)"
        Range("F14").FormulaR1C1 = "=SUM(R[-10]C,R[-5]C)"

    'Copy first splits to Main
        Sheets("Main").Select
        Range("H16").FormulaR1C1 = "='EB Adj Cons'!R[-14]C[-2]"
        Range("H17").FormulaR1C1 = "='EB Adj Cons'!R[-10]C[-2]"
        Range("H19").FormulaR1C1 = "='EB Adj Cons'!R[-16]C[-2]"
        Range("H20").FormulaR1C1 = "='EB Adj Cons'!R[-12]C[-2]"
        Range("H21").FormulaR1C1 = "='EB Adj Cons'!R[-17]C[-2]"

    'Consolidate month splits

        'Set up EB Adj Cons month splits
            Sheets("EB Adj Cons").Select
            Range("K:K,O:O,S:S").ColumnWidth = 2
            Range("L:L,P:P").ColumnWidth = 8
            Range("M:M,Q:Q").ColumnWidth = 10
            Range("N:N,R:R").ColumnWidth = 13

            Range("L1").FormulaR1C1 = "Account"
            Range("M1").FormulaR1C1 = "Bill Period"
            Range("N1").FormulaR1C1 = "Adjustment"
            Range("P1").FormulaR1C1 = "Account"
            Range("Q1").FormulaR1C1 = "Bill Period"
            Range("R1").FormulaR1C1 = "Adjustment"

        'Local 70
            Dim lr3 As Long

            'Consolidate and paste acct #s and bill periods
                Range("A2").Select
                    Range(Selection, Selection.End(xlDown)).Select
                    Selection.Copy
                Range("L2").Select
                    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
                Range("B2").Select
                    Range(Selection, Selection.End(xlDown)).Select
                    Application.CutCopyMode = False
                    Selection.Copy
                Range("M2").Select
                    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
                    Application.CutCopyMode = False
                    Selection.NumberFormat = "mm/yyyy"
                ActiveSheet.Range("$L$1:$M$75000").RemoveDuplicates Columns:=Array(1, 2), Header:=xlNo

            'Consolidate amounts
                lr3 = Cells(Rows.count, "L").End(xlUp).Row
                Range("N2").Select
                ActiveCell.FormulaR1C1 = "=SUMIFS(C[-11],C[-13],RC[-2],C[-12],RC[-1])"
                Range("N2").AutoFill Destination:=Range("N2:N" & Range("L" & Rows.count).End(xlUp).Row)
                Range(Selection, Selection.End(xlDown)).Select

        'Other Locals
            Dim lr4 As Long

            'Consolidate and paste acct #s and bill periods
                Range("H2").Select
                    Range(Selection, Selection.End(xlDown)).Select
                    Selection.Copy
                Range("P2").Select
                    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
                Range("I2").Select
                    Range(Selection, Selection.End(xlDown)).Select
                    Application.CutCopyMode = False
                    Selection.Copy
                Range("Q2").Select
                    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
                    Application.CutCopyMode = False
                    Selection.NumberFormat = "mm/yyyy"
                ActiveSheet.Range("$P$1:$Q$75000").RemoveDuplicates Columns:=Array(1, 2), Header:=xlNo

            'Consolidate amounts
                lr4 = Cells(Rows.count, "P").End(xlUp).Row
                Range("R2").Select
                ActiveCell.FormulaR1C1 = "=SUMIFS(C[-8],C[-10],RC[-2],C[-9],RC[-1])"
                Range("R2").AutoFill Destination:=Range("R2:R" & Range("P" & Rows.count).End(xlUp).Row)
                Range(Selection, Selection.End(xlDown)).Select

    'Move totals to Split Request
        Dim lr5 As Long

        'Local 70
            Range("L2:N" & lr3).Copy
            Sheets("Split Request").Range("A" & Rows.count).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues

        'Other Locals
            Sheets("EB Adj Cons").Select
            Range("P2:R" & lr4).Copy
            Sheets("Split Request").Range("A" & Rows.count).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues

    'Add current bill period totals to Split Request

        '129542
            Sheets("Main").Select
            Range("F15").Copy
            Sheets("Split Request").Range("A" & Rows.count).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
            Sheets("Main").Select
            Range("G15").Copy
            Sheets("Split Request").Range("C" & Rows.count).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues

        '129543
            Sheets("Main").Select
            Range("F18").Copy
            Sheets("Split Request").Range("A" & Rows.count).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
            Sheets("Main").Select
            Range("G18").Copy
            Sheets("Split Request").Range("C" & Rows.count).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues

        '129544
            Sheets("Main").Select
            Range("F21").Copy
            Sheets("Split Request").Range("A" & Rows.count).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
            Sheets("Main").Select
            Range("G21").Copy
            Sheets("Split Request").Range("C" & Rows.count).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues

    'Reminder to enter current bill period dates
        MsgBox "Make sure to add current bill period to last entries on the Split Request.", vbOKOnly

'Remove negatives from EB adjustment files

    'Add Sheet
        Sheets.Add After:=ActiveSheet
        Sheets("Sheet13").Name = "Eastbay Adjustments"
        Sheets("Eastbay Adjustments").Select

'Eastbay Local 70

    'Clean and set up EB Local 70 Adjustment workbook
        Dim lr6 As Long
        Dim lr7 As Long
        Sourcewb.Activate
        Columns("D").Delete

    'Filter, copy, paste
        lr6 = Range("A" & Rows.count).End(xlUp).Row
        ActiveSheet.Range("$A$1:$P$" & lr6).AutoFilter Field:=11, Criteria1:="<0"
        Range("$A$1:$P$" & Cells(Rows.count, "A").End(xlUp).Row).SpecialCells(xlCellTypeVisible).Copy
        ThisWorkbook.Activate
        Sheets("Eastbay Adjustments").Select
        Range("A1").Select
        Cells(Rows.count, 1).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
        Application.CutCopyMode = False

    'Delete unhidden data
        Sourcewb.Activate
        Application.DisplayAlerts = False
        ActiveSheet.UsedRange.Offset(1, 0).Resize(ActiveSheet.UsedRange.Rows.count - 1).Rows.Delete
        Application.DisplayAlerts = True

    'Remove filter
        Range("K:K").AutoFilter

    'Save EB 70 Adjustment workbook to new name
        Application.DisplayAlerts = False
        MsgBox "Save as 'Eastbay_70_Adj' to be sent to Operations.", vbOKOnly
        Dim File_Name As String
        File_Name = Application.GetSaveAsFilename
        ActiveWorkbook.SaveAs Filename:=File_Name & "xlsx"
        ActiveWorkbook.Saved = True
        ActiveWorkbook.Close
        Application.DisplayAlerts = True

'Eastbay Other Locals

    'Clean and set up EB Other Locals Adjustment workbook
        Dim lr8 As Long
        Dim lr9 As Long
        Sourcewb2.Activate
        Columns("D").Delete

    'Filter, copy, paste
        lr8 = Cells(Rows.count, "A").End(xlUp).Row
        Columns("K:K").AutoFilter
        ActiveSheet.Range("$A$1:$P$" & lr8).AutoFilter Field:=11, Criteria1:="<0"
        Range("$A$1:$P$" & Cells(Rows.count, "A").End(xlUp).Row).SpecialCells(xlCellTypeVisible).Copy
        ThisWorkbook.Activate
        Sheets("Eastbay Adjustments").Select
        lr9 = Cells(Rows.count, "A").End(xlUp).Row
        Cells(Rows.count, 1).End(xlUp).Offset(2, 0).PasteSpecial xlPasteValues
        Application.CutCopyMode = False

    'Delete unhidden data
        Sourcewb2.Activate
        Application.DisplayAlerts = False
        ActiveSheet.UsedRange.Offset(1, 0).Resize(ActiveSheet.UsedRange.Rows.count - 1).Rows.Delete
        Application.DisplayAlerts = True

    'Remove filter
        Range("K:K").AutoFilter

    'Save EB 287 Adjustment workbook to new name
        Application.DisplayAlerts = False
        MsgBox "Save as 'Eastbay_287_Adj' to be sent to Operations.", vbOKOnly
        Dim File_Name2 As String
        File_Name2 = Application.GetSaveAsFilename
        ActiveWorkbook.SaveAs Filename:=File_Name2 & "xlsx"
        ActiveWorkbook.Saved = True
        ActiveWorkbook.Close
        Application.DisplayAlerts = True

'Export EB Manual Adjustments to Manual Adjustments workbook
    Dim EBws As Worksheet
    Dim MA As Workbook

    'Loop through all open workbooks
        For Each MA In Application.Workbooks
            If MA.Name Like "*Manual Adjustments*" Then
                MA.Activate
                Exit For
            End If
        Next MA

    'Copy and paste EB Adjustments sheet
        ThisWorkbook.Activate
        Sheets("Eastbay Adjustments").Select
        Sheets("Eastbay Adjustments").Move After:=Workbooks(2).Sheets(1)
        MA.Activate

    'Save Manual Adjustment workbook
        ActiveWorkbook.Close SaveChanges:=True

Application.ScreenUpdating = True

End Sub

 

[Joe4]

I noticed here that you are telling it what workbook to reference, but not which sheet in that workbook to filter:

'Eastbay Other Locals

    'Clean and set up EB Other Locals Adjustment workbook
        Dim lr8 As Long
        Dim lr9 As Long
        Sourcewb2.Activate
        Columns("D").Delete

Are you sure it is on the correct sheet at the time it is trying to run your filter?

I would highly recommend stepping through your code one line at a time using the F8 key, while you watch and see what it is doing.
Many times, when you do that, it becomes quite evident what the problem is. You may not be in the proper place at the time of the error, or the data may not look like you think it does at the time of the error.
Stepping through you code while watching the sheet updates is a very useful debugging technique that helps zero in on a lot of issues!

 

[Lelewiwi]

There is only one sheet in that specific workbook. It has a variable name, but just the one sheet. I tried doing what you said, but I still cannot understand why the error is being thrown.

 

[Joe4]

I do see one very odd thing in your code.
All your references to "Rows.count" shows a lower case "c" in the word count.
"Count" is a reserved word and therefore usually automatically capitalizes itself.

The fact that it is NOT capitalized indicates that you may have used the word "count" in your code somewhere, as a:
- variable
- procedure name
- function name

Is that the case? If so, that could be problematic, as you should NEVER use reserved words like "count" for any of those things, as it could cause unexpected results and errors.
Find where you may have done that, and rename it.

 

[Lelewiwi]

Renamed all the count to Count. Still getting the error.

 

[Joe4]

Renamed all the count to Count. Still getting the error.

That still may not have fixed the issue.
Find where else you used "count" as a variable, procedure, or function name, and give it a new/different name.

 

[Lelewiwi]

I guess I'm not really understanding what any of those are. I am not a coder and this was pieced together. Do you want me to post the code in its entirety? There are 13 different Macros that run one after another.

 

[Joe4]

Do a Search/Find on your entire project, and look for every instance of the word "Count".
Show me everywhere where it is used, except in references like "Rows.Count".

 

[Lelewiwi]

On Macro 3:

    'Clean up Manual Adjustments sheet
        Dim r1, r2
        Sheets("Sheet1").Select
        r1 = Range("A75000").End(xlUp).Row

        Dim Count As Long
        For Count = 2 To r1
            If Range("H" & Count).Value < 0 Then
                Rows(Count).EntireRow.Cut
                'Determine last row in the Manual Adjustments Sheet (r2)
                Sheets("Manual Adjustments").Select
                r2 = Range("A75000").End(xlUp).Row
                Rows(r2 + 1).EntireRow.Select
                ActiveSheet.Paste
                Sheets("Sheet1").Select
            End If
        Next Count

        On Error Resume Next
        Columns("A").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
        Sheets("Manual Adjustments").Select

On Macro 4:

    'Copy worksheet to new book
        Sheets("Sheet1").Copy
        Set wb = ActiveWorkbook
        Set ws = ActiveSheet

        With wb
            'Delete rows with blanks in Columns A and B, clear all columns but A
            With ws
                .Range("A:H").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
                .Range(.Cells(1, "B"), .Cells(.Rows.Count, .Columns.Count)).Clear
                .Range(.Cells(1, "C"), .Cells(.Rows.Count, .Columns.Count)).Clear
                .Range(.Cells(1, "D"), .Cells(.Rows.Count, .Columns.Count)).Clear
                .Range(.Cells(1, "E"), .Cells(.Rows.Count, .Columns.Count)).Clear
                .Range(.Cells(1, "F"), .Cells(.Rows.Count, .Columns.Count)).Clear
                .Range(.Cells(1, "G"), .Cells(.Rows.Count, .Columns.Count)).Clear
                .Range(.Cells(1, "H"), .Cells(.Rows.Count, .Columns.Count)).Clear
            End With
            .SaveAs FileSaveName, xlTextWindows
            .Close False
        End With
        Application.DisplayAlerts = True

On Macro 8:

    'Choose Annuity Summary file
        MsgBox "Please select Annuity Summary file.", vbOKOnly
        Dim Dbox As FileDialog
        Dim File_Path As String
        Set Dbox = Application.FileDialog(msoFileDialogFilePicker)
        Dbox.Title = "Choose and Open" & FileType
        Dbox.Filters.Clear
        Dbox.Show
        If Dbox.SelectedItems.Count = 1 Then File_Path = Dbox.SelectedItems(1)
        Set Sourcewb = Workbooks.Open(Filename:=File_Path)

On Macro 10:

    'Choose Eastbay Local 70 regular
        MsgBox "Please select Eastbay Local 70 regular file.", vbOKOnly
        Dim Dbox As FileDialog
        Dim File_Path As String
        Set Dbox = Application.FileDialog(msoFileDialogFilePicker)
        Dbox.Title = "Choose and Open" & FileType
        Dbox.Filters.Clear
        Dbox.Show
        If Dbox.SelectedItems.Count = 1 Then File_Path = Dbox.SelectedItems(1)
        Set EB70 = Workbooks.Open(Filename:=File_Path)

On Macro 11:

    'Choose Eastbay Other Locals regular
        MsgBox "Please select Eastbay Other Locals regular file.", vbOKOnly
        Dim Dbox As FileDialog
        Dim File_Path As String
        Set Dbox = Application.FileDialog(msoFileDialogFilePicker)
        Dbox.Title = "Choose and Open" & FileType
        Dbox.Filters.Clear
        Dbox.Show
        If Dbox.SelectedItems.Count = 1 Then File_Path = Dbox.SelectedItems(1)
        Set EB287 = Workbooks.Open(Filename:=File_Path)

On Macro 12:

    'Choose Eastbay Local 70 Adjustments file
        MsgBox "Please select Eastbay Local 70 Adjustments file.", vbOKOnly
        Dim Dbox As FileDialog
        Dim File_Path As String
        Set Dbox = Application.FileDialog(msoFileDialogFilePicker)
        Dbox.Title = "Choose and Open" & FileType
        Dbox.Filters.Clear
        Dbox.Show
        If Dbox.SelectedItems.Count = 1 Then File_Path = Dbox.SelectedItems(1)
        Set Sourcewb = Workbooks.Open(Filename:=File_Path)
        Set Sourcews = Sourcewb.Sheets(1)

    'Choose Eastbay Other Locals Adjustments file
        MsgBox "Please select Eastbay Other Locals Adjustments file.", vbOKOnly
        Dim Dbox2 As FileDialog
        Dim File_Path2 As String
        Set Dbox2 = Application.FileDialog(msoFileDialogFilePicker)
        Dbox2.Title = "Choose and Open" & FileType
        Dbox2.Filters.Clear
        Dbox2.Show
        If Dbox2.SelectedItems.Count = 1 Then File_Path2 = Dbox2.SelectedItems(1)
        Set Sourcewb2 = Workbooks.Open(Filename:=File_Path2)
        Set Sourcews2 = Sourcewb2.Sheets(1)

 

[Joe4]

OK, here is the issue I was referring to - you are using "Count" as a variable here:

        Dim Count As Long
        For Count = 2 To r1
            If Range("H" & Count).Value < 0 Then
                Rows(Count).EntireRow.Cut
                'Determine last row in the Manual Adjustments Sheet (r2)
                Sheets("Manual Adjustments").Select
                r2 = Range("A75000").End(xlUp).Row
                Rows(r2 + 1).EntireRow.Select
                ActiveSheet.Paste
                Sheets("Sheet1").Select
            End If
        Next Count

where "Count" is already a reserved word, as it is an existing property, method, function, object.

Try changing that section of code to this:

        Dim MyCount As Long
        For MyCount = 2 To r1
            If Range("H" & MyCount).Value < 0 Then
                Rows(MyCount).EntireRow.Cut
                'Determine last row in the Manual Adjustments Sheet (r2)
                Sheets("Manual Adjustments").Select
                r2 = Range("A75000").End(xlUp).Row
                Rows(r2 + 1).EntireRow.Select
                ActiveSheet.Paste
                Sheets("Sheet1").Select
            End If
        Next MyCount

(note how I changed all the references in this block of code to our "Count" variable to "MyCount").

I don't know if that what was causing issues with your code, but it is definitely a section of code that needs addressing to avoid errors and unexpected results.