Link two dynamic workbooks with multiple tabs

Hi

1) I've seen an addin (morefunc perhaps???) that will do a vlookup across mulitple sheets. Depending on whether your organisation will allow 3rd party addins, you may be able to find / use that function.

2) Another option would be to build an udpate macro that will run on demand. You would control when the udpdate is done.

Hope that gives you some ideas.

rylo

Thank you Rylo,

Option 2 looks to be more along the lines of what I think would work.
could you please go into more detail as to how you were thinking the update macro would work?

Hi

Create a couple of sample workbooks that reflect your situation, and add some data. Then come up with a couple of step by step scenarios, and show what the output should look like at the end of the updates. Much easier if you build the example workbooks as it removes any confusion on interpretation.


rylo

Please see attached files, I've added comments in each of them, I hope it's clear as to what I want to accomplish.

I really appreciate your help.

Hi

Here's a start. Put the code below into a general module in Vendor.xls

There are some assumptions
1) There will be a sheet in Vendor.xls for every name in Check. So you will have to add sheets for Jim and Frank.....
2) There must be an invoice in Vendor to match the vendor / invoice combinaion - so the invoice for Vendor 1 in Oct 08 will have to be changed to 7773
3) the Check list sheets have to be the same structure - so the August 08 sheet will have to be delete 3 rows so that the word payee is in A6 not A9.
4) Both workbooks will be open at the same time.

See how that goes...

rylo

Sub DoUpdate()
  Dim DataWB As Workbook
  Dim OutSH As Worksheet
  Set DataWB = Workbooks("Check List.xls")
  
  DataWB.Activate
  For i = 1 To Sheets.Count
    Sheets(i).Select
    For j = 8 To Cells(Rows.Count, 1).End(xlUp).Row
      If Not IsEmpty(Cells(j, "B")) Then 'there is an invoice number
        Set OutSH = ThisWorkbook.Sheets(Cells(j, "A").Value)
        outrow = WorksheetFunction.Match(Cells(j, "B").Value, OutSH.Range("C:C"), 0)
        OutSH.Cells(outrow, "H").Value = Cells(j, "C").Value
        OutSH.Cells(outrow, "I").Value = Cells(j, "G").Value
        OutSH.Cells(outrow, "J").Value = Cells(j, "H").Value
      End If
    
    Next j
      
    
  Next i
  
  
End Sub

Hi!

Assumptions:

1) There will be a sheet in Vendor.xls for every name in Check. So you will have to add sheets for Jim and Frank..... the vendor list does not have every name in the check list. Vendor list only tracks our normal reocurring vendors and the check list contains all checks issued including a lot of one time payments Misc. stuff.
2) There must be an invoice in Vendor to match the vendor / invoice combinaion - so the invoice for Vendor 1 in Oct 08 will have to be changed to 7773. You are correct

3) the Check list sheets have to be the same structure - so the August 08 sheet will have to be delete 3 rows so that the word payee is in A6 not A9. The number of checks vary from month to month. and we try to keep them in alphabetical order within each of the 4 categories. This means that sometimes we need to insert a rows. i.e. if we have received invoices for payee AAA and CCC and later we receive an invoice from BBB we would need to insert a rwo between AAA and CCC to enter the information for BBB.

4) Both workbooks will be open at the same time. Is there a way to get around having both workbooks open?

Thank you for your promp responses.!!!

Hi

1) So there will be instances that there are cheques raised, but not a vendor sheet. Does this mean that if a sheet doesn't exist, you ignore the item, or will there be a default sheet that will contain all the other items?

3) I was referring to the starting row. It doesn't matter about the order of the data, but the starting row has to be row 6 not row 9. Is this going to be a problem?

4) Much easier if they are open. As this is going to be a manually controlled update, is there a problem with having it open? Is this data workbook likely to keep changing? Do you want the ability to nominate the data workbook, and have it open as part of the process?

rylo

1) So there will be instances that there are cheques raised, but not a vendor sheet. Does this mean that if a sheet doesn't exist, you ignore the item, or will there be a default sheet that will contain all the other items? You are correct. if there is no sheet for a particular payee (vendor) item will be ignored.

3) I was referring to the starting row. It doesn't matter about the order of the data, but the starting row has to be row 6 not row 9. Is this going to be a problem? This is not a problem.

4) Much easier if they are open. As this is going to be a manually controlled update, is there a problem with having it open? Is this data workbook likely to keep changing? Do you want the ability to nominate the data workbook, and have it open as part of the process? Opening both workbooks to run the update shouldn't be a big problem, so long once the update is ran and the information on the vendor file remains. Management will from time to time open the vendor file to look at what has been paid and what has not. It would be a hassle for them to have to open two files just to take a quick look at the vendor list's accruals.

Thank you.

Hi

1) Have a look at the attached. I've updated both sheets to what I believe would be a starting position. Run the macro attached to Vendor.xls and review the output.

4) Data is pasted into Vendor.xls so it can be opened on its own and the data will show.

rylo

WOW!! That works great!!! THANK YOU so much.


I haven't played around with it much but it looks like this should work. Would it be possible for you to tell me how the code works?. It works great, but I would like to understand it. I would like to know enough so I can tweak it in case I something changes.

Also is there a site where I can start learning the syntax used? Beginner Please

Rylo you are the best.

can't thank you enough!

Hi

Here's the code with some comments.

Sub DoUpdate()
'dimension workbook/worksheet variables
  Dim DataWB As Workbook
  Dim OutSH As Worksheet
'set the data workbook to the variable
  Set DataWB = Workbooks("Check List.xls")
'select the data workbook
  DataWB.Activate
'cycle through all the sheets
  For i = 1 To Sheets.Count
'select the next sheet
    Sheets(i).Select
'start at column & and work through to the alst column
    For j = 8 To Cells(Rows.Count, 1).End(xlUp).Row
      If Not IsEmpty(Cells(j, "B")) Then 'there is an invoice number
' if there is an existing sheet, then do the set, otherwise step over and avoid the error
        On Error Resume Next
        Set OutSH = Nothing
        Set OutSH = ThisWorkbook.Sheets(Cells(j, "A").Value)
        On Error GoTo 0
'an output sheet has been found
        If Not OutSH Is Nothing Then
'determine the next output row
          outrow = WorksheetFunction.Match(Cells(j, "B").Value, OutSH.Range("C:C"), 0)
'output the data using the cells approach
          OutSH.Cells(outrow, "H").Value = Cells(j, "C").Value
          OutSH.Cells(outrow, "I").Value = Cells(j, "G").Value
          OutSH.Cells(outrow, "J").Value = Cells(j, "H").Value
        End If
      End If
    Next j
  Next i
End Sub

If something still doesn't make sense, don't hesitate to ask.

rylo