Create a Master sheet to then transpose and export spreadsheets

[loribear180]

Hello! I just need some guidance into what I'm supposed to actually be learning/googling.

I usually create multiple checklists from an Excel 'template' spreadsheet. However, this is too time consuming and I was hoping I could create a simple Master spreadsheet where I could enter all the data and then have Excel automatically transpose (not sure i'm using the correct term) this info and create all the necessary files using the 'template'.

For example, the template includes name, dob, address, etc. I wanted to be able to enter all of this in the Master spreadsheet and then Excel can automatically enter all of this using the template and create/save the multiple files in a specific folder. HELP, i'm not sure where to begin googling or what thread to search for.

 

[DanteAmor]

Do the following. In the same book, on the "Master" sheet put your information as in your example:

In the same book, in the sheet "Template", create your template as is your example.

Now run the following macro.
Change "C:\wok\files\" to the name of your folder.

Sub Export_Template()
  Dim wb2 As Workbook
  Dim shM As Worksheet, shT As Worksheet, sh2 As Worksheet
  Dim i As Long
  
  Application.ScreenUpdating = False
  Set shM = Sheets("Master")    'Master sheet
  Set shT = Sheets("Template")  'Template sheet
  
  For i = 2 To shM.Range("A" & Rows.Count).End(3).Row
    shT.Copy
    Set wb2 = ActiveWorkbook
    Set sh2 = wb2.Sheets(1)
    sh2.Range("E2").Value = shM.Range("A" & i).Value  'NAME
    sh2.Range("E3").Value = shM.Range("B" & i).Value  'DOB
    sh2.Range("B5").Value = shM.Range("C" & i).Value  'ADDRESS
    sh2.Range("B6").Value = shM.Range("D" & i).Value  'DEPT
    sh2.Range("B7").Value = shM.Range("E" & i).Value  'START DATE
    sh2.Range("B8").Value = shM.Range("F" & i).Value  'WORK D
    '
    wb2.SaveAs "C:\wok\files\" & shM.Range("A" & i).Value & ".xlsx"
    wb2.Close False
  Next
End Sub

 

[loribear180]

Do the following. In the same book, on the "Master" sheet put your information as in your example:

View attachment 40395

In the same book, in the sheet "Template", create your template as is your example.

Now run the following macro.
Change "C:\wok\files\" to the name of your folder.

Sub Export_Template()
  Dim wb2 As Workbook
  Dim shM As Worksheet, shT As Worksheet, sh2 As Worksheet
  Dim i As Long
 
  Application.ScreenUpdating = False
  Set shM = Sheets("Master")    'Master sheet
  Set shT = Sheets("Template")  'Template sheet
 
  For i = 2 To shM.Range("A" & Rows.Count).End(3).Row
    shT.Copy
    Set wb2 = ActiveWorkbook
    Set sh2 = wb2.Sheets(1)
    sh2.Range("E2").Value = shM.Range("A" & i).Value  'NAME
    sh2.Range("E3").Value = shM.Range("B" & i).Value  'DOB
    sh2.Range("B5").Value = shM.Range("C" & i).Value  'ADDRESS
    sh2.Range("B6").Value = shM.Range("D" & i).Value  'DEPT
    sh2.Range("B7").Value = shM.Range("E" & i).Value  'START DATE
    sh2.Range("B8").Value = shM.Range("F" & i).Value  'WORK D
    '
    wb2.SaveAs "C:\wok\files\" & shM.Range("A" & i).Value & ".xlsx"
    wb2.Close False
  Next
End Sub

Thank you @DanteAmor ! Quick question, will the code above save each row of information as an individual 'template' spreadsheet? Also, how did you create the code above? I just want to become familiar with it and google more into it.

 

[DanteAmor]

will the code above save each row of information as an individual 'template' spreadsheet?

It is right.

_____

Also, how did you create the code above?

I have been making codes for several years and on several occasions I have made codes similar to this one.

 

[loribear180]

It is right.

_____

I have been making codes for several years and on several occasions I have made codes similar to this one.

Great thank you @DanteAmor, I'm cleaning up my spreadsheet and i'll test the code, I hope I don't mess it up and everything goes smoothly

 

[loribear180]

@DanteAmor I edited the code to match my template sections, however, I'm running into some issues. It is currently only picking up row 2 from the "Data" sheet.

I think this might be my mistake since I had originally linked only row 2 to my template (before I reached out for help on how to automate). For example: from the 'Data' sheet A2 is directly linked to TextBox15, B2 is directly linked to TextBox16, C2 to TextBox17, etc. Should I remove those links so the code inserts the information from the 'Data' sheet automatically into the appropriate 'Rollover Template' sections??

Sub Export_Template()

Dim wb2 As Workbook

Dim shM As Worksheet, shT As Worksheet, sh2 As Worksheet

Dim i As Long



Application.ScreenUpdating = False

Set shM = Sheets("Data") 'Data sheet

Set shT = Sheets("Rollover Template") 'Rollover Template sheet



For i = 2 To shM.Range("A" & Rows.Count).End(3).Row

shT.Copy

Set wb2 = ActiveWorkbook

Set sh2 = wb2.Sheets(1)

sh2.Range("TextBox15").Value = shM.Range("A" & i).Value 'DC or AJ

sh2.Range("TextBox16").Value = shM.Range("B" & i).Value 'Last, First Name

sh2.Range("TextBox17").Value = shM.Range("C" & i).Value 'Annual Work Period Start

sh2.Range("TextBox18").Value = shM.Range("D" & i).Value 'Term

sh2.Range("TextBox20").Value = shM.Range("E" & i).Value '% Effort

sh2.Range("TextBox19").Value = shM.Range("F" & i).Value 'Workday Period

sh2.Range("D5").Value = shM.Range("G" & i).Value 'UIN

sh2.Range("E7").Value = shM.Range("H" & i).Value 'Position

sh2.Range("E9").Value = shM.Range("I" & i).Value 'Department

sh2.Range("E11").Value = shM.Range("J" & i).Value 'Office Location

sh2.Range("G11").Value = shM.Range("K" & i).Value 'Ext.

sh2.Range("E13").Value = shM.Range("L" & i).Value 'Supervisor

sh2.Range("G15").Value = shM.Range("M" & i).Value 'Pay Date

'

wb2.SaveAs "C:\Users\lorena.rodriguez\Desktop\TRAINING RESOURCES\ADJUNCT\MACRO TEMPLATES" & shM.Range("A" & i).Value & ".xlsx"

wb2.Close False

Next

End Sub

 

[DanteAmor]

sh2.Range("TextBox15").Value

Are they textbox form control or ActiveX control or is it a range name?

You could upload a copy of your file to a free site such www.dropbox.com or google drive. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. If the workbook contains confidential information, you could replace it with generic data.

 

[loribear180]

Hello @DanteAmor I uploaded it to Dropbox:

Dropbox

www.dropbox.com

I'm trying to enter all the required information in the Data tab and have each row transfer to the Rollover Template, then hopefully have Excel automatically export each template with the info from each row into it's own spreadsheet (hopefully with a naming convention taken from the Last, First Name field). I might be asking for too much especially when I don't even know how to do it, but I was hoping to learn though I don't even know what I should be learning

 

[JackDanIce]

Hi, took a look at your file and another option is to use Mail merge if you want to consider using a Word based Template (instead of Excel), which may avoid need for VBA:
YouTube Excel Mail Merge

 

[loribear180]

Hi, took a look at your file and another option is to use Mail merge if you want to consider using a Word based Template (instead of Excel), which may avoid need for VBA:
YouTube Excel Mail Merge

Hi @JackDanIce I wish I was able to use Word it would make this whole process so much simpler. Unfortunately I need it as a spreadsheet as it's the required file type when uploading to an {out dated} document repository At the moment I'm literally typing in all the information manually and it is upwards of 50 different individuals every quarter, I have got to find a way to streamline this horrible process.