VBA Help

S

Sonny

New member
Joined
Mar 20, 2011
Messages
3
Reaction score
0
Points
0
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.
 
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.
 
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.
 
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
 
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.
 
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.
 
Roger Govier said:
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.
Click to expand...

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)
 
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.
 
Hi Ken

Yes, you're right
The behaviour altered from XL2007, which I had forgotten when I posted.
That is why I made the supplementary posting to Sonny, that clicking on the Grand Total for any Supplier who has been selected using the Page Filter, will produce just the list of the transactions - which was exactly what Show Pages used to do in XL2003 and earlier.

There is no cache involved in this case, you are just looking at a table, which does contain just the transactions for that supplier and absolutely nothing else.
Moving that sheet to a new workbook, allows you to give it to the Supplier, knowing it only contains his transactions.

My bad for not remembering that when I made the first post.
 
Roger Govier said:
The behaviour altered from XL2007, which I had forgotten when I posted.
That is why I made the supplementary posting to Sonny, that clicking on the Grand Total for any Supplier who has been selected using the Page Filter, will produce just the list of the transactions - which was exactly what Show Pages used to do in XL2003 and earlier.
Click to expand...

That is bizarre. The Excel team won't fix the ShowFloaties = True (which hides the floatie toolbar) in case it breaks funcionality that relies on the borked coding job they did there. Yet they'd change the way ShowPages works? Wow... I wonder how many people didn't realize and sent out full pivots instead of what they thought was a static list...

Who knows... maybe this is the reason why they won't fix the ShowFloaties thing now? :lol:
 
You must log in or register to reply here.
Back
Top