VBA to pull rows from a user selected workbook

Cr864 · Jan 26, 2022

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!

VBA Code:

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

8

9

10

11

12

13

14

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 · Jan 27, 2022

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

bye

Cr864 · Jan 27, 2022

Anthony47 said:
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 · Jan 27, 2022

Cr864 said:
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 · Jan 27, 2022

Try this code:

VBA 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 · Jan 27, 2022

Anthony47 said:

Try this code:

VBA 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 · Jan 27, 2022

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:

VBA Code:

        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 · Jan 27, 2022

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:

VBA 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 · Jan 28, 2022

Anthony47 said:
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:

VBA 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 · Jan 28, 2022

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

VBA to pull rows from a user selected workbook

Cr864

New Member

Excel Facts

Anthony47

Well-known Member

Cr864

New Member

Cr864

New Member

Anthony47

Well-known Member

Cr864

New Member

Anthony47

Well-known Member

Anthony47

Well-known Member

Cr864

New Member

Anthony47

Well-known Member

Similar threads

Forum statistics

Share this page

VBA to pull rows from a user selected workbook

New Member

Excel Facts

Well-known Member

New Member

New Member

Well-known Member

New Member

Well-known Member

Well-known Member

New Member

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock