copying and renaming sheets
 

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

copying and renaming sheets
 

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

copying and renaming sheets
 

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

copying and renaming sheets
 

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

copying and renaming sheets
 

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

copying and renaming sheets
 

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

copying and renaming sheets
 

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

copying and renaming sheets
 

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

copying and renaming sheets
 

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

copying and renaming sheets
 

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