Dear My Online,
I got following problem, is there a way to do it be a VB loop, now doing manually by cut and paste
We have Master Record where every Columns fields may or may not have following records:
- Vendor Code:
- Vendor Name:
- Vendor Address:
- Vendor Phone /Fax #:
- Vendor Email Address:
- Product Item Number:
- Price (Unit)
- Salesperson PIC Email Address
Each Vendor has multiple items so many of the lines may have the first three columns duplicated until the list gets to the next Vendor.
I would like help creating a VBA script which will create separate worksheet each base on Vendor Name only the items and asking quote from that Vendor.
i.e. we would like to have separate worksheet or separate PDF price quote from each Vendor from the master record.
Thanks!
B.Rgds
David
Hi David
Post some sample data. It will be easier for other members to help.
Sunny
I got following problem, is there a way to do it be a VB loop, now doing manually by cut and paste
We have Master Record where every Columns fields may or may not have following records:
- Vendor Code:
- Vendor Name:
- Vendor Address:
- Vendor Phone /Fax #:
- Vendor Email Address:
- Product Item Number:
- Price (Unit)
- Salesperson PIC Email Address
Each Vendor has multiple items so many of the lines may have the first three columns duplicated until the list gets to the next Vendor.
I would like help creating a VBA script which will create separate worksheet each base on Vendor Name only the items and asking quote from that Vendor.
i.e. we would like to have separate worksheet or separate PDF price quote from each Vendor from the master record.
Thanks!
B.Rgds
David
Hi David
See of this is sufficient for you. It involves creating a Pivot Table.
1 Create a PivotT able
2 Drop the Vendor Name 1 into the Report Filter area
3 Drop the Price into the Values area (There is no Asking Quote field in your data so I dropped the Price instead)
4 Select the PivotTable
5 Under the PivotTable Tools - Options tab, select Options -Show Report Filter Pages….
6 Click OK
7 A worksheet will be created for each vendor with the required fields.
Hope this helps.
Sunny
Hi David
I noticed a lot of inconsistency in the Vendor's name (some with full stop and some have none etc) and this will cause problems when you split the data.
Example
HITACHI KASEI SHOJI CO., LTD
HITACHI KASEI SHOJI CO., LTD.
EAST CHINA INDUSTRY CO., LTD
EAST CHINA INDUSTRY CO., LTD.
Excel will consider them to be different Vendors.
You will need to clean them up first before splitting them.
Sunny
Dear Sunny,
But I still gloomy as we need the Data split[ from master record], after Pivot, to place exactly onto the Template worksheet area desired., not simply split baed on hte page filed of the Pivot table
Pls help further.
For data validity we wil tidy up before piot or split , it is just for testing only
Hi David,
It may be more efficient to use the PivotTable to extract the data and then use VBA to format the PivotTable as you want, as opposed to using VBA to extract the data.
I'll let others chime in with their advice though.
Mynda
I think it is quite similar Ms Word mail merge function
ie a standard document and merger with data file [ thal variables field record..
so we do hope Excel can do the same...
Hi David
See if this is what you require.
It contains a macro (CreateWS) that will create a copy of the template for each vendor, rename it and then copy the required fields.
Change it to suit your requirements.
I have deleted the last record from your test file as it is a duplicate and resulted in an error when renaming the sheet.
As for the printing to PDF, you can find a lot of such code in the internet.
Sunny
Thanks Sunny, I test run the VB , it seems OK,
I need to moodify the loop a bit to fit the exact requirment.
Once again deep thanks for the help....
B.Rgds,
David
Thanks Sunny, the WS Macro, it works OK so far
What about if we want to query certain records base on certain criteria required from the Master Table then the selected(Query) records goes to the Template , ie how to modified the VB to fit the pupose..ie not all records, but all Master Table keep intact.
Hi David
Try this. You will need to filter the Master Record worksheet.
The macro CreateWSFitered will create the worksheets for the filtered records only.
You will need to modify it to suit your requirements.
Sunny
Thanks Sunny! Will try on this.
Rgds,
Daviid
Again thanks so much Sunny, it works perfectly OK after a light modification to the VB loop.
Rgds,
David
