VBA code to pull values

[Ajit Kiran]

Hello Guys!

I'm looking at a macro which can pull values into a spreadsheet from another when the column headings match.

For eg., the VB code has to pull values from "Source Data" file saved down in E: to "Extract Values into this" file when the column headings i.e. A, B, C etc match with the other.

I would need to perform this kind of task quite a no. of times during the day. Hence, is it necessary to save down the "Source Data" file in the same location?
Have attached the files for your reference.

Pls could someone take a look at this and provide a VB code? Thanks a ton for the help in advance!

Thanks
Ajit Kiran

 

[Smallman]

Ajit

In your example all of the column headings match. Is this indicative? I was expecting some of the headers not to match.

Take care

Smallman

 

[Ajit Kiran]

Ajit

In your example all of the column headings match. Is this indicative? I was expecting some of the headers not to match.

Take care

Smallman

 

[Ajit Kiran]

Hi

Thanks very much for reverting!

Yes, I have a spreadsheet names "Export values into this" whose headers quite match with the headers on "Source Data." However, the headers are spread out i.e. header A could be in Col B or C and header B could be spaced apart 3 columns further and so forth. There's no consistency. Thanks again!

Well, the code should be able to ignore those headers where they don't match. Is that possible please?

Thanks again!

Ajit Kiran

 

[Smallman]

Ajit

Your example should have reflected this. I will have a look at this when I return home. I seem to recall answering an identical question on chandoo a couple of weeks back.

Take care

Smallman

 

[Ajit Kiran]

Brilliant! Thanks a zillion again! Hope u don't mind me popping a gentle reminder some time tomorrow?

Sorry 'bout the miss though...
Thanks again!
Ajit Kiran

 

[Smallman]

Which Headings are of order the Source Data workbook or the Extract Values workbook. I am going to assume it is the Source Data workbook. Also which workbook do you want the procedure to run from. I am going to assume the Source Data workbook. Once again correct information to start with is key when posting. Unless you get back to the forum in the next 15 minutes you get the above.

Take care

Smallman

 

[Smallman]

Ajit

Here you go.

Put this in the Source Data file. Change the path. Make sure the data you want to move is in sheet1 and the data you are importing to is also the first sheet in the file. If not change either it to a relevant sheet name sheets("Sheet1") etc. I have tested on my computer and it works wonderfully well. If it does not work for you get back to the forum and I will assist you with your learning.

Option Explicit
 
Sub Goski()
Dim ws As Worksheet
Dim ar As Variant
Dim j As Integer
Dim i As Integer
Dim owb As Workbook
 
Set ws = Sheet1
ar = [{"A", "B", "C", "D"}]
Set owb = Workbooks.Open("C:\Users\HYMC\Excel\Helping\ADel\Extract Values into this.xlsm")
 
    For i = 1 To 4
        j = ws.Rows("1:1").Find(ar(i)).Column
        ws.Range(ws.Cells(3, j), ws.Cells(ws.Cells(ws.Rows.Count, j).End(xlUp).Row, j)).Copy _
        owb.Sheets(1).Cells(Rows.Count, i).End(xlUp)(2)
    Next i
    owb.Close True 'Save Changes and close
End Sub

Take care

Smallman

 

[Ajit Kiran]

Hi

Thanks again! Just a couple of things here:

1. Headings are of order in the Extract Values workbook
2. Procedure to run from - Extract Values Workbook

Also, I've changed the location of my workbook to the below while running in the previous format. I get an error saying the file doesn't exist there. I know I'm missing something small here...apologies, not too tech savvy...pls could you tell me where would I be able to enable the location in the excel spreadsheet? I tried enabling it thru the options menu in excel but to no avail...

"E:\Users\Extract Values into this.xlsm")

 

[Smallman]

Ajit

Did you save your file as an Excel Macro enabled workbook or did you just save it as a regular workbook? You will need to change the file suffix accordingly. This is most likely your issue.

Take care

Smallman

 

[Ajit Kiran]

Hey, I saved the files down as xlsm. Thanks v much.

However, below are 2 which I'm not too sure to fix 'em up:

1. Headings are of order in the Extract Values workbook
2. Procedure to run from - Extract Values Workbook

Also, have edited the code to read 2 from 3 earlier in your code and manage to get all the values there...thanks a bunch...pls could you take another look at the code to incorporate the above 2 points?

Thanks a ton!

ws.Range(ws.Cells(2, j), ws.Cells(ws.Cells(ws.Rows.Count, j).End(xlUp).Row, j)).Copy _
        owb.Sheets(1).Cells(Rows.Count, i).End(xlUp)(2)

 

