VBA code to pull values

Smallman · Oct 29, 2013

OK so neither of those workbooks have the code FICC

By just looking at the problem it most likely relates to my last post/lesson on the sheet code name. Check to make sure the sheet you want to past to has a sheet code name called Sheet2. See image below;

$D:\codename.jpg$

So I would suggest you upload the one with that code so I can run a test.

Also I mentioned earlier there is an error in the coding remove the ws reference before the Rows line.

Code:

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

Hope this helps.

Take care

Smallman

EDIT - I have also uploaded the image as I can't seem to see it on my screen.

Ajit Kiran · Oct 30, 2013

Hi Marcus,

Glad you're enjoying posting up here! Thanks very much. Yes, pls do suggest an alternative code when you get to.

Yes, debugging issues increase even when there is a small change to the sheet name. Also, just wanna understand this a little better. Is there a way to look for the headers without specifying them in the array?

i.e. when I find any of the headers present in the source file, the code should just take the values into another sheet. Like I'm doing the below.

Code:

ar = [{"Region", "Trader", "Desk", "Portfolio", "Business Unit", "Business Area"}]
arr = [{1,3,4,6,7,12}] 'Static Cols you are exporting to

Hope I am clear...

Ok, the below code to clear cells:

Code:

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

Can a code have 2 procedures within it i.e. Sub and End Sub occurring twice?

Thanks again!
Ajit Kiran

Ajit Kiran · Oct 30, 2013

Hi,

There seems to be a prob uploading the files. Hence giving you the code in here.
Please could you look into it?

Code:

