Katherine has attended:
Excel Advanced course
Excel VBA Intro Intermediate course
Copying same tab several times and changing just one cell
I have got a worksheet set up which has one cell (made up of a 4 digit code) that can be changed. I need to be able to replicate the same worksheet for upto 100 other 4 digit codes which I have placed into a list on another worksheet within the same workbook.
Ideally, the VBA will also rename the worksheets so that it is the same as the 4 digit code.
Would this be possible to do with a VBA?
Many thanks,
Katherine
RE: Copying same tab several times and changing just one cell
Hi Katherine, thanks for your query. It certainly is possible! You will need to loop through that list, dropping each cell value into a value and then using:
Sheets.Add
Activesheet.name = thecode
The result will be a lot of new worksheets named after your 4 digit code!
Hope this helps,
Anthony
RE: Copying same tab several times and changing just one cell
Thank you for the response. I found the code below which allowed me to copy, then using the knowledge learned in the class and your response I managed to tweak it to work.
Thanks,
Katherine
Sub Copier2()
Dim x As Integer
x = InputBox("Enter number of times to copy Sheet1")
For numtimes = 1 To x
'Loop by using x as the index number to make x number copies.
'Replace "Sheet1" with the name of the sheet to be copied.
ActiveWorkbook.Sheets("Sheet1").Copy _
After:=ActiveWorkbook.Sheets("Sheet1")
Next
End Sub
