asked on

Import data from spreadsheet to another based on value found in both spreadsheets

This question is related to a previous solution found here:

https://www.experts-exchange.com/questions/27416569/Import-spreadsheet-from-an-attachment-on-an-email-to-my-production-spreadsheet.html

While this worked, the spreadsheet that values have to be extracted from has changed. Here's what needs to happen.

Attached is an example spreadsheet (scrubbed) called Pickups.xls.

I need a function to scan through Col C of Pickups.xls and WHEN the value in Col C = the value in Col D of the sheet named WU-Staging-FBME in my Visa-Spreadsheet (also scrubbed and attached) the value in Col G of Pickups.xls is copied to Col H of WU-Staging-FBME sheet.

I need this action to be initiated from a button click that I can put on my sheet 'Main' of my Visa-Spreadsheet which is not included in the attached file.
Pickups.xls Visa-Spreadsheet.xlsm

Jacques Geday

JaseSt,

You say: While this worked, the spreadsheet that values have to be extracted from has changed.
>>> What is it that was previously done and has changed ?

gowflow

Jacques Geday

2 more:

1) Will the file be always name Pickups.xls ?
2) Can we assume it is put in the WU directory ?

both if yes would facilitate life !
gowflow

Jacques Geday

Was it by any chance: Import WU Confirmed Amounts ? if yes they should this sub be changed to this new setting or you still need both ??
gowflow

JaseSt

ASKER

What has changed? The first column containing row numbers has been deleted and he includes column headings on various rows throughout the spreadsheet. Besides that I think it is the same.

- The file could be named pickups.xls but even if it was I would have to rename it as I can't have it overwrite previous files I receive. I can rename it to whatever you want however.

- Yes, I will save it in the WU directory.

JaseSt

ASKER

"Was it by any chance: Import WU Confirmed Amounts ? if yes they should this sub be changed to this new setting or you still need both ??"

- Yes I believe that was the routine.

- Need both of what?

Jacques Geday

So shall we replace this old routine by this new criteria or you need the old way and the new way to be accessible this was my question
gowflow

JaseSt

ASKER

Don't need the old way. Thanks.

Jacques Geday

ok then here it is.

1) Make a copy of your latest Visa file and give it a new name.
2) open VBA and doubleclick on module1 and view 1 sub at a time by clicking on the bottom left icon
3) choose sub WUConfirmedAmts and delete it.
4) Paste the below Sub after any end sub

Sub ImportWUConfirmedAmts()
Dim WS As Worksheet
Dim WSWU As Worksheet

Dim WB As Workbook
Dim I As Long, J As Long
Dim MaxRowWU As Long, MaxRowWUBalance As Long
Dim Rng As Range
Dim TConfirmed As Double, TColG As Double, TColH As Double
Dim WUFile As String, WUDate As String
Dim C

