New user could use some help. I've Google this problem and spliced together a bunch of VBA code in an effort to make this work, but it's not going well. I understand some of the basics of how to read VBA, but not how to write it. Here's the situation:
-I have a workbook with three sheets: Instructions, Master, and Template.
-Instructions can be safely ignored.
-"Master" has user-input values in many cells, but in particular the user places text strings/numbers (usually sequential numbers) in cells Range("I2:W2").
-"Template" is a similar form on which the user needs a copy for every unique value entered in I2:W2, excluding blanks.
-The script should make a copy of Template, place it immediately in front of the template, and name the new sheet to match the value in the Range, and do this for all cells in the Range containing data. In short, if my range has the sequence 1, 2, 3 in I2, J2, and K2, I should see Master, 1, 2,3, Template as workbook names when the script finishes.
-Users may have to run this script more than once - when a new value is entered in the range, they should be able to run the script again to create additional new sheets, but ignore the existing ones (this is the part giving me trouble).
And I'm trying to run this based on a button press. Here is the garbled mess of code I've scrambled together.
This is currently terminating on an error when it hits a blank. Prior to this, I had the much shorter rcell-related snippet, but it could only be run once - it was freaking out about duplicate values.
Help is very much appreciated. Please feel free to ignore that mess and start from scratch.
-I have a workbook with three sheets: Instructions, Master, and Template.
-Instructions can be safely ignored.
-"Master" has user-input values in many cells, but in particular the user places text strings/numbers (usually sequential numbers) in cells Range("I2:W2").
-"Template" is a similar form on which the user needs a copy for every unique value entered in I2:W2, excluding blanks.
-The script should make a copy of Template, place it immediately in front of the template, and name the new sheet to match the value in the Range, and do this for all cells in the Range containing data. In short, if my range has the sequence 1, 2, 3 in I2, J2, and K2, I should see Master, 1, 2,3, Template as workbook names when the script finishes.
-Users may have to run this script more than once - when a new value is entered in the range, they should be able to run the script again to create additional new sheets, but ignore the existing ones (this is the part giving me trouble).
And I'm trying to run this based on a button press. Here is the garbled mess of code I've scrambled together.
Code:
Private Sub CommandButton1_Click()
Dim wks, xlWSH As Worksheet
Dim myRange, Cell As Range
Dim ProtectIt As Boolean
'Refer to sheet name where you save your sheet names list
Set wks = Worksheets("Master")
With wks
'Refer to first cell where your sheet names list starts. Here is "A1"
Set myRange = Range("I2:W2")
End With
For Each xlWSH In ActiveWorkbook.Worksheets
For Each Cell In myRange
'If sheet name is in your list then set DoIt to False
If xlWSH.Name = Cell.Value Then
DoIt = False
Exit For
Else
DoIt = True
End If
Next Cell
If DoIt = True Then
With xlWSH
Dim rcell As Range
For Each rcell In Range("I2:W2")
If rcell.Value <> "" Then
Sheets("Template").Copy Before:=Sheets("Template")
Sheets("Template (2)").Name = rcell.Value
End If
Next rcell
End With
End If
Next xlWSH
End Sub
This is currently terminating on an error when it hits a blank. Prior to this, I had the much shorter rcell-related snippet, but it could only be run once - it was freaking out about duplicate values.
Help is very much appreciated. Please feel free to ignore that mess and start from scratch.