Populate arrays in multiple tabs from seperate spreadsheet

[VBA Rookie]

Hi guys,

I'm really hoping someone can help me with this. I have a particular task I'm trying to do in excel and up until now no one I've asked has been able to crack it. I've been told its not possible, but I'm hoping someone here will prove them wrong.

Basically, what I'm trying to do is the following:

I have two separate spreadsheets:

• Spreadsheet A - contains a tab called "Database" in which each row is an entry. One column (B) contains a unique client code for each entry and another column (H) contains a unique code for an asset held by that client. A client can hold any number of assets and any asset can be held by more than one client.
• Spreadsheet B summarises each client's assets. Each client has its own tab with its unique identifier in cell B3, and tabs run from tab"1" to tab "36". Each tab has a list of the asset codes that the client holds from Cell B20 to Cell B60.

Periodically the database is refreshed and at the moment I have to manually filter the "Database" Tab in Spreadsheet A by each client code and copy & paste the asset numbers into the relevant client tab in Spreadsheet B. I have to repeat this for each client which take ages , so I really want to write a macro which will automate this.
The steps that I need the macro to perform are:

• Go to Tab "1" in Spreadsheet B.
• Read & remember client code in cell B3
• Go to Tab "Database" in Spreadsheet A.
• Search down column B, when value matches currently saved client code, return value in column H (asset code).
• Return to Spreadsheet B and enter value in cell B20 in Tab "1".
• Return to Tab "Database" in Spreadsheet A and carry on searching down column B and when value matches current client code, return value in column H (asset code).
• Return to Spreadsheet B and enter value in cell B21 in Tab "1".
• Return to Tab "Database" in Spreadsheet A and carry on searching down column B and when value matches current client code, return value in column H (asset code).
• Return to Spreadsheet B and enter value in cell B21 in Tab "1".
• Repeat until no more client codes match.
• Lastly, I need the above to repeat for each Tabs 1-36 in Spreadsheet B.

I been struggling with this a while as my VBA skills are very limited and I can never get the syntax correct. Any help on solving this would be very much appreciated.

Thanks,

Richard.

[JBeaucaire]

Click GO ADVANCED and use the paperclip icon to post up a desensitized copies of your workbooks. Make sure the sample workbooks properly demonstrate the raw data and the parsed data.

[VBA Rookie]

Jerry,

Thanks for your interest. I've attached a stripped down desensitized version of the spreadsheets. The cells are slightly different to what I described above. The values I want to be returned from the "Database" tab in Spreadsheet A is in column E (not H) titled "Sec ID". And the cells in each tab of Spreadsheet B that I want the data placed in are A74 to A113 (not B20 to B80) where you can see I have already pasted the Sec IDs manually.

Many thanks,

Rich

[JBeaucaire]

I can find no match method to determine how the sample data in sheetA was parsed to sheetB tabs. It appears you gave every client the same name instead of Client1, Client2, etc.

Without a clear method of seeing how you got data from the Database onto Tab1, I can't suggest anything.

[VBA Rookie]

Sorry Jerry, I don't fully understand. At the moment there is no match method as that it what I'm trying to get a macro to do. I've tried Vlookups, but that stops at the first record that matches the client code, and SUMPRODUCT doesn't work becuase it doesn't returned the matched data in cells one after another.

I got the data from the database to Tab1 by filtering the Database tab in Spreadseet A by the relevent Client Code (column B) using the drop down at the top and copy and pasting the Sec IDs that result into the coresponding tab in spreadshet B. The relevant client code for each tab is in cell C3 in spreadsheet B. I've annonymized the client names as they are not needed, just client codes (column B).


Thanks for your help,

Rich

[JBeaucaire]

Filtering is a great technique, no reason not to use that in the macro.

1) I changed the sheet tab names to match the value desired on each sheet.
2) I put a formula in C3 on each sheet to display the tab name so you only need to change the tab name to get C3 to update, too.
3) This macro goes in the spreadsheet B in a standard code module. It will open the Database sheet if necessary and then filter by the names of the tabs in spreadsheet B.
4) Be sure to edit the code to the full path where the database sheet is stored.

