VBA Help

[Sonny]

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.

[Ken Puls]

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.

[Roger Govier]

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.

[Ken Puls]

Hi Roger,

I've changed the limit. Can you give it a go again?

[Roger Govier]

Hi

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

[Sonny]

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

[Roger Govier]

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.

[Roger Govier]

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.

[Ken Puls]

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)

[eferrero]

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.