VBA to pull rows from a user selected workbook

[Cr864]

Hey everyone,

Im looking for a way to have users be able to click a button to select a file, and within that file if the check numbers listed in Range A7:A100 in the Updated SQL Query 2.0 workbook match a check number in the Column P in the user selected file (for this case, Test.xlsx) then return the entire row and copy it into the Updated SQL Query 2.0 workbook in a sheet named ABC for the purposes of this.

So far, ive found the following code that seems to work to have users to be able to select a file and pull a certain range of data from it. I'm not sure how to look for the numbers in the range and pull back the rows of data from the other sheet though. Any help would be appreciated. Thank you!

Dim FileToOpen As Variant
Dim OpenBook As Workbook

Application.ScreenUpdating = False
    FileToOpen = Application.GetOpenFilename(Title:="Browse for your File & Import Range", FileFilter:="Excel Files (*.xls*),*xls*")
    If FileToOpen <> False Then
        Set OpenBook = Application.Workbooks.Open(FileToOpen)
        OpenBook.Sheets(1).Range("A1:AQ20").Copy
        ThisWorkbook.Worksheets("NDC").Range("A2").PasteSpecial xlPasteValues
        OpenBook.Close False
    End If
Application.ScreenUpdating = True

Test.xlsx
	P
1	CheckNo
2	300118
3	299284
4	299284
5	299284
6	299284
7	299284
8	299286
9	299284
10	299284
11	299284
12	299284
13	299284
14	299284
15	299284
16	299284
17	299284
18	300294
19	299277
20	299288
ExcelDestination

Updated SQL Query Sheet 2.0.xlsm
	A	B	C	D
6	List of checks to be retrieved into tab "BK"	Check Amount	Total Check	Difference
7			0.00	0.00
8			0.00	0.00
9			0.00	0.00
10			0.00	0.00
11			0.00	0.00
12			0.00	0.00
13			0.00	0.00
14			0.00	0.00
Main

Cell Formulas
Range	Formula
C7:C14	C7	=SUMIF(Combined!$J$2:$J$9958,Main!$A7,Combined!$L$2:$L$9958)
D7:D14	D7	=B7-C7

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C7:C100	Expression	=$A7=""	text	NO
C7:C100	Expression	=$C7=$B7	text	NO
C7:C100	Expression	=$C7<>$B7	text	NO

 

[Anthony47]

I didn't understand if there are two or three workbooks involved in the process, can you clarify?

bye

 

[Cr864]

I didn't understand if there are two or three workbooks involved in the process, can you clarify?

bye

Yes, sorry i wasn't more clear.

There is the main workbook we work in "Updated SQL Query 2.0.xlsx"

Then there is a user selected work book that we want to pull data from and place it into the workbook named above. The workbook we pull the data from has a filename that changes every week or so but the format is always the same. So i was hoping we could have the user select the most recent workbook with an open file dialog and then inside the user selected workbook, look at Column P for the Check#s that will be listed in ranged A7:A100 on the Sheet named "Main" in the "Updated SQL Query 2.0.xlsx" workbook. If it finds the check#s (there could be multiple listings) have it pull every row containing that check number and place it into a Sheet called "NDC" in the "Updated SQL Query 2.0.xlsx" workbook which is our main workbook.

Thank you for taking the time to respond and I hope that is more clear. If you need any more information let me know!

 

[Cr864]

Yes, sorry i wasn't more clear.

There is the main workbook we work in "Updated SQL Query 2.0.xlsx"

Then there is a user selected work book that we want to pull data from and place it into the workbook named above. The workbook we pull the data from has a filename that changes every week or so but the format is always the same. So i was hoping we could have the user select the most recent workbook with an open file dialog and then inside the user selected workbook, look at Column P for the Check#s that will be listed in ranged A7:A100 on the Sheet named "Main" in the "Updated SQL Query 2.0.xlsx" workbook. If it finds the check#s (there could be multiple listings) have it pull every row containing that check number and place it into a Sheet called "NDC" in the "Updated SQL Query 2.0.xlsx" workbook which is our main workbook.

Thank you for taking the time to respond and I hope that is more clear. If you need any more information let me know!

Also, since i didnt actually answer your question. There are 2 workbooks involved.

 

[Anthony47]

Try this code:

Dim FileToOpen As Variant
Dim OpenBook As Workbook

