envir excel in office97, winxphome

hi, from a buttonclickevent in sheet1 i want this to happen:

Loop through cells in a columnH in sheet2
Make a copy of sheet3
Rename that copy to a textvalue in current cell in columnH in sheet2
stopping at a value in a cell in sheet2

Could u help me to convert this to vba code?
thank You
regards Jan

With Worksheets("Sheet2")
for each cell in .Range(.Cells(1,8),.Cells(rows.count,8).End(xlup))
if not isempty(cell) then
worksheets("Sheet3").Copy After:=Worksheets(worksheets.count)
Activesheet.Name = Cell.Value
end if
Next
End With

--
Regards,
Tom Ogilvy

"Jan Eikeland" wrote in message
...
envir excel in office97, winxphome

hi, from a buttonclickevent in sheet1 i want this to happen:

Loop through cells in a columnH in sheet2
Make a copy of sheet3
Rename that copy to a textvalue in current cell in columnH in sheet2
stopping at a value in a cell in sheet2

Could u help me to convert this to vba code?
thank You
regards Jan

thank u and mri x-mas
reg Jan
"Tom Ogilvy" skrev i melding
...
With Worksheets("Sheet2")
for each cell in .Range(.Cells(1,8),.Cells(rows.count,8).End(xlup))
if not isempty(cell) then
worksheets("Sheet3").Copy After:=Worksheets(worksheets.count)
Activesheet.Name = Cell.Value
end if
Next
End With

--
Regards,
Tom Ogilvy

"Jan Eikeland" wrote in message
...
envir excel in office97, winxphome

hi, from a buttonclickevent in sheet1 i want this to happen:

Loop through cells in a columnH in sheet2
Make a copy of sheet3
Rename that copy to a textvalue in current cell in columnH in sheet2
stopping at a value in a cell in sheet2

Could u help me to convert this to vba code?
thank You
regards Jan

hi
im getting an error saying function not defined on marked text below :


Private Sub CommandButton1_Click()
With Worksheets("Elever")
For Each Cell In .Range(.Cells(1, 7), .Cells(Rows.Count, 7).End(xlUp))
If Not IsEmpty(Cell) Then
Worksheets("Elevmal").Copy After:=Woorksheets(Worksheets.Count)
ActiveSheet.Name = Cell.Value
End If
Next
End With
End Sub


tks reg jan

"Jan Eikeland" skrev i melding ...
thank u and mri x-mas
reg Jan
"Tom Ogilvy" skrev i melding
...
With Worksheets("Sheet2")
for each cell in .Range(.Cells(1,8),.Cells(rows.count,8).End(xlup))
if not isempty(cell) then
worksheets("Sheet3").Copy After:=Worksheets(worksheets.count)
Activesheet.Name = Cell.Value
end if
Next
End With

--
Regards,
Tom Ogilvy

"Jan Eikeland" wrote in message
...
envir excel in office97, winxphome

hi, from a buttonclickevent in sheet1 i want this to happen:

Loop through cells in a columnH in sheet2
Make a copy of sheet3
Rename that copy to a textvalue in current cell in columnH in sheet2
stopping at a value in a cell in sheet2

Could u help me to convert this to vba code?
thank You
regards Jan

That is your typo Jan, I wrote Worksheets, you wrote Woorksheets.

--
Regards,
Tom Ogilvy

"Jan Eikeland" wrote in message ...
hi
im getting an error saying function not defined on marked text below :


Private Sub CommandButton1_Click()
With Worksheets("Elever")
For Each Cell In .Range(.Cells(1, 7), .Cells(Rows.Count, 7).End(xlUp))
If Not IsEmpty(Cell) Then
Worksheets("Elevmal").Copy After:=Woorksheets(Worksheets.Count)
ActiveSheet.Name = Cell.Value
End If
Next
End With
End Sub


tks reg jan

"Jan Eikeland" skrev i melding ...
thank u and mri x-mas
reg Jan
"Tom Ogilvy" skrev i melding
...
With Worksheets("Sheet2")
for each cell in ..Range(.Cells(1,8),.Cells(rows.count,8).End(xlup) )
if not isempty(cell) then
worksheets("Sheet3").Copy After:=Worksheets(worksheets.count)
Activesheet.Name = Cell.Value
end if
Next
End With

