Pull Data from one Workbook to another not using Vlookup help!

[Grimlocc]

Hello,

I am trying to figure out a formula that will allow one spreadsheet to pull in data from another.

I have one spreadsheet that contains data relating to staff in columns A to BO and an Area Pay Code relating to each member of staff is in column S. I will call this spreadsheet Full Staff List.

I have a separate spreadsheet which I currently copy and paste data into from the Full Staff List and on this spreadsheet I only have staff who are on a particular Area Pay Code (code 7). I will call this spreadsheet Area Pay Code List.

What I would like to be able to do is make it so that the Area Pay Code List spreadsheet automatically looks at the Full Staff List spreadsheet, identifies all instances of code 7 in column S, then, for each member of staff on code 7, it brings in their name, Pay ID, their line manager, hours worked, pay grade and location. All of these items of data are on the Full Staff List spreadsheet along the row for each member of staff.

I know how to do a Vlookup but I don’t think that will work because I want to pull in data from more than one cell along a row.

Is there a way to do this? I’m trying to think of a formula that says:

Look in another spreadsheet at column S. Find all instances of pay code 7. For each code 7 you find, look along the same row and bring in data from cells x,y and z.

The difficulty I think is that the spreadsheet that I want this formula in has no references to look up against in the other spreadsheet other than knowing that I want pay code 7. Also, I wish to bring in data from multiple cells along the row but not necessarily cells that are next to each other.

Hope you can help and thanks very much!

 

[Grimlocc]

Hi Igold,

I have used alternative names for confidentiality reasons but what I have now done is created two experimental workbooks so that I can practice this properly. I will give the names and references that relate to these workbooks. I may not have been very clear earlier - I am hoping to pull data from one workbook to another rather than 1 sheet to another.

The header row on my source workbook is on row 1 and I am hoping to pull data into the second workbook under the header row which is on row 4. So, yes, the Area Pay Code sheet has headers on row 4.

However, the headers are not in an identical order, nor do they have exactly the same name but they can do. If it is easier to give them the same name, I will use the following:

On the sheet I want to pull the data into with the header on row 4, these are the headings and the order:

PIDNo | First Name / Surname | AD / G6 | FTE | SiP Grade | Location

These headings match the names on my source sheet but are in columns A, B, C, D, E and F whereas on the source workbook, they are different.

On my source workbook, the location comes between First Name/Surname and AD/G6. This is the order on my source workbook:

PidNo - column A
First name/ surname - column B
Location - column G
AD/G - column K
FTE - column M
Sip Grade - column N

The exact names of my experimental workbooks are:
Source Workbook = Experiment Staff List (Formerly Full Staff List)
Recipient Workbook = Experiment CCC Register (Formerly Area Pay Code)

So yes, I only want columns A to F filling on the recipient workbook and they would be filled with data from columns A, B, G, K, M, N on the source workbook.

I hope that makes things a bit clearer?

Thanks very much.

 

[igold]

Is the criteria for the records still "7" in column S on source sheet?

 

[igold]

If your criteria is still the same as asked above, then with this code residing in and run from the source workbook should meet your requirements. Please change the names of the workbooks in the code (highlighted in red) to your current names. Both workbooks should be open when the code is run from the source book. The code also assumes that there is only one worksheet in the destination workbook.

Credit should be given to Marcelo Branco whose code I copied and altered to get the visible range into an array.

Sub PullData()


    Dim lRow As Long, x As Long, i As Long, c As Integer, cc As Integer
    Dim wsFSL As Worksheet: Set wsFSL = Worksheets("[COLOR=#ff0000]Full Staff List[/COLOR]")
    Dim rngVisible As Range, rCell As Range
    Dim myArray() As Variant, finl
    Dim myCols(), nwArray As Variant
    myCols = Array(1, 2, 11, 13, 14, 7)
    
    lRow = wsFSL.Cells(Rows.Count, 1).End(xlUp).Row
    wsFSL.Range("$A$1:$BO$" & lRow).AutoFilter Field:=19, Criteria1:=7
    With wsFSL
        'Set the visible range
        With .AutoFilter.Range
            Set rngVisible = .Offset(1, 0).Resize(.Rows.Count - 1, 14) _
                  .SpecialCells(xlCellTypeVisible)
        End With
        i = rngVisible.Cells.Count / 14
        ReDim myArray(1 To i, 1 To 14)
        i = 1
        'Loop through visible range and populate the array
        For Each rCell In rngVisible
            x = x + 1
            myArray(i, x) = rCell
            If x = 14 Then
                x = 0
                i = i + 1
            End If
        Next rCell
    End With
    ReDim nwArray(1 To i - 1, 1 To 6)
    For c = LBound(myArray) To UBound(myArray)
        For cc = 1 To 6
            nwArray(c, cc) = myArray(c, myCols(cc - 1))
        Next
    Next
    Workbooks("[COLOR=#ff0000]APC.xlsm[/COLOR]").Activate
    Range("A5").Resize(UBound(nwArray, 1), UBound(nwArray, 2)) = nwArray
    
    wsFSL.AutoFilterMode = False
    
End Sub

 

[Grimlocc]

Hi Igold,

Thanks very much for this but I can't get it to work I keep getting a run time error 9, subscript out of range.

I think it may be the paths that I am using? The problem I have is that I can't post the path I'm using here for confidentiality reasons. The path I am using is the one where, if you click on File in the workbook, the info screen pops up with the address of the file - I am copying and pasting that into the space where the file names should be (where you indicated in red towards the top and bottom of the code). Or should the path just look like it does when you use a formula that references a separate workbook?

To run the code, I am in the Macro space (I get there by hitting ctrl+F11) and I hit F5 - that's the right way to do it right?

Also, no, code 7 isn't the real code but I just replace that with the real code in your coding so that shouldn't be an issue. I'm not sure what else I can do... I really appreciate the effort you've put in though.

 

[igold]

Using this method you do not need the paths of the workbooks, but both workbooks must be open for the code to work.

Other than changing the names (do not add the path) of the source and destination workbooks and the "code 7", do not change any other part of the code.

What line of code is highlighted as the error?

I just realized that I am telling you to change the name of the source workbook. I meant to say that you should change the name of the worksheet in source workbook in the first red highlight (then name of the book does not matter here. The name of the destination workbook must be changed).

 

[igold]

To answer another part of the question- Yes you can run the macro using F5 after you have opened the Visual Basic Editor using Ctrl+F11. Once you have it running (and it will run) the preferred method would be to (with both workbooks open) go to the source workbook/worksheet and use Alt+F8 to bring up the Macro Box and double click on the name of the Macro "Pull Data" to run it.

As a side to all of this, instead of using F5 to run the code, you can use F8 to step through the code. It will be a lot of clicking but if there is an error you will know immediately what line is throwing the error.

 

[Grimlocc]

Hi Igold,

Thanks for clearing it up for me - I have now managed to get the macro to run but it doesn't quite work properly. I don't get any more error messages but what happens is that when the macro runs, I can see it going through the data on my source sheet but it jumbles everything up, appearing to stop at around row 140ish. The cursor then appears in cell A5 in the destination workbook but no data is pulled in. So, it's definitely working to an extent, just not quite there...

 

[igold]

Can you better explain what you mean when you say it jumbles everything up. The way it is written you should see nothing happening to your data (other than the filter for your criteria on the source sheet). Everything is being written into arrays. When the cursor appears on the destination sheet it should then write the array to that sheet.

It is going to write what was in columns (1, 2, 11, 13, 14, 7) in that order on the source sheet into columns A:F on the destination sheet. Does that look right to you.