select values once from another workbook

**Jimbs4** · 05-25-2017, 02:47 AM

Hi all,

so have a bit of a complex question that at the moment is above my VBA knowledge. Here's the context;

So I have the worksheet with the macro and where I would eventually like the data. First cell to fill with data would be B2 in sheet 1. So I have built another script that builds the file path and puts this value into U10 and the file name in U11. This should then use this information to open and find the sheet to take the data from. In the 'from sheet' the names are in column E starting at E3. Each name occurs twice or more in this column so I just want it to loop through, find a name then find the next name (it ignores the same name if it finds it again) this loop finished once it finds the word 'END' in the column and pastes it all back into the original workbook. I've started with something I have used before but need a bit of help.

Thanks in advance if you can help!

Please Login or Register  to view this content.

**leelnich** · 05-25-2017, 04:09 AM

Hi Jimbs4-
Path and Filename for the source workbook are in U10:U11. Where (or what) is the source worksheet name?
Are the names in column E of the source worksheet sorted?

**Jimbs4** · 05-25-2017, 04:16 AM

Hi leelnich,

so the file path and name are contained in cells U10 and U11 respectively in sheet1 of the workbook.

generally yes the names are sorted in ascending order but not 100% of the time. Below is an example of column E;

0
VC1300N52
VC1300N52

VC1300N53
VC1300N53

VC1300N54
VC1300N54

VC1300N55
VC1300N55

VC1300N56
VC1300N56

VC1300N57
VC1300N57

VC1300N58
VC1300N58

VC1300N59
VC1300N59

VC1300N60
VC1300N60

VC1300N61
VC1300N61

VC1300N62
VC1300N62

END

**leelnich** · 05-25-2017, 04:35 AM

So column E above is on a worksheet named "Sheet1" of the Source Workbook?

**Jimbs4** · 05-25-2017, 04:53 AM

Yes, it should be. The value in U11 is 'test_workbook' but this is dynamic and the name changes so it just references whatever file name is pre-defined

**leelnich** · 05-25-2017, 06:00 AM

Ok, try this. The code uses the AdvancedFilter feature to copy only unique values to the destination.
Paste it in a standard module in you Destination workbook.

Please Login or Register  to view this content.

NOTE: AdFilter operations require a header, so the source range includes row 2. This is copied to the destination and later removed.

Please click the Add Reputation star below any helpful posts, and if you have your answer, mark your thread as SOLVED (Thread Tools up top). Thanks!-Lee

**Jimbs4** · 05-25-2017, 08:05 AM

Thanks Lee! That works well. Just a couple of questions so I can understand what it's doing. I'm not familiar with the AdFilter, does it somehow separate out the double ups? Does it take the first occurance of the named value or the second? So I don't need a loop and 'end' for the loop to stop? Just asking as there is data in each row next to each name that I was going to incorporate a vlookup to capture and place in rows next to names in the destination sheet.

Again, thanks very much!

**leelnich** · 05-25-2017, 09:01 AM

This is an example of VBA borrowing from Excel's built-in functionality. The Advanced (Filter) button on the Data ribbon does the same thing. If you click that, you'll see you have 2 options: Filter the List in-place OR Copy to another location. There's also boxes for entering filtering criteria and a copy location, plus a check-box for Unique Values. Now, since the code said 'Unique = True' and gave it a destination, this Excel function filtered out the duplicates and copied the remaining cells to the new location. Then the code deleted the extra header at the top, plus all the blanks interspersed among the useful data. [Answers: 1)It keeps the first instance. 2)No loop/'End' - the code finds the last row.]

Filtering and Sorting are Excel's bread-and-butter. If you have time, you should explore these powerful tools, plus Pivot Tables for manipulating data in really useful ways.

**Jimbs4** · 05-25-2017, 09:24 AM

Excellent, Thanks Lee. That's a great explanation. Yes agree, I really do like it's filtering, familiar with pivot tables but was not with the advanced filtering, very handy. I see what you mean for filtering out duplicates, so for false I would have pasted all the double ups?

As I said earlier I was going to use a vlookup to capture other data in the rows alongside the names however the advanced filtering may be more efficient, can I switch between columns in this? so say I want the data in column 'B' pasted into destination cell 'G' exactly the same way can I add a few lines as below?

Please Login or Register  to view this content.

**leelnich** · 05-25-2017, 10:07 AM

Remember, the Source workbook is active, so you must tell VBA your destination is in another workbook, else it thinks you're referencing Range("G2") of the ACTIVE worksheet of the ACTIVE workbook.

Please Login or Register  to view this content.

NOTE:ThisWorkbook is a built-in reference to the workbook containing the code. I'm assuming that's your destination workbook.
In any event, you must specify "G2" as a Range object: Range("G2")

Also:

Please Login or Register  to view this content.

The "-1" at the end is there to skip copying your 'End' marker. If you remove the marker, remove the "-1".

select values once from another workbook

LinkBack

Thread Tools

Rate This Thread

Display

select values once from another workbook

Re: select values once from another workbook

Re: select values once from another workbook

Re: select values once from another workbook

Re: select values once from another workbook

Re: select values once from another workbook

Re: select values once from another workbook

Re: select values once from another workbook

Re: select values once from another workbook

Re: select values once from another workbook

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Copy Select Columns from closed workbook into active workbook

Use userform to add data to seperate workbook using combo box to select workbook.

Find row with specific values in workbook A and update with values from row in workbook B

[SOLVED] Copy Values From Each Workbook in Folder to a Single Sheet in New Workbook +Workbook names

how to select range of values from excel sheet and write to different workbook

[SOLVED] Select/Copy/Paste from workbook to workbook

[SOLVED] Select sheet tabs in workbook & save to separate workbook files

Bookmarks

Bookmarks

Posting Permissions