VBA help: creating multiple workbooks from a template and list of companies

[JLuby]

Hopefully some can help me with getting some code to create a VBA macro to help me with the following:

I have a list of companies that I make income reports for each quarter. I have a template for the report and a list of these companies.

In my workbook I have the following:
1) Three data sheets, one for each month's income
2) A company list sheet that has a listing of all the companies I need reports created for.
3) A Template sheet, which I already have set up with vlookups and counts to do all the necessary changes for each company back to the data tabs.

I need a macro that will take the template sheet and copy it into a new workbook for each company from the list sheet. I also need it to save each workbook to a specific location with the file name being the company name.

I am pretty new to macros so the code for this is giving me a hard time!

Any help is much appreciated!!

 

[JLGWhiz]

If you intend to save the workbooks to the same folder each quarter, then you would probably want to add a date to the file name so you don't get an error message telling you that the file already exists. Since no workbook, sheet names or location of the list of companies was provided, you will need to modify the following code to substitute the correct names and locations.
Example:Assumes Template is in same workbook as code.

Sub t()
Dim tp As Worksheet, c As Range, rng As Range, fPath As String
fPath = ThisWorkbook.Path
    With Sheets("Company List")
        Set rng = .Range("A2", .Cells(Rows.Count, 1).End(xlUp)) 'Assumes list is in column A
    End With
    For Each c In rng
        Sheets("Template").Copy
        Set tp = ActiveWorkbook.Sheets(1)
        tp.Name = c.Value
        ActiveWorkbook.SaveAs fPath & "\" & c.Value & Format(Date, "mmm-yyyy") & ".xlsx"
        ActiveWorkbook.Close False
    Next
End Sub

 

[JLuby]

In my Template, when the new workbook is generated I need the heading is A

 

[JLuby]

In my Template, when the new workbook is generated I need the heading is A1 to be replaced with the next Company Name. This is what all my lookups in my template flow from.
Also you mentioned adding the date is in the file name and yes that is kind of what I need. It needs to list the quarter it belong too which I have listed in cell A7 of my template. Can my file name be my company name plus the quarter, (A1 + A7 data)?

 

[JLGWhiz]

Something like this:

Sub t2()
Dim tp As Worksheet, c As Range, rng As Range, fPath As String
fPath = ThisWorkbook.Path
    With Sheets("Company List")
        Set rng = .Range("A2", .Cells(Rows.Count, 1).End(xlUp)) 'Assumes list is in column A
    End With
    For Each c In rng
        Sheets("Template").Copy
        Set tp = ActiveWorkbook.Sheets(1)
        tp.Name = c.Value
        tp.Range("A1") = c.Value
        ActiveWorkbook.SaveAs fPath & "\" & c.Value & ": " & tp.Range("A7").Value & ".xlsx"
        ActiveWorkbook.Close False
    Next
End Sub