You are here:

- Home
- Computing/Technology
- Business Software
- Excel
- Assistance Required - Macro to Create Individual Workbooks

Advertisement

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

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

- Add to this Answer
- Ask a Question

Rating(1-10) | Knowledgeability = 10 | Clarity of Response = 10 | Politeness = 10 |

Comment | Exactly what I'm looking for. Tom you are simply GREAT.Thanks a TONNE. Thank you for taking time and answering my question. Line added is ActiveSheet.Range("A1").Value = rC1.Offset(0, 3).Value It was giving me error - Variable not defined - and rC1 was highlighted I went through the entire code and and made a change to the line which is ActiveSheet.Range("A1").Value = rCl.Offset(0, 3).Value instead of rC1 (Digit 1) it will be rCl (letter l) Just have 1 question (suggestion) rather than uploading snapshots cant we upload excel sample sheet |

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

Answers by Expert:

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

Extensive experience. **Education/Credentials**

Master of Science (MS) degree Operations Research (ORSA)**Awards and Honors**

Microsoft MVP in Excel.