PDA

View Full Version : Excel templates and save as workbook



bittu2016
02-09-2017, 01:21 AM
Hi all,

I am trying to populate a new workbook with the data in a list box using standard template. I have a data in the listbox with employer details and I want to populate these details in a standard template , once the template is filled with the employer details , the template should be saved with individual employer details
Ex : If I have 5 employers .. I want to generate a workbook with 5 different tabs ( shown in output file )

Here attached the files for the reference.

Could anybody can share me the solution .

Thanks
Bittu

bittu2016
02-09-2017, 09:45 AM
Any one plz ..:help

Paul_Hossler
02-12-2017, 07:41 PM
Something like this maybe





Option Explicit

Sub Macro1()
Dim wb1 As Workbook, wb2 As Workbook
Dim wsEmployees As Worksheet, wsTemplate1 As Worksheet, wsTemplate2 As Worksheet
Dim rEmployees As Range
Dim iEmployee As Long, iDatum As Long


Set wb1 = ThisWorkbook
Set wsEmployees = wb1.Worksheets("Data")
Set wsTemplate1 = wb1.Worksheets("template")
Set rEmployees = wsEmployees.Cells(1, 1).CurrentRegion

Application.ScreenUpdating = False

For iEmployee = 2 To rEmployees.Rows.Count
If wb2 Is Nothing Then
wsTemplate1.Copy
Set wb2 = ActiveWorkbook
Set wsTemplate2 = ActiveSheet
Else
wsTemplate1.Copy After:=wb2.Worksheets(wb2.Worksheets.Count)
Set wsTemplate2 = ActiveSheet
End If

For iDatum = 1 To rEmployees.Columns.Count
wsTemplate2.Cells(2 + iDatum, 3).Value = rEmployees.Cells(iEmployee, iDatum).Value
Next iDatum

wsTemplate2.Name = rEmployees.Cells(iEmployee, 3)

Next iEmployee

Application.ScreenUpdating = True
End Sub

bittu2016
02-12-2017, 09:35 PM
Awesome Paul , It is working

Thanks again
Bittu

bittu2016
02-12-2017, 09:50 PM
Hi Paul ,
Is that possible if the template is located in other file ( only template) and I want to generate the output results in the data form ?

Thanks