--
Regards,
Tom Ogilvy

"Jan Eikeland" wrote in message
...
envir excel in office97, winxphome

hi, from a buttonclickevent in sheet1 i want this to happen:

Loop through cells in a columnH in sheet2
Make a copy of sheet3
Rename that copy to a textvalue in current cell in columnH in sheet2
stopping at a value in a cell in sheet2

Could u help me to convert this to vba code?
thank You
regards Jan

oops, shame on me,
sorry
tks

"Tom Ogilvy" skrev i melding
...
That is your typo Jan, I wrote Worksheets, you wrote Woorksheets.

--
Regards,
Tom Ogilvy

"Jan Eikeland" wrote in message
...
hi
im getting an error saying function not defined on marked text below :


Private Sub CommandButton1_Click()
With Worksheets("Elever")
For Each Cell In .Range(.Cells(1, 7), .Cells(Rows.Count, 7).End(xlUp))
If Not IsEmpty(Cell) Then
Worksheets("Elevmal").Copy After:=Woorksheets(Worksheets.Count)
ActiveSheet.Name = Cell.Value
End If
Next
End With
End Sub


tks reg jan

"Jan Eikeland" skrev i melding
...
thank u and mri x-mas
reg Jan
"Tom Ogilvy" skrev i melding
...
With Worksheets("Sheet2")
for each cell in .Range(.Cells(1,8),.Cells(rows.count,8).End(xlup))
if not isempty(cell) then
worksheets("Sheet3").Copy After:=Worksheets(worksheets.count)
Activesheet.Name = Cell.Value
end if
Next
End With

--
Regards,
Tom Ogilvy

"Jan Eikeland" wrote in message
...
envir excel in office97, winxphome

hi, from a buttonclickevent in sheet1 i want this to happen:

Loop through cells in a columnH in sheet2
Make a copy of sheet3
Rename that copy to a textvalue in current cell in columnH in sheet2
stopping at a value in a cell in sheet2

Could u help me to convert this to vba code?
thank You
regards Jan

hm, correct the failure and get runtimeerror 1004:
Copy method in Worksheet-class failure.

regards Jan

"Jan Eikeland" skrev i melding
...
oops, shame on me,
sorry
tks

"Tom Ogilvy" skrev i melding
...
That is your typo Jan, I wrote Worksheets, you wrote Woorksheets.

--
Regards,
Tom Ogilvy

"Jan Eikeland" wrote in message
...
hi
im getting an error saying function not defined on marked text below :


Private Sub CommandButton1_Click()
With Worksheets("Elever")
For Each Cell In .Range(.Cells(1, 7), .Cells(Rows.Count, 7).End(xlUp))
If Not IsEmpty(Cell) Then
Worksheets("Elevmal").Copy After:=Woorksheets(Worksheets.Count)
ActiveSheet.Name = Cell.Value
End If
Next
End With
End Sub


tks reg jan

"Jan Eikeland" skrev i melding
...
thank u and mri x-mas
reg Jan
"Tom Ogilvy" skrev i melding
...
With Worksheets("Sheet2")
for each cell in .Range(.Cells(1,8),.Cells(rows.count,8).End(xlup))
if not isempty(cell) then
worksheets("Sheet3").Copy After:=Worksheets(worksheets.count)
Activesheet.Name = Cell.Value
end if
Next
End With

--
Regards,
Tom Ogilvy

"Jan Eikeland" wrote in message
...
envir excel in office97, winxphome

hi, from a buttonclickevent in sheet1 i want this to happen:

Loop through cells in a columnH in sheet2
Make a copy of sheet3
Rename that copy to a textvalue in current cell in columnH in
sheet2
stopping at a value in a cell in sheet2

Could u help me to convert this to vba code?
thank You
regards Jan

If you are running this in Excel 97 and you put the code in the click event
for a commandbutton (which it appears you did), change the takefocusonclick
property of the commandbutton to false (just a guess as a possible source
of your error).

Anyway,

