Create Worksheet/Workbook and email through Macro

[Amarjeet Singh]

Hi

I need help on a Macro.

I have an Excel file wherein I have three tabs "List", "Template", "PO Report". Template is linked to PO Report and in the List I have number of Cost Centers in Column A. What i need is to create a copy of Template as per the list ( which will be fluctuating) in the LIST tab. Rename the tabs created as per List ( Cost Centers).-----(1)

Secondly i want to create separate file for each of the tab created with tab name.----(2)

Thirdly a Macro to email each work sheet created at (1) or file created at (2) as per the list to separate Cost Center owners.


For First i am using the below code, the problem with this is everyday list of cost center increases/decreases i have change the number of cost centers in the code for which tabs to be created . It should automaticaly pick the Cost Center from the list until blank cell is found.
Option Explicit

Sub Copy_Sheets()

Dim i As Integer
Dim wks As Worksheet

Set wks = Sheets("List")

For i = 1 To 6
Sheets("Template").Copy After:=Sheets(2)
ActiveSheet.Name = wks.Cells(i, 1)
ActiveSheet.Cells(1, 2) = wks.Cells(i, 1)
Next

End Sub


For (2) I am using the below code, the problem with this is it creates a file of all the tabs in the file i.e. It creates a separate file for "LIST", "Template" and "PO Report" as well. It should only create file of cost centers as per the List only.

Sub SaveEachWS()
Dim ws As Worksheet

For Each ws In ThisWorkbook.Worksheets
ws.Copy
ActiveWorkbook.SaveAs Filename:=ws.Name
ActiveWorkbook.Close
Next ws

End Sub

For (3)

I could not create code, It should email the tabs / files created from the email address in a partcular cell it may be A1 or D2 anything.


Thanks in advance for all you help on this.
Amar

[HaHoBe]

Hi, Amar ,

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

----

Please do not start new threads regarding the same subject (Rule #5) - http://www.excelforum.com/excel-prog...and-email.html.

Ciao,
Holger

[arlu1201]

This is a duplicate post and as such does not comply with Rule 5 of our forum rules. This thread will now be closed, you may continue in your other thread.

Thread Closed.