You are here:

Excel/Assistance Required - Macro to Create Individual Workbooks

Advertisement


Question
Data and Template
Data and Template  
We receive a List with 100+ Names of Agencies every month. Also, this list keeps changing. It becomes time consuming to create Individual Workbooks every month.

Names of Agencies are on Sheet1 Column A, Amount in Column B, Heading in Column D and Template on Sheet2.

ColumnA  ColumnB   ColumnD
Agency   Amount    Heading
Aaaaa    $1,000    Aaaaa Statement for 2015
Ddddd    25,400   Bbbbb Statement for 2015
Ggggg    $15,002   Ggggg Statement for 2015

Macro should do the following :

1) Create Individual Workbook as per Template on Sheet2 and save the Workbook as per data in Sheet1 Column A.

2) Sheet Tab on all Individual Workbooks should also be re-named as per data in Sheet1 Column A.

3) Should update Cell B3 of the Individual Workbook now created with their respective amount. Explanation as below :

For Aaaaa Workbook, Cell B3 should reflect $1,000
For Ddddd Workbook, Cell B3 should reflect 25,400
Above task should be performed for the remaining Agencies

4) Column D contains formula, should update Cell A1 of the Individual Workbook now created with their respective heading without the formula. Explanation as below :

For Aaaaa Workbook, Cell A1 should reflect Aaaaa Statement for 2015
For Bbbbb Workbook, Cell A1 should reflect Bbbbb Statement for 2015
Above task should be performed for the remaining Agencies

5) All Individual Workbooks will be saved in the folder where the macro workbook will be kept.

Important Note :

Individual Workbook should not be connected to the sourcessheet.In other words Individual Workbook should not reflect any formulas other than the formulas being used in the template.
----------
Below is a macro that I came across which does everything as stated above.Except for 1 small detail, it doesn't perform what is covered in point (4),Can you tweak the code to do what is mentioned in point (4)

Option Explicit

Sub CreateBooks()
    
   Dim rCl As Range, rRng As Range
    
   Set rRng = Sheet1.Range("A1").CurrentRegion.Offset(1)
   Application.ScreenUpdating = False
   For Each rCl In rRng.Columns(1).Cells
       If Not IsEmpty(rCl) Then
         Sheet2.Range("B1").Value = rCl.Value
         Sheet2.Copy
         ActiveSheet.Name = rCl.Value
         ActiveSheet.Range("B3").Value = ActiveSheet.Range("B3").Value
         ActiveWorkbook.SaveAs ThisWorkbook.Path & "\" & rCl.Value, FileFormat:= _
         51, CreateBackup:=False
         ActiveWorkbook.Close True
       End If
   Next rCl
   Application.ScreenUpdating = True
End Sub

Answer
Bimmy,

here is the altered code

rC1.offset(0,3)   is column D in the same row as rC1


Sub CreateBooks()

Dim rCl As Range, rRng As Range

Set rRng = Sheet1.Range("A1").CurrentRegion.Offset(1)
Application.ScreenUpdating = False
For Each rCl In rRng.Columns(1).Cells
If Not IsEmpty(rCl) Then
Sheet2.Range("B1").Value = rCl.Value
Sheet2.Copy
ActiveSheet.Name = rCl.Value
ActiveSheet.Range("B3").Value = ActiveSheet.Range("B3").Value
ActiveSheet.Range("A1").Value = rC1.Offset(0, 3).Value  '<== line added
ActiveWorkbook.SaveAs ThisWorkbook.Path & "\" & rCl.Value, FileFormat:= _
51, CreateBackup:=False
ActiveWorkbook.Close True
End If
Next rCl
Application.ScreenUpdating = True
End Sub
About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here

Excel

All Answers


Answers by Expert:


Ask Experts

Volunteer


Tom Ogilvy

Expertise

Selected as an Excel MVP by Microsoft since 1999. Answering Excel questions in Allexperts since its inception in 2001. Able to answer questions on almost all aspects of Excel's internal capabilities. If seeking a VBA solution, please specify that in your question itself so I give you the answer you want. [Excel has weak protection - if you are distributing an application, I don't answer questions on how to protect your project from your users.]

Experience

Extensive experience.

Education/Credentials
Master of Science (MS) degree Operations Research (ORSA)

Awards and Honors
Microsoft MVP in Excel.

©2016 About.com. All rights reserved.