Merging List From Multiple Sheets

[JonnieO]

I know this issue has been discussed, but for the life of me I cant get it to work fully. Hopefully, someone can point out my mistake.

Okay, I have a workbook with two sheets for two of our locations (Cincinnati and Chicago). The sheet structures are the exact same and are formatted the exacts same. In column "H" is an indicator (1=Active Account). I am trying to look on the Cincinnati sheet ("CIN BOXES") and grab all of the active accounts (those marked with a "1" in column "H"), then paste them on the "ACTIVE_BOXES" tab. Then grab the active boxes from the Chicago sheet (CHI BOXES) and append them to the list on the "ACTIVE_BOXES" tab. Although there are over 10,000 accounts I am setting it to look for the first 10, simply for testing and debugging purposes

When I run the macro, it pulls in the 10 active accounts from the "CIN BOXES" sheet, then displays the message box, sayings it's complete, none of the data from the CHI BOXES sheet is ever displayed. I even set them up as separate macros and the Chicago macro instantly displays the "complete" message box

Cincinnati Sub

Sub CIN_ACTIVE_BOXES()
Application.ScreenUpdating = False
Application.StatusBar = "CINCINNATI Active Lockbox List is currently updating, please wait."




'COPY ALL ACTIVE LOCKBOXES TO "ACTIVE_BOXES" SHEET
Sheets("ACTIVE_BOXES").Select
With Sheets("ACTIVE_BOXES")
    .UsedRange.Offset(2).ClearContents
    .UsedRange.Offset(2).ClearFormats
End With
Dim lr As Long, lr2 As Long, lr3 As Long, r As Long, r2 As Long, ws1 As Worksheet, ws2 As Worksheet, N As Long
Set ws1 = Sheets("CIN BOXES")
Set ws2 = Sheets("ACTIVE_BOXES")
N = 2
lr = ws1.Cells(Rows.Count, "H").End(xlUp).Row
lr2 = ws2.Cells(Rows.Count, "A").End(xlUp).Row




    For r = 2 To 10
        If ws1.Range("H" & r).Value = 1 Then
            ws1.Rows(r).Copy
            ws2.Rows(N).Select
            Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
            Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
            N = N + 1
        End If
    Next r
Call CHI_ACTIVE_BOXES


End Sub

Chicago Sub

Sub CHI_ACTIVE_BOXES()
Application.ScreenUpdating = False
Application.StatusBar = "CHICAGO Active Lockbox List is currently updating, please wait."




'COPY ALL CHI ACTIVE LOCKBOXES TO "ACTIVE_BOXES" SHEET BELOW CINCINNATI LIST
Sheets("ACTIVE_BOXES").Select


Dim lr As Long, lr2 As Long, r As Long, ws3 As Worksheet, ws2 As Worksheet, N As Long
Set ws3 = Sheets("CHI BOXES")
Set ws2 = Sheets("ACTIVE_BOXES")
N = 11
lr = ws3.Cells(Rows.Count, "H").End(xlUp).Row
lr2 = ws2.Cells(Rows.Count, "A").End(xlUp).Row


    For r = 2 To 10
        If ws3.Range("H" & r).Value = 1 Then
            ws3.Rows(r).Copy
            ws2.Rows(N).Select
            Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
            Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
            N = N + 1
        End If
    Next r
Sheets("ACTIVE_BOXES").Select
Application.StatusBar = False
Application.ScreenUpdating = True
  
MsgBox "Active Lockbox List Updated!", vbExclamation, "Active List"
End Sub

Any help would be appreciated.

 

[tonyyy]

JonnieO,

Just to provide some feedback, your code works fine in my Windows 7 / Excel 2010 environment. And in reviewing the code, nothing jumps out as blatantly incorrect.

My suggestion would be to 1) ensure you have the proper column reference(s) - is the 1=Active Account actually in Column H?, and 2) ensure there is valid data in Column H.

Cheers,

tonyyy