worksheet split based on the name of the cell

[preciouslife73]

I have a worksheet that has multiple information with difference client and I want to tab it out to new sheet when the cell contains a specific name on the first 10 left characters.
Then I want each of these sheets to page layout as print when it reaches the last row and last column to print out as page fit into PDF file.
And then to filename by current month and year and specific cell with another another cell that contains a company name. for instance, cell a7 is Mike and if cell g4 contains a company of my list lets say the cell shows "JP Morgan Exchange" and it is on my list as JP Morgan, then filename the pdf file as "Mike_JP Morgan~April 2016" and I want this save in the current folder where the spreadsheet reside.

[preciouslife73]

I have researched this forum. the only part I found was relating to Filename. I can see that it would be better to tab out when a specific cell in the sheet contains the first ten left characters then tab in a new sheet by naming it by a cell value in A7. then when we filename it, it will name the file easily this way with the other criteria mentioned above. I did find this VBA but when it exported as PDF in the folder, it won't allow it open. So that means i can't just type ".pdf"?

By the way, the macros only work on the worksheet that I created the Macro workbook. I want this Macro to work in any workbook that I open. I forgot how to do that? or maybe the language needs to include a statement to allow the macro to run on the workbook that I have open.

I still need to split workbook though and to print the layout the page to fit into PDF.

[preciouslife73]

Please Login or Register  to view this content.

The code above works good but it is only for file naming but it doesn't let me open the pdf when saved. I want to manipulate this code to perform exactly how I wanted in my criteria mentioned above. Maybe you can provide a brand new one. By the way, the macros only work on the worksheet that I created the Macro workbook. I want this Macro to work in any workbook that I open. I forgot how to do that? or maybe the language needs to include a statement to allow the macro to run on the workbook that I have open.

I still need to split workbook though and to print the layout the page to fit into PDF.

[Richard Buttrey]

Hi,

There's an Application File Save As option associated with saving a pdf that determines whether the file should be opened as a pdf after publishing. Set your Excel app for this to 'Open pdf'

Put the macro in your Personal Workbook so that it will work at any time.

[preciouslife73]

Hi Richard,

How do I add that into the macro? and how do I do that? I am very basic excel guy. It has been years since I played with Macros.

[preciouslife73]

Hi I am sorry but this doesn't really help me. what do you mean? I have been research other process but this is one the other half of the project. Can you explain or copy the vba with what you intended to say. please

[sanram]

Use the following code

Please Login or Register  to view this content.

Instead of

Please Login or Register  to view this content.

[preciouslife73]

Thank you, it works good but why is it opening new workbook from each tab? I love it that it was able to save each tab into PDF.
But I dont need each tab in workbook saved or open.

Please Login or Register  to view this content.

[sanram]

Thank you, it works good but why is it opening new workbook from each tab? I love it that it was able to save each tab into PDF.
But I dont need each tab in workbook saved or open.

Please Login or Register  to view this content.

Then simply change

Please Login or Register  to view this content.

To

Please Login or Register  to view this content.

[preciouslife73]

I changed it but still keeps the worksheets open after saving into PDF.

[sanram]

I changed it but still keeps the worksheets open after saving into PDF.

Do you want to close the worksheet after saving as PDF and open the pdf?

[preciouslife73]

Right I want to close the worksheet after saving as PDF but also not open the PDF.

[sanram]

Right I want to close the worksheet after saving as PDF but also not open the PDF.

Add the following line before End Sub :

Please Login or Register  to view this content.

[preciouslife73]

it is asking me if I want to save it. I dont want it to ask me to save it because I dont want to save it.

[sanram]

it is asking me if I want to save it. I dont want it to ask me to save it because I dont want to save it.

Delete this line. That's it.

Please Login or Register  to view this content.

[preciouslife73]

I am sorry, this seems to be working so great that I want declare this solved but it is still asking me if I want to save each workbooks after saving as pdf. for instance, my workbook has 10 tabs (sheets) and when each of these have already been saved as pdf, each of these converted sheets as workbooks remained open prompting me if I want to save each of them. I have to close ten workbooks by saying no each time.

[sanram]

I have tested your code and make the correction. Please see the red colored :

Please Login or Register  to view this content.

[preciouslife73]

It only closes one workbook not the rest.

[sanram]

But I have tested this code on my system and it's working fine. Can you upload a sample file?

[preciouslife73]

I see what you mean. It works good. Thank you! I just need to figure out to use this macro to work for whatever workbooks I open to perform this because this macro is only doing it for this workbook.

[sanram]

This code should work on any workbook. There remain only one thing in that code that can be changed which is "ws.Range("A1").Value", if you want to change this.

[preciouslife73]

Great thanks!