'GRC MACRO:
 
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(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
 
 
'FICC MACRO:
 
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(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

Thanks again
Ajit Kiran

Smallman · Oct 30, 2013

Hi Ajit

If you could upload the files you are attempting to consolidate once more that would help. I have a tennis match this evening so may not get to it till tomorrow AM (Aussie time) but I will get to it if you upload them.

Take care

Smallman

Ajit Kiran · Oct 30, 2013

Hey! Good luck with your match! hope u've invoked RF n whacked some deadly smashes!

Anyway, have attached the spreadsheets herewith...

Thanks a bunch again!
Ajit Kiran

Smallman · Oct 31, 2013

Hi Ajit

Yeah I got up. All good, so just about to grab a chocholate chaser to celebrate.

Your first procedure can be simplied in the extreme. Seeing your full file helps that come out. You don't need to find each column as they line up perfectly so this.

Code:

Option Explicit
Sub FICC() 'One PnL App Data workbook remains closed!
Dim owb As Workbook
Dim ws As Worksheet
 
Set ws = Sheet2
Sheet2.[A2:F100].Clear
Set owb = Workbooks.Open("D:\OnePnLAppDataGRCnFICC.xlsm") 'Change Path
Sheets("FICC").[a1].CurrentRegion.Offset(1, 0).Copy ws.Cells(Rows.Count, 1).End(xlUp)(2)
owb.Close False 'No Save just close
End Sub

I have had to refer the sheet name above as it gets a bit tricky with two files open with the same sheet code name. I changed your file name to be what I would call it.

I will get back to you on the second procedure shortly.

Take care

Smallman

Smallman · Oct 31, 2013

Hi Ajit

You had a worksheet change event in the file. You need to get rid of that. Click in all the sheet objects and remove any code. You really do need to do this task.

OK I have looked at the second procedure which needs to be a bit more complex than the first. Paste this into the file you are running it from.

Code:

Option Explicit
 
Sub GRC() 'One PnL App Data workbook remains closed!
Dim ws As Worksheet
Dim ar As Variant
Dim arr As Variant
Dim j As Integer
Dim i As Integer
Dim sh As Worksheet
Dim owb As Workbook
 
Set ws = Sheet1
ws.[A2:L100].Clear
ar = Sheet3.Range("A2", Sheet3.Range("A65536").End(xlUp))
Set owb = Workbooks.Open("D:\OnePnLAppDataGRCnFICC.xlsm")
arr = [{1,3,4,6,7,12}] 'Static Cols you are exporting to
Set sh = Sheets("GRC")
 
    For i = 1 To UBound(ar)
        j = sh.Rows("1:1").Find(ar(i, 1)).Column
        sh.Range(Cells(2, j), sh.Cells(sh.Cells(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

Let me know if you have any queries about the workings.

Take care

Smallman

EDIT - I attached a workbook with the cleaned up sheet objects.

Ajit Kiran · Oct 31, 2013

Hi Marcus,

At the outset, I want to apologize for making you go round and round in circles!

I don't feel good about that myself!

And yes, thanks a ton for bringing up the above but I felt the below code is what I could understand better at the moment (given my nascent stage into the subject).

Code:

Option Explicit
 
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(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

Also, I'm attaching the 'final' version of the files herewith. I'm able to have the above procedure for 'GRC' working well but unable to accomplish the same for 'FICC'. Not sure what is wrong in my logic for 'FICC' code. Below is the FICC Code I've used:

Code:

Option Explicit
 
Sub FICC() 'One PnL App Data workbook remains closed!
Sheets("FICC").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 = Sheet2
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(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

I believe there's something wrong with the below line of code:

Code:

Set ws = Sheet2

The result for 'FICC' is identical to 'GRC' which is wrong given the values for FICC in "One PnL App Data (GRC n FICC) file.

Please could you look into the above procedures and help me understand where I'm going wrong?

I want to put a full stop to this - I don't want you to unnecessarily waste your time into this. It's been about 2-3 days and I know how much you were putting your efforts into this - so sorry about that.

Hopefully, this should be the last bit to wrap up. Thanks a zillion again!

The reason I kinda want to stick to the above procedures is because they're relatively easier to understand and puts me in a good stead when it comes to explaining the code to others. Hope I have not irked you by asking you to look into the above codes and attached files.

Just another thing, you'd notice the header range for both GRC & FICC are the same - I have kept that same deliberately. Thanks!

Rgs
Ajit Kiran

Smallman · Nov 1, 2013

Ajit

I will have a look at your files later this morning. Ahhh it is an enjoyable project. Don’t worry about all the questions. I could point you to some posts from years ago where I spend almost half a day on something and there was only tumble weed which greeted me. At least you give some feedback and this is at the simple end of vb development and any change takes a small amount of time. So lets just get you over the line and aid your learning and I will feel I have made the world a better place in a small way.

Take care

Smallman

Ajit Kiran · Nov 1, 2013

You brought a big smile on my grumpy face this morning!

Thanks mate.

Rgs
Ajit Kiran

Smallman · Nov 1, 2013

Hi Ajit

Thanks for your post. I got distracted.

I have had a look at your code and tried to rework it. Your coding should follow the following construct;

Variables
Assignment Variables to Items
Perform Actions

Your code above departs from this and I have realligned the process.

Here is the code. Tested it and it works. I changed the file name so watch that.

Code:

Option Explicit
 
Sub FICC() 'One PnL App Data workbook remains closed!
Dim ws As Worksheet
Dim ar As Variant
Dim arr As Variant
Dim j As Integer
Dim i As Integer
Dim sh As Worksheet
Dim owb As Workbook
 
Set ws = Sheet2
ws.[A2:L100].Clear
ar = Sheet3.Range("A2", Sheet3.Range("A65536").End(xlUp))
Set owb = Workbooks.Open("D:\OnePnLAppDataGRCnFICC.xlsm")
arr = [{1,3,4,6,7,12}] 'Static Cols you are exporting to
Set sh = Sheets("FICC")
 
    For i = 1 To UBound(ar)
        j = sh.Rows("1:1").Find(ar(i, 1)).Column
        sh.Range(Cells(2, j), sh.Cells(sh.Cells(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

I will attach a workbook shortly.

Take care

Smallman

Ajit Kiran · Nov 2, 2013

Hi Marcus,

Sorry, couldn't get back to you sooner...wasn't keeping well...

Thanks again for revisiting the code.
Have tested it n I seem to be getting a run time error on the below line...is there something I'm missing here?

Code:

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

Pls could you look into it? Lemme attach the files again for your perusal.

Have a great weekend ahead!

Thanks a bunch again
Ajit Kiran

Smallman · Nov 2, 2013

Ajit

I just rang the code again and this time from my office computer and no error. If I can't replicate the error then I can't do anything other than provide suggestions. Firstly is the file opening. You can step through the code but Pressing F8 for each line item. Make sure the file actually opens.

If the file does open there should be no reason other than the sheet names being different to fail. I just downloaded the files made sure the path was correct and it ran perfectly.

Take care

Smallman

Ajit Kiran · Nov 2, 2013

Hey, thanks very much.

Will take a look at it.

Ajit Kiran

Ajit Kiran · Nov 3, 2013

hey,

Check this out...guess the attached help my requirement...

Thanks a zillion for all ur help mate!

Ajit Kiran

Ajit Kiran · Nov 3, 2013

hey,

Check this out...guess the attached help my requirement...

Thanks a zillion for all ur help mate!

Ajit Kiran

Smallman · Nov 3, 2013

Thanks Ajit - All the very best with your project and it was a genuine pleasure to meet you.

Take care

Smallman

Ajit Kiran · Nov 3, 2013

Likewise Marcus...

Thanks much!

Ajit Kiran

VBA code to pull values

Excel Ninja

Attachments

Member

Member

Excel Ninja

Member

Attachments

Excel Ninja

Excel Ninja

Attachments

Member

Attachments

Excel Ninja

Member

Excel Ninja

Attachments

Member

Attachments

Excel Ninja

Member

Member

Attachments

Member

Excel Ninja

Member