Dim cCheck As Range, aaNext As Long, aaSh As Worksheet
Dim I As Long, myMatch, Col2Copy As Long
'
Set cCheck = ThisWorkbook.Sheets("Main").Range("A7:A100")   '<<< ??
Set aaSh = ThisWorkbook.Sheets("NDC")                       '<<< ??
Col2Copy = 10                                               '<<< How many cols to copy?
'
aaSh.Range("A2").Resize(10000, Col2Copy + 5).ClearContents  '<?? Clear sheets NDC
Application.ScreenUpdating = False
    FileToOpen = Application.GetOpenFilename(Title:="Browse for your File & Import Range", FileFilter:="Excel Files (*.xls*),*xls*")
    If FileToOpen <> False Then
        Set OpenBook = Application.Workbooks.Open(FileToOpen)
        Sheets(1).Select
        aaNext = 2
        For I = 1 To Cells(Rows.Count, "P").End(xlUp).Row
            myMatch = Application.Match(Cells(I, "P").Value, cCheck, False)
            If Not IsError(myMatch) Then
               aaSh.Cells(aaNext, 1).Resize(1, Col2Copy).Value = Cells(I, "A").Resize(1, Col2Copy).Value
                aaNext = aaNext + 1
            End If
        Next I
        OpenBook.Close False
    End If
Application.ScreenUpdating = True

Check the lines marked <<<; they specify: the range with the check n° to look for /the name of the workshet to copy the matching lines /how many columns need to be copied for one file to the original one
Sheets NDC is cleared at the beginning; if this is wrong then delete the line marked <??, but then also the initial value for aaNext has to be modified; so please advice if that is your case

Bye

 

[Cr864]

Try this code:

Dim FileToOpen As Variant
Dim OpenBook As Workbook

Dim cCheck As Range, aaNext As Long, aaSh As Worksheet
Dim I As Long, myMatch, Col2Copy As Long
'
Set cCheck = ThisWorkbook.Sheets("Main").Range("A7:A100")   '<<< ??
Set aaSh = ThisWorkbook.Sheets("NDC")                       '<<< ??
Col2Copy = 10                                               '<<< How many cols to copy?
'
aaSh.Range("A2").Resize(10000, Col2Copy + 5).ClearContents  '<?? Clear sheets NDC
Application.ScreenUpdating = False
    FileToOpen = Application.GetOpenFilename(Title:="Browse for your File & Import Range", FileFilter:="Excel Files (*.xls*),*xls*")
    If FileToOpen <> False Then
        Set OpenBook = Application.Workbooks.Open(FileToOpen)
        Sheets(1).Select
        aaNext = 2
        For I = 1 To Cells(Rows.Count, "P").End(xlUp).Row
            myMatch = Application.Match(Cells(I, "P").Value, cCheck, False)
            If Not IsError(myMatch) Then
               aaSh.Cells(aaNext, 1).Resize(1, Col2Copy).Value = Cells(I, "A").Resize(1, Col2Copy).Value
                aaNext = aaNext + 1
            End If
        Next I
        OpenBook.Close False
    End If
Application.ScreenUpdating = True

Check the lines marked <<<; they specify: the range with the check n° to look for /the name of the workshet to copy the matching lines /how many columns need to be copied for one file to the original one
Sheets NDC is cleared at the beginning; if this is wrong then delete the line marked <??, but then also the initial value for aaNext has to be modified; so please advice if that is your case

Bye

Hey Anthony,

I just got a chance to test this out and it doesn't seem to be pulling anything back currently. The ranges are correct for cCheck as well as aaSh and clearing the sheet is fine. I updated the Col2Copy to 30 columns since that is the full size of the data.

The file prompt opens, i can select the file and nothing returns to the NDC sheet.

Any suggestions? Once again, thank you for taking time to reply and help out. I appreciate it!

 

[Anthony47]

Is Sheets(1) the correct sheet to look in? Has the list in this column P the same format of the ones in A7:A100 in the sheet Main?
Add a stop in this position and when the code will "stop" check that you are in the correct sheet with the proper data in column P:

        aaNext = 2
        Stop
        For I = 1 To Cells(Rows.Count, "P").End(xlUp).Row

The theory of the macro is that it open a file, select the first sheet; for each row it checks that the value in column P match with any value in Main!A7:A100; if Yes then the target sheet (NDC) is populated with that row.
If all that I say doesn't switch any light on then either you supply a test environment or you need to debug the situation on your data.

Bye

 

[Anthony47]

