copying and renaming sheets

  • Thread starter Thread starter Jan Eikeland
  • Start date Start date
J

Jan Eikeland

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
 
thank u and mri x-mas
reg Jan
Tom Ogilvy said:
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
Click to expand...
 
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
 
That is your typo Jan, I wrote Worksheets, you wrote Woorksheets.

--
Regards,
Tom Ogilvy

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
 
oops, shame on me,
sorry
tks

"Tom Ogilvy" <[email protected]> skrev i melding
That is your typo Jan, I wrote Worksheets, you wrote Woorksheets.

--
Regards,
Tom Ogilvy

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
 
hm, correct the failure and get runtimeerror 1004:
Copy method in Worksheet-class failure.

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?
 
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
 
Back
Top