Results 1 to 10 of 10
  1. #1
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts

    Macro to open workbook and copy data where name matches

    I need a macro to do the following

    1) To give option select a workbook workbook in directory c:\my documents
    2) To compare the name on the source workbook in Cell B1 to the name in Col B ofn the destination workbook and if they match to copy
    a) the value in Col B in line with V.A.T. On Sales to Col H on active sheet of the destination workbook
    b) the value in Col B in line with V.A.T. On Purchases to Col G on active sheet of the destination workbook
    c) the value in Col B in line with Sales To Internal to Col M on active sheet of the destination workbook


    Your assistance in this regard is most appreciated

  2. #2
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts
    Hi Maudibe

    Thanks for the reply. It is different to my earlier post. It would be appreciated if you could assist me

    Regards

    Howard

  3. #3
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts
    Hi Maudibe

    See attached sample files

    Your assistance in writing the code will be most appreciated
    Attached Files Attached Files

  4. #4
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,823
    Thanks
    135
    Thanked 482 Times in 459 Posts
    Hi Howard

    My attached file should do what you want.

    1. Copy the attached files to a test folder of your choice
    2. Open the file [rz1a-Vat.2012.2013.Sample.xls]

    On sheet [Mar-2013] I have a a button labelled [Fetch VAT data].
    Click it to select one of the sample files attached.

    This uses a combination of VBA processing and a formulas sheet named [parameters].

    let me know what you think.

    zeddy
    Attached Files Attached Files

  5. #5
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,823
    Thanks
    135
    Thanked 482 Times in 459 Posts
    HI Howard

    The file [rz1a-Vat.2012.2013.Sample.xls] is intended for use in Excel2003.

    The attached file [rz1b-Vat.2012.2013.Sample.xlsm] should be used if you are using Excel2007, Excel2010 or Excel2013

    zeddy
    Attached Files Attached Files

  6. The Following User Says Thank You to zeddy For This Useful Post:

    HowardC (2013-04-21)

  7. #6
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts
    Hi Zeddy

    Thanks for all the effort in developing the code to suit my needs. I really appreciate the help that you have given me

    Regards

    Howard

  8. #7
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts
    Hi Zeddy

    The destination file has multiple sheets, for each month of the year

    I created an additional sheet, "Feb-2013" and when selecting the source workbook sheet rz1-br4-source vat report.sample #name? appears in H8.

    It would be appreciated if you would amend your code to accomodate multiple sheets and well as this error

    Regards

    Howard
    Attached Files Attached Files

  9. #8
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts
    Hi Zeddy

    I have resolved the problem. The range name amount2 in the sheet parameters was missing. I may have deleted this in error. I have created the ranhe name and re-tested your macro and it is working.

    Once again thanks very much. You are a star

    Regards

    Howard

  10. #9
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,823
    Thanks
    135
    Thanked 482 Times in 459 Posts
    Hi Howard

    H8 uses the figure for VAT On Sales which comes from named range amount1 (rather than amount2).
    But I guess that was just a typo in your post#8, as you fixed it.
    Top marks for tracking down the source of the problem and fixing it yourself.
    Now that is what I call a great result.

    I figured you would want other named sheets for the months, so coded the VBA to work with other months too.
    So now you can copy the [Fetch VAT data] button (right-click-copy) and paste it onto as many other sheets as required.

    zeddy

  11. The Following User Says Thank You to zeddy For This Useful Post:

    HowardC (2013-04-21)

  12. #10
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts
    Hi Zeddy

    Thanks for the reply. Will copy the "Fetch Vat data" button on all the required sheets

    Howard

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •