excel to excel mail merge?

[rikvny02]

Is it possible to use excel to create a new worksheet for each row of data. My best way to describe this would be for it to work exactly the way a mail merge would be in Microsoft Word. The difference being it would just create a new worksheet within the current workbook.

 

[Joe4]

Please show us some sample data along with your desired result.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[rikvny02]

Please show us some sample data along with your desired result.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

Joe
My IT department has restrictions which wont allow me to download the add-in. Any other suggestions.

 

[Joe4]

Take a screen print of your data and post that image here.
And also show us what you created sheets should look like.

 

[rikvny02]

I'm sorry friends of the forum, I cannot upload the xl2bb add-in as I'm doing this research from work and they have strict security . I need to create a large workbook with probably 100 sheets, 1 for each employee and then 5 more workbooks for different locations. I have a data sheet that has specific data (second picture) that id like to populate on each sheet in a new format( see first picture). I know this will be a loop of some sort but I can't figure out how to populate the data into specific cells. Thank you for all your help and guidance.

The screenshot is of my Master Sheet. All highlighted highlighted fields will need to be populated.

The screenshot below is the data that i would like to populate in each cell.

The result would be a new sheet for each row of data, with the title of the sheet named by column A (emp- 1234)

 

[Joe4]

OK, assuming that your data sheet is named "Data" and you have a blank template sheet with the structure you want named "Template", this could should do what you want:

Sub CreateSheets()

    Dim dws As Worksheet
    Dim tws As Worksheet
    Dim lr As Long
    Dim r As Long
    Dim emp As String
   
    Application.ScreenUpdating = False
   
'   Set Data and Template worksheets
    Set dws = Sheets("Data")
    Set tws = Sheets("Template")
   
'   Find last row in column A on "Data" sheet
    lr = dws.Cells(dws.Rows.Count, "A").End(xlUp).Row
   
'   Exit if no data
    If lr < 2 Then
        MsgBox "No data on data sheet", vbOKOnly, "ABORTING MACRO"
        Exit Sub
    End If
   
'   Loop through all rows on data sheet
    For r = 2 To lr
'       Get name of emp
        emp = dws.Cells(r, "A")
'       Insert new sheet and rename it
        Sheets.Add After:=ActiveSheet
        ActiveSheet.Name = emp
'       Copy over template to new sheet
        Sheets("template").Select
        Cells.Copy
        Sheets(emp).Select
        ActiveSheet.Paste
'       Copy data over from Data sheet to Emp sheet
        Sheets(emp).Range("C2") = emp 'EMP field
        Sheets(emp).Range("H2") = dws.Cells(r, "B") 'NAME field
        Sheets(emp).Range("Q2") = dws.Cells(r, "C") 'DEPT field
        'KEEP ADDING REST OF FIELDS HERE
    Next r
   
    Application.ScreenUpdating = True
   
    MsgBox "Macro complete!"
   
End Sub

Note that I only copied over the first three columns. You can complete the rest and add them where it says "'KEEP ADDING REST OF FIELDS HERE". It should follow the same pattern as the two rows above it.