HELP! - VBA to VLookup, Copy & Paste

[Joey86]

Hi Guys,

I really need help trying to get a VBA macro to do a Vlookup on another sheet then to find all the rows with that value, then copy them and paste them on another sheet, hopefully this is possible and that someone can help cause I don't have any knowledge on VBA.

Ok so I have 2 sheets, 'Daily Report' and 'Exceptions'. On the 'Daily Report' sheet when a user changes the sections in yellow shown below, I'm needing the macro to go to the 'Exceptions' page and lookup that selection criteria then copy all the values (might be more than 1 row) then it needs to come back to the 'Daily Report' sheet and paste the values down below in the green.

Daily Report Sheet

Exceptions Sheet

 

[Andrew Poulsom]

You could use Advanced Filter:

http://www.contextures.com/xladvfilter01.html#ExtractWs

The macro recorder will give you the code you need to refresh it.

 

[Joey86]

Hi Andrew, thanks for the quick response, I tried it with the Exceptions Sheet, although it gave me an error of: 'You can only copy filtered data to the active sheet', I've uploaded a template of it here if it helps: Test_Book.xlsx

Thanks

 

[Andrew Poulsom]

Did you read this?

http://www.contextures.com/xladvfilter01.html#ExtractWs

 

[Joey86]

Yes, followed the video exactly, although now it's giving me the error of 'The extract range has a missing or invalid field name' not quite sure what that is. See the Exceptions sheet is a list from SharePoint that's feeding into it as a table so when I select the advanced filter as per below it gives me the error:
- List Range: Table_owssvr_13[[#All],[Customer]:[SLA Exempt]]
- Criteria Range: 'Consolidated Daily Report'!$F$7
- Copy To: 'Consolidated Daily Report'!$B$31:$K$44

 

[Mindpsyche]

Check if your column headers in the source range, criteria range and destination are the same (No spelling differences / i'm not sure about case sensitivity). Field names refers to column names I think.

 

[Andrew Poulsom]

In the workbook you posted, insert 2 rows above row 19 on sheet Daily Report. In B19 enter Customer. In B20 enter =B7. Select A1 and use the following Advanced filter settings:

List range: Exceptions!$A$1:$M$5
Criteria range: $B$19:$B$20
Copy to: $B$21:$J$21

Result:

Excel 2010
	B	C	D	E	F	G	H	I	J
19	Customer
20	Customer 1
21	Customer	Date	Server	Error	Sequence	Reference	Reason	Current Status	Exempt
22	Customer 1	09/09/2013	ABC	123	987	00-00-00	Testing	Good	Yes
23	Customer 1	10/09/2013	ABC	123	987	00-00-00	Testing	Good	Yes
24	Customer 1	11/09/2013	ABC	123	987	00-00-00	Testing	Good	Yes
Daily Report

 

[Joey86]

Yep, exactly the same, would formatting the headers on the 'Consolidated Daily Report' sheet make any difference like centering them in the cell?

 

[Joey86]

Andrew I tried it in the test worksheet and it worked fine, but for some reason in the actual worksheet it won't work also when you change the customer in B7 it's not changing over, is that the part the macro is supposed to record like you said earlier?

 

[Andrew Poulsom]

The filter will need to be updated when the Customer changes.

Please copy the headings of the Filter range and paste them into a reply. Then copy the headings in the extract range and paste them.