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

 

[Cr864]

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

I did, it stops on the correct sheet and here is some of the data in column P on that sheet after the stop. Does it matter that there are headers on the columns?

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
ExcelDestination

 

[Anthony47]

In which vba module did you insert the macro? (all what you read in the header of the vba window)

Bye

 

[Cr864]

In which vba module did you insert the macro? (all what you read in the header of the vba window)

Bye

Hey Anthony,

We're making progress! So, i took the code and inserted it into a new module and just clicked the run button on the module and it is pulling the data! But if i try to insert the same code into a button click sub, it does not pull the data back to the sheet.

What could cause that? But outside of that, the data is pulling now!



Thanks

 

[Anthony47]

The macro need to be stored into a "Standard Code Module", not a "Sheet module" or ThisWorkbook module on any other "Class module", or targetting the right data is a little bit more complex.
So leave the code wher it is now, and insert a

Call MyOtherMacro

into the Button_Click macro.

And of course you may remove all the Debug.Print lines

Bye

 

[Cr864]

The macro need to be stored into a "Standard Code Module", not a "Sheet module" or ThisWorkbook module on any other "Class module", or targetting the right data is a little bit more complex.
So leave the code wher it is now, and insert a

Call MyOtherMacro

into the Button_Click macro.

And of course you may remove all the Debug.Print lines

Bye

ANTHONY, You're a god. Thank you! Everything works now, thanks for having the patience and taking the time to work through that with me man!!

 

[Anthony47]

Thank you for the feedback