Code will only consolidate from one file and will get an error right after

Hi,

I am currently working on this project that will consolidate databases from multiple files. The code is not mine. I got this from one of the experts here. Unfortunately, I could not recall his/her name and I am unable to view that thread again(thanks to our company's deep freeze). It will only consolidate from Logger v2.1 and will get an error when getting the information from Logger v3.1. Can you please tell me what I did wrong? I am not good with coding and I just started learning MS Excel VB.

Another minor issue is that when I enter data into the database using the Logger v1.1, if I add a data using Posting ID and then add another data using Posting ID or Order ID, the previous data gets overwritten. Can you please help me with the code?

Private Sub Tstamp()
    txtDate.Value = Now
    txtDate = Format(txtDate.Value, "mmm/dd/yy hh:mm")
End Sub
Private Sub Clearcache()
    Me.txtOrder.Text = Empty
    Me.txtPosting.Text = Empty
    Me.cboStatus.Text = Empty
    Me.txtOrigin.Text = Empty
    Me.txtaddInfo.Text = Empty
    Me.txtDate.Text = Empty
    Me.txtOrder.SetFocus
End Sub
Private Sub ToggleButton1_Click()
    If ToggleButton1.Value = False Then
        Application.Visible = False
        ToggleButton1.Caption = "Show Excel"
    Else
        Application.Visible = True
        ToggleButton1.Caption = "Hide Excel"
    End If
End Sub
Private Sub ToggleButton2_Click()
    If ToggleButton2.Value = False Then
        Sheets("FormsTest").Select
        Range("E1").Select
        Selection.AutoFilter
        ActiveSheet.Range("$A$1:$F$79").AutoFilter Field:=5, Criteria1:=Array( _
        "Cancelled", "Cost Confirm", "Emailed AM", "Emailed Client", "Emailed Site", "JB Request"), Operator:= _
        xlFilterValues
        ToggleButton2.Caption = "Filter OFF"
    Else
        Sheets("FormsTest").Select
        Range("E1").Select
        Selection.AutoFilter
        ToggleButton2.Caption = "Filter ON"
    End If
    Do Until ActiveCell.Value = Empty
    ActiveCell.Offset(1, 0).Select
    Loop
End Sub
Private Sub UserForm_Initialize()
    i = 1
    'Application.Visible = False
    Me.cboStatus.AddItem "Resolved"
    Me.cboStatus.AddItem "JB Request"
    Me.cboStatus.AddItem "Cost Confirm"
    Me.cboStatus.AddItem "Emailed AM"
    Me.cboStatus.AddItem "Emailed Site"
    Me.cboStatus.AddItem "Emailed Client"
    Me.cboStatus.AddItem "Cancelled"
    Me.txtOrder.SetFocus
End Sub
Private Sub overWrite()
    Range("A2").Select
    ActiveCell.Offset(1, 0).Select
    Do Until ActiveCell.Value = Empty
    If ActiveCell.Offset(0, 1) = Me.txtOrder.Text And _
        ActiveCell.Offset(0, 2) = Me.txtPosting.Text Then
            Call Tstamp
            ActiveCell.Offset(0, 0).Value = Me.txtDate.Text
            ActiveCell.Offset(0, 1).Value = Me.txtOrder.Text
            ActiveCell.Offset(0, 2).Value = Me.txtPosting.Text
            ActiveCell.Offset(0, 4).Value = Me.cboStatus.Text
            Call Clearcache
            Exit Sub
    End If
    ActiveCell.Offset(1, 0).Select
Loop
End Sub
Private Sub cmdClose_Click()
    Unload Me
End Sub
Private Sub cmdLog_Click()
    Dim iRow As Long
    Dim ws As Worksheet
    Set ws = Worksheets("FormsTest")
    i = 0
    iRow = ws.Cells(Rows.Count, 2).End(xlUp).Offset(1, 0).Row

If Me.txtPosting.Text = Empty And _
    Me.txtOrder.Text = Empty Then
    MsgBox "Please enter Order ID or Posting ID.", vbExclamation
    Me.txtOrder.SetFocus
    Exit Sub
End If

If Me.cboStatus.Text = Empty Then
    MsgBox "Please set Status.", vbExclamation
    Me.cboStatus.SetFocus
    Exit Sub
End If

Call overWrite

Do Until ActiveCell.Value = Empty
    ActiveCell.Offset(1, 0).Select
Loop

Call Tstamp
    If Me.cboStatus.Text <> Empty Then
        ws.Cells(iRow, 1).Value = Me.txtDate.Value
        ws.Cells(iRow, 2).Value = Me.txtOrder.Value
        ws.Cells(iRow, 3).Value = Me.txtPosting.Value
        ws.Cells(iRow, 4).Value = Me.txtOrigin.Value
        ws.Cells(iRow, 5).Value = Me.cboStatus.Value
        ws.Cells(iRow, 6).Value = Me.txtaddInfo.Value
    End If
    
Call Clearcache


Call Tstamp

End Sub

I attached my work. Thank you so much.

Hi jednified

Does Logger v1.1.xlsm not get consolidated?

Hi jednified

Try this Code...appears to do as you require...

Sub Consolidate()
    Dim wb_1 As Excel.Workbook
    Dim LR As Long
    On Error GoTo Errorcatch
    Set wb_1 = Workbooks.Open(ThisWorkbook.Path & "\Logger v3.1.xlsm")

    With wb_1.Sheets("FormsTest")
        LR = .Cells.Find("*", .Cells(.Rows.Count, .Columns.Count), SearchOrder:=xlByRows, _
                SearchDirection:=xlPrevious).Row
        .Range(.Cells(2, 1), .Cells(LR, "F")).Copy
    End With

    Windows("Database Server.xlsm").Activate
    With Sheets("Sheet1")
        .Range("B2").PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:=xlNone _
                , SkipBlanks:=False, Transpose:=False
    End With

    Application.CutCopyMode = False
    wb_1.Close savechanges:=False

    Dim wb_2 As Excel.Workbook
    Set wb_2 = Workbooks.Open(ThisWorkbook.Path & "\Logger v2.1.xlsm")

    With wb_2.Sheets("FormsTest")
        LR = .Cells.Find("*", .Cells(.Rows.Count, .Columns.Count), SearchOrder:=xlByRows, _
                SearchDirection:=xlPrevious).Row
        .Range(.Cells(2, 1), .Cells(LR, "F")).Copy
    End With

    Windows("Database Server.xlsm").Activate
    With Sheets("Sheet1")
        .Range("B" & .Rows.Count).End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:=xlNone _
                , SkipBlanks:=False, Transpose:=False
    End With
    
    Application.CutCopyMode = False
    wb_2.Close savechanges:=False

Errorcatch:
    MsgBox Err.Description
End Sub

Hi jednified

Compare this line of Code

Range("B2" & Rows.Count).End(xlUp).Offset(1)

to this

.Range("B" & .Rows.Count).End(xlUp).Offset(1).

Other than that, I modified the Code to eliminate all the selecting.

Hi jednified

Yes, I should think you can do this

I can also create a button in excel to start consolidating the files

Assign the Code to the Button...

You're welcome...glad I could help. Thanks for the Rep.