Forum home » Delegate support and help forum » Microsoft Excel VBA Training and help » Copying same tab several times and changing just one cell
|
|
|
The UK's most regular Training information: |
|
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Copying same tab several times and changing just one cell
Resolved · Medium Priority · Version 2010
Reply Mon 10 Oct 2011, 15:05Delegate Katherine said...
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
For upcoming training course dates see: Pricing & availability
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
Reply Mon 10 Oct 2011, 17:37Delegate Katherine said...
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
|