Macro to create tab/file and email

[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.

[HaHoBe]

Hi, Amarjeet Singh,

mabe you could wrap up your procedures with code-tags as requested by Forum Rule #3?

From what I understand you could combine the first two procedures into something like this

Please Login or Register  to view this content.

As you have been very quiet about the mail program to use and which cell to hold the address maybe you have a look at Mail every worksheet with address in A1 which might be integrated into the code of the procedure as well.

Ciao,
Holger

[arlu1201]

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

[Amarjeet Singh]

Hi Holger,

Thanks for your help, It's giving error at [ ActiveSheet.Move
ActiveWorkbook.SaveAs ActiveSheet.Name
ActiveWorkbook.Close]
Saying File name already exist in the location?

Thanks

[Amarjeet Singh]

Fixed it . Many thanks for all you help.

[arlu1201]

Amarjeet,

Please check post 3.