Sub AACopy()
With Worksheets("Sheet2")
For Each Cell In .Range(.Cells(1, 8), .Cells(Rows.Count, 8).End(xlUp))
If Not IsEmpty(Cell) Then
Worksheets("Sheet3").Copy After:=Worksheets(Worksheets.Count)
ActiveSheet.Name = Cell.Value
End If
Next
End With

End Sub

The above code, which is the original code I provided, worked fine for me.

In sheet2 I put
H1: A
H2: B
H3: C
H4: D

I ran the macro and it made 4 copies of Sheet3, naming them A, B, C, D
respectively.

also, Your workbook isn't protected is it?

--
Regards,
Tom Ogilvy

"Jan Eikeland" wrote in message
...
hm, correct the failure and get runtimeerror 1004:
Copy method in Worksheet-class failure.

regards Jan

"Jan Eikeland" skrev i melding
...
oops, shame on me,
sorry
tks

"Tom Ogilvy" skrev i melding
...
That is your typo Jan, I wrote Worksheets, you wrote Woorksheets.

--
Regards,
Tom Ogilvy

"Jan Eikeland" wrote in message
...
hi
im getting an error saying function not defined on marked text below :


Private Sub CommandButton1_Click()
With Worksheets("Elever")
For Each Cell In .Range(.Cells(1, 7), .Cells(Rows.Count, 7).End(xlUp))
If Not IsEmpty(Cell) Then
Worksheets("Elevmal").Copy After:=Woorksheets(Worksheets.Count)
ActiveSheet.Name = Cell.Value
End If
Next
End With
End Sub


tks reg jan

"Jan Eikeland" skrev i melding
...
thank u and mri x-mas
reg Jan
"Tom Ogilvy" skrev i melding
...
With Worksheets("Sheet2")
for each cell in
..Range(.Cells(1,8),.Cells(rows.count,8).End(xlup) )
if not isempty(cell) then
worksheets("Sheet3").Copy After:=Worksheets(worksheets.count)
Activesheet.Name = Cell.Value
end if
Next
End With

--
Regards,
Tom Ogilvy

"Jan Eikeland" wrote in message
...
envir excel in office97, winxphome

hi, from a buttonclickevent in sheet1 i want this to happen:

Loop through cells in a columnH in sheet2
Make a copy of sheet3
Rename that copy to a textvalue in current cell in columnH in
sheet2
stopping at a value in a cell in sheet2

Could u help me to convert this to vba code?
thank You
regards Jan

hi, ur suggestion of takefocusonclick to false did it.
But i run into error saying no names longer than 31 letters.
Well, suppose I cant sneak around this matter(?), so Ill shorten the names

thank you
regards jan
"Tom Ogilvy" skrev i melding
...
If you are running this in Excel 97 and you put the code in the click
event
for a commandbutton (which it appears you did), change the
takefocusonclick
property of the commandbutton to false (just a guess as a possible source
of your error).

Anyway,

Sub AACopy()
With Worksheets("Sheet2")
For Each Cell In .Range(.Cells(1, 8), .Cells(Rows.Count, 8).End(xlUp))
If Not IsEmpty(Cell) Then
Worksheets("Sheet3").Copy After:=Worksheets(Worksheets.Count)
ActiveSheet.Name = Cell.Value
End If
Next
End With

End Sub

The above code, which is the original code I provided, worked fine for me.

In sheet2 I put
H1: A
H2: B
H3: C
H4: D

I ran the macro and it made 4 copies of Sheet3, naming them A, B, C, D
respectively.

also, Your workbook isn't protected is it?

--
Regards,
Tom Ogilvy

"Jan Eikeland" wrote in message
...
hm, correct the failure and get runtimeerror 1004:
Copy method in Worksheet-class failure.

regards Jan

"Jan Eikeland" skrev i melding
...
oops, shame on me,
sorry
tks

"Tom Ogilvy" skrev i melding
...
That is your typo Jan, I wrote Worksheets, you wrote Woorksheets.

--
Regards,
Tom Ogilvy

"Jan Eikeland" wrote in message
...
hi
im getting an error saying function not defined on marked text below
:


Private Sub CommandButton1_Click()
With Worksheets("Elever")
For Each Cell In .Range(.Cells(1, 7), .Cells(Rows.Count,
7).End(xlUp))
If Not IsEmpty(Cell) Then
Worksheets("Elevmal").Copy After:=Woorksheets(Worksheets.Count)
ActiveSheet.Name = Cell.Value
End If
Next
End With
End Sub


tks reg jan

"Jan Eikeland" skrev i melding
...
thank u and mri x-mas
reg Jan
"Tom Ogilvy" skrev i melding
...
With Worksheets("Sheet2")
for each cell in
.Range(.Cells(1,8),.Cells(rows.count,8).End(xlup))
if not isempty(cell) then
worksheets("Sheet3").Copy
After:=Worksheets(worksheets.count)
Activesheet.Name = Cell.Value
end if
Next
End With

--
Regards,
Tom Ogilvy

"Jan Eikeland" wrote in message
...
envir excel in office97, winxphome

hi, from a buttonclickevent in sheet1 i want this to happen:

Loop through cells in a columnH in sheet2
Make a copy of sheet3
Rename that copy to a textvalue in current cell in columnH in
sheet2
stopping at a value in a cell in sheet2

Could u help me to convert this to vba code?
thank You
regards Jan

Yes, you will have to keep the names within the limit.

--
Regards,
Tom Ogilvy

"Jan Eikeland" wrote in message
...
hi, ur suggestion of takefocusonclick to false did it.
But i run into error saying no names longer than 31 letters.
Well, suppose I cant sneak around this matter(?), so Ill shorten the names

thank you
regards jan
"Tom Ogilvy" skrev i melding
...
If you are running this in Excel 97 and you put the code in the click
event
for a commandbutton (which it appears you did), change the
takefocusonclick
property of the commandbutton to false (just a guess as a possible
source
of your error).

Anyway,

Sub AACopy()
With Worksheets("Sheet2")
For Each Cell In .Range(.Cells(1, 8), .Cells(Rows.Count, 8).End(xlUp))
If Not IsEmpty(Cell) Then
Worksheets("Sheet3").Copy After:=Worksheets(Worksheets.Count)
ActiveSheet.Name = Cell.Value
End If
Next
End With

End Sub

The above code, which is the original code I provided, worked fine for
me.

In sheet2 I put
H1: A
H2: B
H3: C
H4: D

I ran the macro and it made 4 copies of Sheet3, naming them A, B, C, D
respectively.

also, Your workbook isn't protected is it?

--
Regards,
Tom Ogilvy

"Jan Eikeland" wrote in message
...
hm, correct the failure and get runtimeerror 1004:
Copy method in Worksheet-class failure.

regards Jan

"Jan Eikeland" skrev i melding
...
oops, shame on me,
sorry
tks

"Tom Ogilvy" skrev i melding
...
That is your typo Jan, I wrote Worksheets, you wrote Woorksheets.

--
Regards,
Tom Ogilvy

"Jan Eikeland" wrote in message
...
hi
im getting an error saying function not defined on marked text
below
:


Private Sub CommandButton1_Click()
With Worksheets("Elever")
For Each Cell In .Range(.Cells(1, 7), .Cells(Rows.Count,
7).End(xlUp))
If Not IsEmpty(Cell) Then
Worksheets("Elevmal").Copy After:=Woorksheets(Worksheets.Count)
ActiveSheet.Name = Cell.Value
End If
Next
End With
End Sub


tks reg jan

"Jan Eikeland" skrev i melding
...
thank u and mri x-mas
reg Jan
"Tom Ogilvy" skrev i melding
...
With Worksheets("Sheet2")
for each cell in
.Range(.Cells(1,8),.Cells(rows.count,8).End(xlup))
if not isempty(cell) then
worksheets("Sheet3").Copy
After:=Worksheets(worksheets.count)
Activesheet.Name = Cell.Value
end if
Next
End With

--
Regards,
Tom Ogilvy

"Jan Eikeland" wrote in message
...
envir excel in office97, winxphome

hi, from a buttonclickevent in sheet1 i want this to happen:

Loop through cells in a columnH in sheet2
Make a copy of sheet3
Rename that copy to a textvalue in current cell in columnH
in
sheet2
stopping at a value in a cell in sheet2

Could u help me to convert this to vba code?
thank You
regards Jan