I seem you are not going to look at the situation tonight, this gave me time for some more testing option, in addition to what I wrote in my previous message (see above):
-prepare a Test file with few lines (let's say 30), and of corse some of these lines have a CheckNo in column P listed in Main!A7:A100 and other are not listed.
-use the following code:

Dim FileToOpen As Variant
Dim OpenBook As Workbook

Dim cCheck As Range, aaNext As Long, aaSh As Worksheet
Dim I As Long, myMatch, Col2Copy As Long
'
Set cCheck = ThisWorkbook.Sheets("Main").Range("A7:A100")   '<<< ??
Set aaSh = ThisWorkbook.Sheets("NDC")                       '<<< ??
Col2Copy = 10                                               '<<< How many cols to copy?
'
aaSh.Range("A2").Resize(10000, Col2Copy + 5).ClearContents  '<<< Clear sheets NDC
Application.ScreenUpdating = False
    FileToOpen = Application.GetOpenFilename(Title:="Browse for your File & Import Range", FileFilter:="Excel Files (*.xls*),*xls*")
    If FileToOpen <> False Then
        Set OpenBook = Application.Workbooks.Open(FileToOpen)
        Sheets(1).Select
        Debug.Print "AA", ActiveSheet.Name, Range("P2").Value, cCheck.Cells(1, 1).Value
        aaNext = 2
        ' Stop
        Debug.Print "BB", Cells(Rows.Count, "P").End(xlUp).Row
        For I = 1 To Cells(Rows.Count, "P").End(xlUp).Row
            myMatch = Application.Match(Cells(I, "P").Value, cCheck, False)
            Debug.Print "**" & I, Cells(I, "P").Value, myMatch
            If Not IsError(myMatch) Then
                Debug.Print "##" & I, aaNext
                aaSh.Cells(aaNext, 1).Resize(1, Col2Copy).Value = Cells(I, "A").Resize(1, Col2Copy).Value
                aaNext = aaNext + 1
            End If
        Next I
        OpenBook.Close False
        Debug.Print "CC", I, aaNext
    End If
Application.ScreenUpdating = True

There are several "Debug.Print" that will save into the "Immediate window" of your vba some debug information. Run the code and check what happened to sheet NDC. Then open the vba and open the Immediate window (typing Contr-g should do the job, or use Menu /View /Immediate Window). Copy what is listed there and paste it into your next message.

Bye

NB: the macro need to be saved in Updated SQL Query 2.0.xlsx (now .xlsm)

 

[Cr864]

I seem you are not going to look at the situation tonight, this gave me time for some more testing option, in addition to what I wrote in my previous message (see above):
-prepare a Test file with few lines (let's say 30), and of corse some of these lines have a CheckNo in column P listed in Main!A7:A100 and other are not listed.
-use the following code:

Dim FileToOpen As Variant
Dim OpenBook As Workbook

Dim cCheck As Range, aaNext As Long, aaSh As Worksheet
Dim I As Long, myMatch, Col2Copy As Long
'
Set cCheck = ThisWorkbook.Sheets("Main").Range("A7:A100")   '<<< ??
Set aaSh = ThisWorkbook.Sheets("NDC")                       '<<< ??
Col2Copy = 10                                               '<<< How many cols to copy?
'
aaSh.Range("A2").Resize(10000, Col2Copy + 5).ClearContents  '<<< Clear sheets NDC
Application.ScreenUpdating = False
    FileToOpen = Application.GetOpenFilename(Title:="Browse for your File & Import Range", FileFilter:="Excel Files (*.xls*),*xls*")
    If FileToOpen <> False Then
        Set OpenBook = Application.Workbooks.Open(FileToOpen)
        Sheets(1).Select
        Debug.Print "AA", ActiveSheet.Name, Range("P2").Value, cCheck.Cells(1, 1).Value
        aaNext = 2
        ' Stop
        Debug.Print "BB", Cells(Rows.Count, "P").End(xlUp).Row
        For I = 1 To Cells(Rows.Count, "P").End(xlUp).Row
            myMatch = Application.Match(Cells(I, "P").Value, cCheck, False)
            Debug.Print "**" & I, Cells(I, "P").Value, myMatch
            If Not IsError(myMatch) Then
                Debug.Print "##" & I, aaNext
                aaSh.Cells(aaNext, 1).Resize(1, Col2Copy).Value = Cells(I, "A").Resize(1, Col2Copy).Value
                aaNext = aaNext + 1
            End If
        Next I
        OpenBook.Close False
        Debug.Print "CC", I, aaNext
    End If
Application.ScreenUpdating = True

There are several "Debug.Print" that will save into the "Immediate window" of your vba some debug information. Run the code and check what happened to sheet NDC. Then open the vba and open the Immediate window (typing Contr-g should do the job, or use Menu /View /Immediate Window). Copy what is listed there and paste it into your next message.

Bye

NB: the macro need to be saved in Updated SQL Query 2.0.xlsx (now .xlsm)

Hey Anthony,

Sorry i didnt get back with you last night, but i have tested out the code you wrote with the debug prints. Here is the result:

AA ExcelDestination 299284
BB 1
**1 Error 2042
CC 2 2

"ExcelDestination" is the name of the first sheet in the user selected file, which is correct.

Here is the data that i used in my Main!A7:A100 range, i confirmed these values exist in the user selected file in column P:

299284​

717860​

1064632​

2203875​

I know you mentioned above i may need to provide a test environment, would you like me to put some sample data together and share it here?

Thank you

 

[Anthony47]

BB 1
**1 Error 2042

From what I see, column P doesn't have any list in it. Did you use Stop (my first message yesterday evening) to check that the active worksheet is what it should be?
Bye