[Smallman]

Hi Ajit

This is just a post to get some clarification. Attached is your Source Data workbook where I ran the procedure from. See in this file how I have spaced out the headings so the find is not looking for headings which are all together. Spinning this round so it would run form the other workbook is not a problem. All you need do is change the path. Can you upload a file which has indicative headers for the other workbook.

Take care

Smallman

 

[Ajit Kiran]

Hi,

Just like to reiterate the above mentioned:

1. Headings are of order in the Extract Values workbook
2. Procedure to run from - Extract Values Workbook

The headers in Source Data are dynamic but the headers in Extract Values into this workbook are static. Hence, I should be able to run the procedure in Extract Values Workbook looking up the dynamic header range in Source Data (so as to take the values from source data into extract values into this workbook).

I in fact tried changing the location like u mentioned above but doesn't seem to be working...pls could you look into it?

Have attached the spreadsheet which you asked for...
Thanks
Ajit Kiran

 

[Smallman]

Hi Ajit

Thanks for uploading a file. That will be very helpful.

It is late here. I will have to retire and address this issue in the morning if it is not solved in the mean time.

Catch you tomorrow.

Smallman

 

[Ajit Kiran]

Brilliant! Thanks a ton for being so helpful!

Good night! Sleep tight

Ajit Kiran

 

[Smallman]

Ajit

Finally back to it. I am not dealing with that duplicate heading you have way out there in Col H. That is way out of play based on what you have described to date. Clarity, it is simply priceless.

Anyways based on the information you have given me you run this procedure from the Extract workbook. Once again it works well at this end from initial testing. I would change the file path and name and run with it on the source data you provided.

Option Explicit
 
Sub Goski() 'Values workbook remains closed!
Dim ws As Worksheet
Dim ar As Variant
Dim arr As Variant
Dim j As Integer
Dim i As Integer
Dim owb As Workbook
 
Set ws = Sheet1
Set owb = Workbooks.Open("D:\Values.xlsm")
ar = [{"A", "B", "C", "D"}]
arr = [{1,3,5,6}] 'Static Cols you are exporting to
 
'Loop through instances of ar
    For i = 1 To 4
        j = Rows("1:1").Find(ar(i)).Column
        Range(Cells(2, j), Cells(Cells(ws.Rows.Count, j).End(xlUp).Row, j)).Copy _
        ws.Cells(Rows.Count, arr(i)).End(xlUp)(2)
    Next i
  owb.Close False 'No Save just close
End Sub

Take care

Smallman

 

[Ajit Kiran]

Hey, Thanks much again. N yea apols for not being clear...

Will test this and let u know of the results...thanks a ton again!

Ajit Kiran

 

[Smallman]

Thanks Ajit

Just post back if anything does not work as expected and I will do my best.

Smallman

 

[Ajit Kiran]

Hey hi,

Thanks a bunch again! Firstly, I'd like to confirm the below:

1. Ran the below procedure from Extract Values workbook attached by you above

