Private Sub CommandButton1_Click()
Dim LastCell As Range, Rng As Range, cell As Range
Dim WS As Worksheet
Set WS = ActiveSheet
Set LastCell = WS.Cells(Rows.Count, "A").End(xlUp)
Set Rng = WS.Range("A2", LastCell)
For Each cell In Rng
If Not IsEmpty(cell) Then
Sheets.Add.Name = cell.Value
End If
Next
End Sub
This makes the worksheets with the name of the values, but I can't
quite get it to use the template for copying. Any help on this is
greatly appreciated.
Ardy
Sheets.Add.Name = cell.Value
add this
Sheets("template").Cells.Copy Destination:=Sheets(cell.Value).Range("A1")
--
Cheers
Nigel
"Ardy" <ar...@totlsolution.com> wrote in message
news:1164260481.8...@l12g2000cwl.googlegroups.com...
Private Sub CommandButton1_Click()
Dim LastCell As Range, Rng As Range, cell As Range
Dim WS As Worksheet
Set WS = ActiveSheet
Set LastCell = WS.Cells(Rows.Count, "A").End(xlUp)
Set Rng = WS.Range("A2", LastCell)
For Each cell In Rng
If Not IsEmpty(cell) Then
Sheets.Add.Name = cell.Value
Sheets("Template").Cells.Copy
Destination:=Sheets(cell.Value).Range("A1")
End If
Next
End Sub
I get an -->Run-Time error"9"
Subscript out of range.
I am trying diffrent variations of code but so far am not successfull.
Should A1 be A2
Ardy
Nigel wrote:
> after the line
>
> Sheets.Add.Name = cell.Value
>
> add this
>
> Sheets("Template").Cells.Copy Destination:=Sheets(cell.Value).Range("A1")
--
Cheers
Nigel
"Ardy" <ar...@totlsolution.com> wrote in message
news:1164263803.0...@m7g2000cwm.googlegroups.com...
Set WS = ActiveSheet
Set LastCell = WS.Cells(Rows.Count, "A").End(xlUp)
Set Rng = WS.Range("A2", LastCell)
For Each cell In Rng
If Not IsEmpty(cell) Then
Sheets("Template").Copy after:=Worksheets(Worksheets.Count)
ActiveSheet.Name = cell.Value
End If
Next
End Sub
--
HTH
Bob Phillips
(replace xxxx in the email address with gmail if mailing direct)
"Ardy" <ar...@totlsolution.com> wrote in message
news:1164260481.8...@l12g2000cwl.googlegroups.com...
Bob, Nigel
I have created a second Command button to preserve my first code. I
get the same error on Bob's version of the code. When it says out of
range dose this mean range (Colum A is grater than it can handle) I
only have 3 names for the sake of testing. It potentially could go to
40. Not to over extend my welcome I also am trying to link the
names(in the Roster Tab) to their respected tabs (worksheets)either
after or during the creation. This is for the Teachers to easily
navigate to each student tab. I have already have code in each tab to
navigate back to the Roster.
--------------Latest Code ---------------------------------
Private Sub CommandButton2_Click()
Dim LastCell As Range, Rng As Range, cell As Range
Dim WS As Worksheet
Set WS = ActiveSheet
Set LastCell = WS.Cells(Rows.Count, "A").End(xlUp)
Set Rng = WS.Range("A2", LastCell)
For Each cell In Rng
If Not IsEmpty(cell) Then
Sheets("Template").Copy after:=Worksheets(Worksheets.Count)
ActiveSheet.Name = cell.Value
End If
Next
End Sub
---------------------------------------------------------------------
Where do you get the error?
We'll get the create to work first, then worry about the navigating.
BTW, what is a K9 teacher?
--
HTH
Bob Phillips
(replace xxxx in the email address with gmail if mailing direct)
"Ardy" <ar...@totlsolution.com> wrote in message
news:1164304821.4...@f16g2000cwb.googlegroups.com...
I have some images that might help you see what I see. At
http://www.pesare-darya.com/error.htm
Eventually when I get it working all names will be under column A. K9
is the term used for teachers that are certified by state to teach in
public schools grade Kinder to 9th grade, basically your elementary
school all the way to end of middle school. My wife is a 1st grade
teacher. It sound like dog trainer K9 some time I bug her abt
that.....LOL.
Ardy
You had said it worked at one stage, I recall. Can you check after the
error has arisen that the sheet Template is still there? Also one other
thought change the test for an empty cell in the range (with names) If
Not IsEmpty(cell) Then to this
If Len(Trim(cell)) > 0 then
--
Cheers
Nigel
"Ardy" <ar...@totlsolution.com> wrote in message
news:1164353197.8...@j44g2000cwa.googlegroups.com...
Ardy
Public Sub ProcessData()
Dim iLastRow As Long
Dim i As Long
Dim sh As Worksheet
Dim cell As Range
With ActiveSheet
iLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = iLastRow To 1 Step -1
.Hyperlinks.Add Anchor:=Cells(i, "A"), _
Address:="", _
SubAddress:=Cells(i, "A").Value & "!A1", _
TextToDisplay:=Cells(i, "A").Value
Next i
End With
End Sub
--
HTH
Bob Phillips
(replace xxxx in the email address with gmail if mailing direct)
"Ardy" <ar...@totlsolution.com> wrote in message
news:1164355027....@j44g2000cwa.googlegroups.com...
Ardy
--
HTH
Bob Phillips
(replace xxxx in the email address with gmail if mailing direct)
"Ardy" <ar...@totlsolution.com> wrote in message
news:1164400476.4...@l39g2000cwd.googlegroups.com...
------------------------------------Latest
Code-----------------------------------
Public Sub ProcessData()
Dim iLastRow As Long
Dim i As Long
Dim sh As Worksheet
Dim cell As Range
With ActiveSheet
iLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = iLastRow To 2 Step -2
Sheets("Roster").Hyperlinks.Add Anchor:=Cells(i,
"A"), _
Address:="", _
SubAddress:=Cells(i, "A").Value & "!A1", _
TextToDisplay:=Cells(i, "A").Value
Next i
End With
End Sub
--
HTH
Bob Phillips
(replace xxxx in the email address with gmail if mailing direct)
"Ardy" <ar...@totlsolution.com> wrote in message
news:1164412627.7...@m7g2000cwm.googlegroups.com...
My mistake Ardy, I didn't cater for spaces in the name. Try this
Private Sub CommandButton2_Click()
' Public Sub ProcessData()
' for testing
Dim iLastRow As Long
Dim i As Long
Dim sh As Worksheet
Dim cell As Range
With ActiveSheet
iLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = iLastRow To 1 Step -1
.Hyperlinks.Add Anchor:=Cells(i, "A"), _
Address:="", _
SubAddress:="'" & Cells(i, "A").Value & "'!A1",
_
TextToDisplay:=Cells(i, "A").Value
Next i
End With
End Sub
--
HTH
Bob Phillips
(replace xxxx in the email address with gmail if mailing direct)
"Ardy" <ar...@totlsolution.com> wrote in message
news:1164505344....@f16g2000cwb.googlegroups.com...