You are here:

Excel/One Excel data source to populate a excel form to create multiple. I need one form for each location

Advertisement


Question
Hi Tom,

Let me see if I can explain it better.  

I have one excel data sheet (Dept Sheet) that has all of the info that I need to populate into the excel sheet form (Form) that I created.  Each location has a unique id number (department #) that identifies it.  Each location needs its own form with the information of the people at that location.

I need to populate the form's with the info that I need from the data sheet so that it creates a form with the unique info for each location.

I found a VB code that I attempted to change that I think will work but not being too familiar with VB I get an error where the SaveWorkbooks is and I can't figure out what to do.  

The data sheet looks like this and it is named Dept Sheet: ( there are 2000 data rows)
Dept   Department Name   Name   Emp##   Hire Date   Job Description   2013 Hrly Rate
001100   Frederick Park   Baker,Adrian A   108772   08/21/2013   Assistant Service Supervisor   20.00
001100   Frederick Park   Perez,Raymundo   104138   11/06/2007   Assistant Service Supervisor   20.00

The form looks like this and is named Form:

2013 PERFORMANCE EVALUATION SUMMARY          
         
  REGIONAL MANAGER:          V. P.  APPROVAL:          
         
  BUSINESS MANAGER:          DATE APPROVED:          
         
         
Dept ##   Department Name          Hire Date       2013 Hrly Rate   Proposed   % of   2014   Proposed
       Name   Emp ##       Job Description       Rate   Increase   Budget   vs Budget
900921   Villiage Oaks   Sample Suzy       02/04/37   Business Manager   20.22   22.00   -1.78   21.50   -0.50
         
Under this header is where I need the info from the data sheet to populate by the department number and have the change in the department number tell excel it needs to create a new form. I only need to pull  the first 7 columns of info from the Dept Sheet into the Form.  No location would have a need for over 40 rows for the form.  

The other columns in the Form have formulas in it to calculate the difference for when the manager enters in the proposed rate so it will show the difference to the approved budgeted number.


Here is the VB script that I found and copied into the VB editor on the Form sheet, I attempted to change.  It errors out at the SaveWorkbook part and I can't figure out what I'm missing: ( the original is below.  When I copy from VB editor I can't get the color to stay in the code)

Sub SplitData()
  Dim newSheet As Worksheet, deptSheet As Worksheet
  Dim cell As Object
  Dim deptRange As String
  Set deptSheet = Sheets("DEPT SHEET")
  ' Turn off screen updating to increase performance.
  Application.ScreenUpdating = False
  ' Build a string that specifies the cells in column B that
  ' contain region names starting from cell B4.
  deptRange = "A2:" & deptSheet.Range("A2").End(xlDown).Address
  For Each cell In deptSheet.Range(deptRange)
     If Dept.SheetExists(cell.Value) = False Then
        ' Add a new worksheet.
        Sheets.Add After:=Sheets(Sheets.Count)
        ' Set newSheet variable to the new worksheet.
        Set newSheet = ActiveSheet
        ' Copy boilerplate data from first three rows
        ' of the master worksheet to the range starting at
        ' A1 in the new sheet.
        deptSheet.Range("A1:A10").EntireRow.Copy newSheet.Range("A1")
        ' Copy and paste the column widths to the new sheet.
        deptSheet.Range("A1:A10").EntireRow.Copy
        newSheet.Range("A1").PasteSpecial xlPasteColumnWidths
        ' Copy the entire row for the current region and
        ' paste starting at cell A4 in the new sheet.
        cell.EntireRow.Copy newSheet.Range("A12")
        ' Name the new sheet.
        newSheet.Name = cell.Value
        ' Call the SaveWorkbook function to save the current
        ' worksheet as a new workbook file.
        SaveWorkbook (cell.Value)
        ' Turn off alerts, and then delete the new worksheet
        ' from the current workbook.
        Application.DisplayAlerts = False
        newSheet.Delete
        ' Turn alerts back on.
        Application.DisplayAlerts = True
     End If
  Next cell
  ' Notify the user that the process is complete.
  MsgBox "All workbooks have been created successfully"

*******************This is the original Sample:********************************

Sub CreateWorkbooks()
  Dim newSheet As Worksheet, regionSheet As Worksheet
  Dim cell As Object
  Dim regionRange As String

  Set regionSheet = Sheets("REGION SHEET")

  ' Turn off screen updating to increase performance.
  Application.ScreenUpdating = False

  ' Build a string that specifies the cells in column B that
  ' contain region names starting from cell B4.
  regionRange = "B4:" & regionSheet.Range("B4").End(xlDown).Address

  For Each cell In regionSheet.Range(regionRange)
     If SheetExists(cell.Value) = False Then
        ' Add a new worksheet.
        Sheets.Add After:=Sheets(Sheets.Count)
        ' Set newSheet variable to the new worksheet.
        Set newSheet = ActiveSheet
        ' Copy boilerplate data from first three rows
        ' of the master worksheet to the range starting at
        ' A1 in the new sheet.
        regionSheet.Range("A1:A3").EntireRow.Copy newSheet.Range("A1")
        ' Copy and paste the column widths to the new sheet.
        regionSheet.Range("A1:A3").EntireRow.Copy
        newSheet.Range("A1").PasteSpecial xlPasteColumnWidths
        ' Copy the entire row for the current region and
        ' paste starting at cell A4 in the new sheet.
        cell.EntireRow.Copy newSheet.Range("A4")
        ' Name the new sheet.
        newSheet.Name = cell.Value
        ' Call the SaveWorkbook function to save the current
        ' worksheet as a new workbook file.
        SaveWorkbook (cell.Value)
        ' Turn off alerts, and then delete the new worksheet
        ' from the current workbook.
        Application.DisplayAlerts = False
        newSheet.Delete
        ' Turn alerts back on.
        Application.DisplayAlerts = True
     End If
  Next Cell

  ' Notify the user that the process is complete.
  MsgBox "All workbooks have been created successfully"

  ' Turn screen updating back on.
  Application.ScreenUpdating = True

End Sub


I appreciate you taking a look at this.  Hopefully it better explains what I am trying to do. I know I could do this as a mail merge in Word but then I lose the formulas which is why I'm trying to keep it in Excel.

Thank you,
Angela

Answer
Angela,

If you are having trouble with the SaveAs and everything else is working perfectly, then I suspect the whatever is in cell.value is not a valid worksheet name or it is a duplicate name.

Also, looking at your code, it appears that you create a workbook for each row in your data.  I don't see where you copy all the data with the same dept number and then save the workbook.


If you want me to look at it, you can send the workbook to me at twogilvy@msn.com

--
Regards,
Tom Ogilvy

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.