2. The values which get exported are turning out to be different (pls take a look at the results I've got in "Extract Values_Result" workbook). Below are the observations:

a. Header B in col B from "Values" workbook has a sub total of 165 whereas Header B in Col F of "Extract Values_Result" workbook has a sub total of 285 (where it should ideally match with 165 from "Values" wb)

b. Same is the case with other headers where the sub totals don't match...

c. The point of recon would be the values in "Values" wb i.e. what ever is found in "Values" wb must be exported to "Extract Values_Result" wb when the headers match.

Will it be possible for you to revisit this, please?

Thanks
Ajit Kiran

Option Explicit
 
Sub Goski() 'Values workbook remains closed!
Dim ws As Worksheet
Dim ar As Variant
Dim arr As Variant
Dim j As Integer
Dim i As Integer
Dim owb As Workbook
 
Set ws = Sheet1
Set owb = Workbooks.Open("D:\Values.xlsm")
 
ar = [{"A", "B", "C", "D"}]
arr = [{1,3,5,6}] 'Static Cols you are exporting to
 
'Loop through instances of ar
    For i = 1 To 4
        j = Rows("1:1").Find(ar(i)).Column
        Range(Cells(2, j), Cells(Cells(ws.Rows.Count, j).End(xlUp).Row, j)).Copy _
        ws.Cells(Rows.Count, arr(i)).End(xlUp)(2)
    Next i
    owb.Close False 'No Save just close
End Sub

 

[Smallman]

Ajit

Thanks for getting back to Chandoo.

I am not going to alter the code. I am going to let you alter the code. Here is where your learning shall begin. You should not be wedded to forums for this sort of solution. Ok so pay close attention.

ar = [{"A", "B", "C", "D"}]
arr = [{1,3,5,6}]

The above describes a logical order. The Letter “A” will be searched for first and it will be returned to COL 1. “B” will be searched second and it will be returned to Col 3. The process will continue till all of the letters have been searched for in the array ar and put in their corresponding position in the Extract Values workbook. The corresponding position is represented by the variant Arr. All that needs to change in the above code is for you to alter the sequence. For example in your code B needs to return to Column ?

arr = [{1,3,5,6}]

This is the answer I will not give you, because my nephew who is learning numbers now could figure this one out. I want you to think about it and post the above line back to the forum so others can learn from your solution. Believe me it is better this way.

You will do well, I have complete confidence.


Smallman

 

[Ajit Kiran]

Hey!!

At the outset, let me thank you profusely for being so helpful n at the same time helping others learn!! You simply rock mate!! I like your approach!

Well, not to say I didn't understand the code you've given - I did pretty much understand the logic from day one. However, I'd have to admit that the logic completely hasn't gotten into me. For eg., I do understand the logic behind 2 arrays the code talks above but I gotta admit that I haven't understood that in its entirety. I do, now (with your help)!!

arr = [{1,6,3,5}]

I have amended the code to reflect the above and it works like a charm!

However, I'm trying to understand this bit of code well...

Range(Cells(2, j), Cells(Cells(ws.Rows.Count, j).End(xlUp).Row, j)).Copy _
        ws.Cells(Rows.Count, arr(i)).End(xlUp)(2)

Please could you help me understand this last bit?

Now that I'm pretty much done with this code at a rudimentary level, I'll take it to the next level in applying this at my work place...I'm sure I'll knock it off...

Also, personally I have a great penchant for VBA as a language and putting in my best efforts to learn from various books / sources...I've started reading this : Microsoft Excel VBA Programming for Dummies 2010. Is there a better resource for novices like me? Pls could you guide me?


Gratitude!!
Give a man a fish, and you feed him for a day; show him how to catch fish, and you feed him for a lifetime.

Ajit Kiran

 

[Smallman]

Hi Ajit

You are too kind. There are many great helpers on this site, I am only a very small part of this vibrant community. There is some genuine talent in here.

This;

Microsoft Excel VBA Programming for Dummies 2010. Is there a better resource for novices like me? Pls could you guide me?

Yes - it is called Chandoo Forum. Come on this website everyday - did I say everyday? Yes I did! Don't let a day pass when you don't read many different problems on this forum. When you feel confident try to solve the problems and compare your answer to the gurus who actually do solve them. Learn from any differences. You will notice your learning increase very quickly when you dedicate yourself to a cause and keep at it. In life, as in coding, nothing replaces perseverance - nothing!!!

This;

However, I'm trying to understand this bit of code well..

Range(Cells(2, j), Cells(Cells(ws.Rows.Count, j).End(xlUp).Row, j)).Copy _
 
        ws.Cells(Rows.Count, arr(i)).End(xlUp)(2)

In order to explain the above we have to go back 2 steps. Firstly

Dim j as Integer

We declare j as an Integer to capture the column number of the Find procedure below

j = Rows("1:1").Find(ar(i)).Column

In vba the Cells method is represented as such;

Cells(Row Number, Column number)

Assuming for example j = 1 the above becomes;

Cells(2,1) this translates to A2 which probably means more.

Now we are trapping a range to Copy which is represented by the start of a range and the end of a range. Typically A2:A10 for example but we need to do it so we capture data dynamically. So we are looking to capture;

Cells(Start row number, Start Col Number), Cells(row End number, End Col Number)

The first part we have,

Cells(2,1) the second part looks in Col 1 and finds the last used cell in Col 1 this is represented by ;

Cells(Cells(Rows.Count, j).End(xlUp).Row

You will notice I dropped a ws from the original code. There was a mistake in the coding but probably will have no effect if you leave it in. Anyways the above traps the last used cell in Col 1. So now we have the start and the end so it should look something like this (using 15 as an example only).

Cells(2, 1), Cells(15, 1)

Or

A2:A15

So the code will copy A2 to A15 and it will paste that data in the Last Used Cell Plus one

ws.Cells(Rows.Count, arr(i)).End(xlUp)(2)

The above grabs the last used cell of the Column which you specify in the Array. I learnt this only by typing it over and over again. I now understand what it does but in the early years I just forced myself to type everyting I could and after a while it stuck and the theory came shortly after when you get a feel for what each element of the code does.

Phew – it takes a lot of writing to explain a simple bit of code. Maybe vb only gets simple the more you understand it.

Take care Ajit

Marcus Small
(Smallman)

 

[Ajit Kiran]

Oh! It isn't my kindness certainly but solely your proficiency! I don't say this for the heck of it or to puff you up but honestly felt so...and more importantly your interest in helping others learn and sharing the knowledge...

Thanks again for such a succinct explanation! Actually, is it ok if I run the below code 1st and then perform the above procedure? Since I have data to perform this procedure everyday and would need to delete previous day's info first...Assuming I've data from range A2:G100 and this needs to be deleted...

Sub clr()
 
Sheets("Sheet1").Select
Range("A2:G100").Select
Selection.Clear
End Sub

Can I perform this clear range code first and the perform the procedure you've given? Hope that wouldn't cause any problem?

Rgs,
Ajit Kiran

 

[Ajit Kiran]

Further to the above, I've tweaked out the code to suit my needs. Have attached the files for your kind perusal.

GRC macro works well but FICC doesn't. In fact FICC takes the same data as it does for GRC. Pls could you help me with this?

Sub GRC() 'One PnL App Data workbook remains closed!
Sheets("GRC").Select
Range("A2:L100").Select
Selection.Clear
 
Dim ws As Worksheet
Dim ar As Variant
Dim arr As Variant
Dim j As Integer
Dim i As Integer
Dim owb As Workbook
 
Set ws = Sheet1
Set owb = Workbooks.Open("E:\One PnL App Data (GRC n FICC).xlsm")
 
ar = [{"Region", "Trader", "Desk", "Portfolio", "Business Unit", "Business Area"}]
arr = [{1,3,4,6,7,12}] 'Static Cols you are exporting to
 
'Loop through instances of ar
    For i = 1 To 6
        j = Rows("1:1").Find(ar(i)).Column
        Range(Cells(2, j), Cells(Cells(ws.Rows.Count, j).End(xlUp).Row, j)).Copy _
        ws.Cells(Rows.Count, arr(i)).End(xlUp)(2)
    Next i
    owb.Close False 'No Save just close
End Sub
 
Sub FICC() 'One PnL App Data workbook remains closed!
Sheets("FICC").Select
Range("A2:F100").Select
Selection.Clear
 
Dim ws As Worksheet
Dim ar As Variant
Dim arr As Variant
Dim j As Integer
Dim i As Integer
Dim owb As Workbook
 
Set ws = Sheet2
Set owb = Workbooks.Open("E:\One PnL App Data (GRC n FICC).xlsm")
 
ar = [{"Business Area", "Business Unit", "Portfolio", "Desk", "Trader", "Region"}]
arr = [{1,2,3,4,5,6}] 'Static Cols you are exporting to
 
'Loop through instances of ar
    For i = 1 To 6
        j = Rows("1:1").Find(ar(i)).Column
        Range(Cells(2, j), Cells(Cells(ws.Rows.Count, j).End(xlUp).Row, j)).Copy _
        ws.Cells(Rows.Count, arr(i)).End(xlUp)(2)
    Next i
    owb.Close False 'No Save just close
End Sub

To clarify, "Greens Vs One PnL App" workbook is the one from where I run the procedure and "One PnL App Data (GRC n FICC)" workbook is the source data from where data must be taken into "Greens Vs One PnL App"

Hope the above is clear.

Pls could you look into this and help me? I believe the following line of code is the one where there is a problem for FICC:

Set ws = Sheet2

Thanks a ton again!
Ajit Kiran

 

[Smallman]

Hi Ajit

Thanks for your update. I am enjoying this post. I will answer post 23 first. I will suggest an alternative to that code you are using if you don't mind. In VBA you almost never have to select anything. It slows code down and it is not necessary. I have seen instances where the only way to get from A to B is through selection but this is very very rare. Onto the next point when refering to worksheets I like to use the sheets codename. That's because you must remember to update the code when you change the sheet's name. Not only is that a lot of trouble, users are apt to change a sheet's name so it can happen often. When the sheet name changes if the code does not change too then DEBUG!!!

This is my personal opinion but I see every other method as valid but ultimately inferor because of the above and you will rarely see my coding refering to a sheet name. This method offers better protection from change.

I have used the method below. The only thing you have to be mindful of is getting the sheet code name right in the first place.

Sub clr()
Sheet1.[A2:G100].Clear
End Sub

I would add the active line to the top of your first procedure without creating a new macro.

Anyways I will have a look at your other query now.

Take care

Smallman