If MsgBox("Are you ready to Import WU Confirmed Amounts ?", vbQuestion + vbYesNo, "Import WU Confirmed Amounts") = vbYes Then
    Do
        WUFile = GFileName(gstFolderWesternUnion, "Please choose WU File to Import: ")
        If WUFile = "" Then
            If MsgBox("No file has been selected" & Chr(10) _
                & "[OK]     to continue and select a file." & Chr(10) _
                & "[Cancel] to Exit." & Chr(10) & Chr(10) _
                & "Please make a selection.", vbInformation + vbOKCancel, "Import WU Confirmed Amounts") = vbCancel Then
                Exit Sub
            End If
        End If
    Loop Until WUFile <> ""
    Set WS = Sheets("WU-Staging-FBME")
    'Set WSWUBalance = Sheets("WU-Balance-New")
    Set WB = Workbooks.Open(WUFile)
    Set WSWU = ActiveSheet
    
    MaxRowWU = WSWU.Range("B:B").End(xlDown).Row
    'MaxRowWUBalance = WSWUBalance.Rows(WSWUBalance.Rows.Count).End(xlUp).Row + 1

    For I = 2 To MaxRowWU
        If WSWU.Cells(I, "C") <> "" Then
            WS.UsedRange.AutoFilter 4, WSWU.Cells(I, "C")
            'WS.UsedRange.AutoFilter 3, Criteria1:=">=" & WSWU.Cells(I, "C"), Operator:=xlAnd, Criteria2:="<=" & WSWU.Cells(I, "C")
            'WS.UsedRange.AutoFilter 2, WSWU.Cells(I, "B")
            Set Rng = WS.UsedRange.SpecialCells(xlCellTypeVisible)
            For Each Row In Rng.EntireRow
                If Not Row.Row = 1 Then
                    'If WSWU.Cells(I, "A") = WS.Cells(Row.Row, "A") And WSWU.Cells(I, "E") = WS.Cells(Row.Row, "E") And WSWU.Cells(I, "F") = WS.Cells(Row.Row, "F") And WS.Cells(Row.Row, "H").Value = "" Then
                    If WSWU.Cells(I, "H") = WS.Cells(Row.Row, "M") And WSWU.Cells(I, "I") = WS.Cells(Row.Row, "P") Then
                        WS.Cells(Row.Row, "H").Value = WSWU.Cells(I, "G").Value
                        WS.Range("H" & Row.Row).Font.ColorIndex = 5
                        TConfirmed = TConfirmed + WSWU.Cells(I, "G").Value
                        J = J + 1
                        Exit For
                    End If
                End If
            Next Row
        End If
    Next I
    
WS.ShowAllData
WS.AutoFilterMode = False
MsgBox ("Confirmed Amounts Updated successfully for " & J & " records totalling " & TConfirmed)


'Close and Exit
WB.Close savechanges:=False
Set WB = Nothing
Set WSWU = Nothing
Set WS = Nothing

End If
End Sub

Open in new window

5) SAVE and Exit the workbook
6) Re-start it and give it a try.

By the way no need to name the file Pickup or anything you can have it the name you want as the routine ask you to select the file to import. Even if the file is not in WU directory you can browse to find it.

gowflow

JaseSt

ASKER

Ok gowflow, but what name do I save the incoming Pickups.xls as?

Jacques Geday

what ever you want you didn't read my post at the end ?
gowflow

JaseSt

ASKER

Ok, right. Problem with doing too many things at once. Didn't happen to see that, sorry. Now, I'll just wait until I get a new pickups file which should be today. Look forward to this one working because it is taking me far too long doing this manually.

Jacques Geday

Got your Pickup working ?
gowflow

JaseSt

ASKER

Haven't had an opportunity to check it out yet. He has yet to send me a new file and I don't want it test it on one previously sent. Should be soon however.

JaseSt

ASKER

A pickups sheet came in. I ran the code and it did not work. For some reason, even though I was able to do it manually, the code did not find the match from Col C of the Pickups.xls file in Col D of the sheet WU-Staging-FBME.

Attached is a scrubbed copy of the pickups file I saved and used to update the WU-Staging-FBME sheet. When ran, a dialog box came up saying 0 matches were found.3-5-12-Pickups.xls

ASKER CERTIFIED SOLUTION

Jacques Geday

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

JaseSt

ASKER

Works magnificently, gowlfow! Saves me a ton of time. And thanks for formatting the imported data as blue so I can easily tell which has been just imported. thank you!

JaseSt

ASKER

I have a function I think you worked on that for some reason does not work any more. I will post a link to it here later and reference the original question, if you care to work on it.

You may remember it. It is the button on the WU-Staging-FBME sheet: "SUM-Up completed batches". When finished it says it summed up the totals but doesn't. I'll explain in the next question.

Jacques Geday

ok and tks for the comments and grade.
Rgds/gowflow

JaseSt

ASKER

Here it is, gowflow:

https://www.experts-exchange.com/questions/27621493/look-at-previous-solution-Sum-Up-Values-WU-Staging-FBME-now-not-working.html