Page 1 of 2 1 2 LastLast
Results 1 to 10 of 12

Thread: VBA Help

  1. #1

    VBA Help



    Register for a FREE account, and/
    or Log in to avoid these ads!

    I am new to VBA therefore needs help.

    I have three worksheet say CompanyA, Supplier1 & Supplier2.

    In CompanyA it has headers:
    Date Company Payment Amount Invoice No. Comments

    I update these details when I make payments to Supplier1 & 2, then I have to go and update the two Suppliers' worksheet.

    Can anyone help me in VBA so that I just use a VBA comment or button to update these data automatically without having to copy and pasting myself.

    Please feel free if anyone knows an easier than what I'm thinking.

  2. #2
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,091
    Articles
    79
    Blog Entries
    14
    Hi Sonny, and welcome to the Excelguru forums!

    We could certainly do this using VBA, but I wonder if there might be an easier way.

    Have you thought about setting up a table of data in your main worksheet (as you've described above), then just use PivotTables on other worksheets to summarize it? Instead of having to worry about maintaining the code, you'd then just need to go to the other worksheet, right click and refresh the PivotTable to get your info. (Actually, we could write VBA to keep the tables updated too.)

    I could upload an example, but I'd need to know what version of Excel you're using.
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Master your data with Power Query: Purchase your copy of my book M is for Data Monkey today!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  3. #3
    Conjurer Roger Govier's Avatar
    Join Date
    Mar 2011
    Location
    Located near Abergavenny, South Wales, UK
    Posts
    113
    Articles
    0
    Hi Sonny
    As ken has suggested, a Pivot Table is probably the best way to go, with no programming or maintenance involved.
    I have produced a small sample file for you in XL2007 format, which I am having difficulty in uploading at the moment - probably teething problems with the site, but I will get it up as soon as I can.
    In the interim, I have loaded it at my site

    http://technology4u.co.uk/Xlguru/

    If you need XL2003 format, just post back and I will try to upload .xls file as well.
    Regards

    Roger Govier
    Microsoft Excel MVP
    http://www.technology4u.co.uk

  4. #4
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,091
    Articles
    79
    Blog Entries
    14
    Hi Roger,

    I've changed the limit. Can you give it a go again?
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Master your data with Power Query: Purchase your copy of my book M is for Data Monkey today!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  5. #5
    Conjurer Roger Govier's Avatar
    Join Date
    Mar 2011
    Location
    Located near Abergavenny, South Wales, UK
    Posts
    113
    Articles
    0

    File attached this time

    Hi

    It looks as though it's going to let me upload a file for Sonny this time
    Attached Files Attached Files
    Regards

    Roger Govier
    Microsoft Excel MVP
    http://www.technology4u.co.uk

  6. #6
    Hi all

    This example is perfect but the problem is that CompanyA is used by myself whilst Supplier1 and Supplier2 are access by different people whom I don't want them to see the details for other supplier.

    What I'd prefer is to insert a button with VBA in CompanyA that will automatically transfer data I've inputed in CompanyA to either Supplier1 or Supplier2 without me having to copy and paste.

    I hope this helps further.

    Thank you, hope to hear from you all soon.

    Sonny

  7. #7
    Conjurer Roger Govier's Avatar
    Join Date
    Mar 2011
    Location
    Located near Abergavenny, South Wales, UK
    Posts
    113
    Articles
    0
    Hi Sonny

    That's fine.
    Just click on any cell within the PT> select Options Tab > far left clcik Options>from the dropdown select Show Report Filter Pages
    A new page will be generated with all the transactions related to each individual Supplier.
    Right click on the sheet tab for any Supplier>Move or Copy>Select Copy>Destination >New Workbook.

    You ow have a file that you can give / send to any Supplier, and it contiais just the transactions relating to them.

    Still no VBA to write.
    Regards

    Roger Govier
    Microsoft Excel MVP
    http://www.technology4u.co.uk

  8. #8
    Conjurer Roger Govier's Avatar
    Join Date
    Mar 2011
    Location
    Located near Abergavenny, South Wales, UK
    Posts
    113
    Articles
    0
    Alternatively, if you just want the transactions listed on the page, without the PT format, Select the first Supplier from the dropdown and double click the Grand Total
    A new page will appear with just hise transactions listed.
    Change Supplier and repeat process.
    Regards

    Roger Govier
    Microsoft Excel MVP
    http://www.technology4u.co.uk

  9. #9
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,091
    Articles
    79
    Blog Entries
    14
    Quote Originally Posted by Roger Govier View Post
    Just click on any cell within the PT> select Options Tab > far left clcik Options>from the dropdown select Show Report Filter Pages
    A new page will be generated with all the transactions related to each individual Supplier.
    Right click on the sheet tab for any Supplier>Move or Copy>Select Copy>Destination >New Workbook.

    You ow have a file that you can give / send to any Supplier, and it contiais just the transactions relating to them.
    Hi Roger,

    This isn't working for me. When I do this I get a separate pivot table filtered to each supplier, but not restricted to each supplier. I copy the table to a new workbook, and the PivotCache seems to still contain all suppliers. Am I missing something? (This is Excel 2010)
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Master your data with Power Query: Purchase your copy of my book M is for Data Monkey today!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  10. #10
    Yes Ken, that's how Pivot Tables work. The PivotCache still contains all the data.

    What I normally do is select the sheet with the filtered PivotTable that I wish to send to someone, copy-all, then paste-special-values. Then right-click the sheet tab and create a copy to a new book.

Page 1 of 2 1 2 LastLast

Posting Permissions

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