Create workbooks from a listClosed

Question

Hi

I'm totally unfamiliar with Marco's, so I hope someone can help.
I need to create 200 excel sheets from a template and save them with the name in a list.
This list is included in a workbook separate from the 'template'.

So I have 2 files (if this would be difficult, I can include the 'list' in the template file too):
1) template
2) list of 200 cost centers, from A1:A200; list of the currencies in B1:B200, and list of countries in C1:C200


The process should do:
A) Open the template
B) Take cost center in the other 'list' excel workbook [or another tab sheet in the template file if easier] in cell A1
C) Fill in this value (cost center) in 'template' - tab sheet 'budget' cell D2
D) do the same for: B1 [list] -> D3 [template] and C1 [list] -> D4 [template]
E) Save the file with name cost center name in cell D2 [template]

Redo for the remaining 199 cost centers listed in the 'list' file.

I don't know if it important information or not, but when the cost center in D2 in the template is filled in, vlookups on this D2 value in other cells of tab sheet 'budget' will lookup values in other tab sheets in the template file (prior year actuals/budgets).

If anybody could help (and save me some hours of work), thank you so much!

TrowaD · Answer

Hi 2jbb,

Point C "  tab sheet 'budget' " got me a little confused.

This is what the code below does:
-Copy template
-Rename Template to the value in A1
-Renamed Template cell D2 = A1
-Renamed Template cell D3 = B1
-Renamed Template cell D4 = C1
-Repeat for the other 199 rows

This will only work when the Template and your main sheet are in the same file.

Here is the code:
Sub RunMe()
For Each cell In Range("A1:A200")
    Sheets("Template").Copy After:=Sheets(Sheets.Count)
    With ActiveSheet
        .Name = cell.Value
        .Range("D2").Value = cell.Value
        .Range("D3").Value = cell.Offset(0, 1).Value
        .Range("D4").Value = cell.Offset(0, 2).Value
    End With
Next cell
End Sub

Run the code from your main sheet where your lists are located.

Does this get the job done for you?

Best regards,
Trowa

Create workbooks from a list

1 response

Similar discussions

Subscribe To Our Newsletter!