Creating multiple workbooks and copying data from the source file

[jayjavaid]

Hi
hope someone can help me with my query

I have template that has several sheets in it, in order to populate those sheets I enter an 8 digit number in Sheet 1, cell B3. I then SaveAs the sheet and save it with the employees name. I have to do 150 of these so I was hoping to automate the process.

I have created a macro enable workbook that has Names in column A and 8 digit number in column B

I would like the macro to

1. Open the template
2. SaveAs the template with the name in column A present in the 2nd workbook
3. Copy the 8 digit number from 2nd worksheet to the template in cell B3
4. Save
5. Close and go to next line.

I have been trying to do this since yesterday and if the template opens and saves, it doesn't copy paste the 8 digit number or it doesn't go to the next step

Any help will be greatly appreciated

Thanks
J

 

[JLGWhiz]

You will need to substitute the actual file name for the template file.

Sub copyNsave()
Dim wb As Workbook, sh1 As Worksheet, sh2 As Worksheet, c As Range
fPath = ThisWorkbook.Path 'assumes both workbooks are in the same directory, if not fix it.
If Right(fPath, 1) <> "\" Then fPath = fPath & "\"
Set wb = Workbooks.Open(fPath & "Template.xlsx") 'Edit workbook name and extension
Set sh1 = wb.Sheets(1) 'Edit sheet name
Set sh2 = ThisWorkbook.Sheets(1) 'Edit sheet name
    For Each c In sh2.Range("A1", sh2.Cells(Rows.Count, 1).End(xlUp)) 'Assumes no header
        sh1.Range("B3") = c.Offset(, 1).Value
        wb.SaveAs c.Value & ".xlsx"
    Next
End Sub

You can see by all the assumptions that quite a bit of information was lacking in the OP. But maybe you can work with it.

 

[jayjavaid]

thank you so much!...this was fantastic!!!!. I amended it a little bit and it worked!!
really appreciate it

 

[JLGWhiz]

thank you so much!...this was fantastic!!!!. I amended it a little bit and it worked!!
really appreciate it

It is helpful to other forum members if you post your working macro so they can benefit from your experience.
Regards, JLG