Option Explicit

Sub GetData()
Dim wsD As Worksheet    'destination sheet(s)
Dim wbS As Workbook     'source workbook
Dim wsS As Worksheet    'source sheet
Dim WasOpen As Boolean
Dim LR As Long

On Error Resume Next
Set wbS = Workbooks("Spreadsheet A.xls")

If wbS Is Nothing Then
    Set wbS = Workbooks.Open("Spreadsheet A.xls")  'may need full path here
Else
    WasOpen = False
End If

Set wsS = wbS.Sheets("Database")
wsS.AutoFilterMode = False
wsS.Rows(6).AutoFilter

For Each wsD In ThisWorkbook.Sheets
    wsS.Rows(6).AutoFilter Field:=2, Criteria1:=wsD.Name
    LR = wsS.Range("B" & Rows.Count).End(xlUp).Row
    If LR > 6 Then wsS.Range("E7:E" & LR).Copy wsD.Range("A74")
Next wsD

wsS.AutoFilterMode = False

If Not WasOpen Then wbS.Close False     'close database if it wasn't open

End Sub

[VBA Rookie]

Jerry, thanks! That works great.

Is there any way do the same thing without assigning the client code in C3 to the tab name? The reason I ask is that currently the tabs are named after the client name for easy identification (I annonimyzed it to Tab 1, 2, 3 etc), and then summarised in another tab using the indirect address function. Alternatively, is there a way of having the tab name made up of the client code first and then client name - eg "V176 - Nike", so that the macro can use the first four characters in the matching proccess?

Thanks again for your help.

Rich

[JBeaucaire]

Using the value in C3:

wsS.Rows(6).AutoFilter Field:=2, Criteria1:=wsD.Range("C3").Text

[VBA Rookie]

Again, thanks very much Jerry that works a treat.

I've added the code below just before the end sub line to format the cells that have been copied across, but it seems to stop after the first sheet. Can you see what I'm doing wrong?

For Each wsD In ThisWorkbook.Sheets
    
    Range("A74:A113").Select
    Selection.Font.Name = "Arial"
    Selection.Font.Size = 10
    Selection.Interior.ColorIndex = 36
    
Next wsD

Either way, thanks again for your help, you've saved me a great deal of time.

Rich

[JBeaucaire]

Stop selecting things. Always simply address your ranges directly. Inside a loop, you need to add the sheet variable reference to each of your commands to have full control and accuracy of what is getting changed:

For Each wsD In ThisWorkbook.Sheets
    
    wsD.Range("A74:A113").Interior.ColorIndex = 36
    
Next wsD

I took out the font and size commands because I bet Arial 10 is your default. The macro recorder adds a LOT of unnecessary code when you do cell formatting. Only leave in the code for the formatting you actually did, which it looks like you colored that range.

[JBeaucaire]

Lastly, I would build that coloring command into the original loop rather than making a new one:

For Each wsD In ThisWorkbook.Sheets
    wsS.Rows(6).AutoFilter Field:=2, Criteria1:=wsD.Name
    LR = wsS.Range("B" & Rows.Count).End(xlUp).Row
    If LR > 6 Then 
        wsS.Range("E7:E" & LR).Copy wsD.Range("A74")
        wsD.Range("A74:A113").Interior.ColorIndex = 36
    End If

Next wsD

[romperstomper]

Just for the record, I think you could do your data extract using queries and no code at all, and set the sheet to refresh automatically if the client name cell was changed.

[VBA Rookie]

Jerry, thanks for the tip works perfectly now.

Is there a book or course you recommend that I can use to learn VBA coding properly? Its definitely something I need to learn.

You've been very generous with you time, thanks once again for your help.

Rich

[JBeaucaire]

I have no recommended reading, though I'm sure you'd get 100s of sufficient options googling the same query.

If that takes care of your need, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.