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.

 

[loribear180]

@JackDanIce since you mentioned the Word mail merge, what is the equivalent process in Excel? I'm willing to put in the time to learn it, I just need to know what I'm supposed to be Googling.

 

[JackDanIce]

It works by moving data from a table in Excel into Word, there's a link in my reply that is to videos on YouTube, as below:

youtube excel mail merge - Google Search

 

[DanteAmor]

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

You have a textbox shape!

It is unusual that object for a templeate. But here is the solution:

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.Shapes("TextBox 15").TextFrame.Characters.Text = shM.Range("A" & i).Value   'DC or AJ
    sh2.Shapes("TextBox 16").TextFrame.Characters.Text = shM.Range("B" & i).Value  'Last, First Name
    sh2.Shapes("TextBox 17").TextFrame.Characters.Text = shM.Range("C" & i).Value  'Annual Work Period Start
    sh2.Shapes("TextBox 18").TextFrame.Characters.Text = shM.Range("D" & i).Value  'Term
    sh2.Shapes("TextBox 20").TextFrame.Characters.Text = shM.Range("E" & i).Value  '% Effort
    sh2.Shapes("TextBox 19").TextFrame.Characters.Text = 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

I'm not sure the folder if it is with \ before the filename:
"C:\Users\lorena.rodriguez\Desktop\TRAINING RESOURCES\ADJUNCT\MACRO TEMPLATES\"

 

[loribear180]

You have a textbox shape!

It is unusual that object for a templeate. But here is the solution:

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.Shapes("TextBox 15").TextFrame.Characters.Text = shM.Range("A" & i).Value   'DC or AJ
    sh2.Shapes("TextBox 16").TextFrame.Characters.Text = shM.Range("B" & i).Value  'Last, First Name
    sh2.Shapes("TextBox 17").TextFrame.Characters.Text = shM.Range("C" & i).Value  'Annual Work Period Start
    sh2.Shapes("TextBox 18").TextFrame.Characters.Text = shM.Range("D" & i).Value  'Term
    sh2.Shapes("TextBox 20").TextFrame.Characters.Text = shM.Range("E" & i).Value  '% Effort
    sh2.Shapes("TextBox 19").TextFrame.Characters.Text = 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

I'm not sure the folder if it is with \ before the filename:
"C:\Users\lorena.rodriguez\Desktop\TRAINING RESOURCES\ADJUNCT\MACRO TEMPLATES\"

@DanteAmor I must be really dumb or something because it just isn't working for me does the Dropbox file work? For some reason when I paste the code intot the spreadsheet in my desktop it just gives me error after error.

 

[DanteAmor]

I paste the code intot the spreadsheet in my desktop it just gives me error after error.

What the error message says. If you press the "Debug" button, which line of the macro is highlighted?

Try with my test file. The new files will be created in the same folder where you have the file with the macro.

The macro is in "Module1"

TEST MASTER Rollover Checklist v2.xlsm

drive.google.com

 

[loribear180]

Could it be my Excel that is creating issues??

I receive the following error:

With this line highlighted:

 

[DanteAmor]

It's the name that appears on the file you shared.
Check the name of each Shape-Textbox.
Select the Shape-Textbox and check the name in the name box.

That data is what should be put in the line of code:

sh2.Shapes("TextBox 15").TextFrame.Characters.Text = shM.Range("A" & i).Value   'DC or AJ

 

[loribear180]

I did list each textbox name, they're all just simply titled TextBox 15, TextBox 16, etc. Could it be due to the spaces in the titles?

 

[DanteAmor]

Spaces don't matter, it works for me.
You also have problems with the file that I shared with you in post #15?

 

[loribear180]

Yes, with your file I get the following: