insert a sheet between sheets hidden

Hi,

In my workbook, 3 sheets :
Premier, Modele, Total.
I create and insert one sheet for each day of a month between Premier and
Modele with this code :
*************************
For A = 1 To LastDay Step 1
Sheets("Modèle").Select
Sheets("Modèle").Copy Before:=Sheets("Modèle")
Set Sh = Worksheets(Worksheets.Count - 2)
With Sh
.Visible = True
.Name = Format(DateSerial(Annee, LeMois, A), "ddd dd-mm-yy")
If Err <> 0 Then
Err = 0
End If
End With
Next
*************************
If sheets named Modele and Premier are visible, OK
If sheets Modele and Premier are hidden, NOK
Why, and how correct ?

Thank You.

Best regards,

Patrick

Dave
Sat Nov 06 18:05:54 CST 2004

First, you can't select a sheet that's hidden. So drop that line.

And excel seems to ignore the before if the sheet is hidden:

Sheets("Modèle").Copy Before:=Sheets("Modèle")

I think I'd just unhide the sheet, do all the copy|renames and then rehide the
sheet:

Option Explicit
Sub testme()

Dim LastDay As Long
Dim A As Long
Dim Annee As Long
Dim Lemois As Long
Dim Sh As Worksheet

LastDay = 5
Annee = 2004
Lemois = 11

Application.ScreenUpdating = False
Sheets("Modèle").Visible = xlSheetVisible

For A = 1 To LastDay Step 1
Sheets("Modèle").Copy Before:=Sheets("Modèle")
Set Sh = ActiveSheet 'the visible copied sheet is the activesheet
With Sh
On Error Resume Next
.Name = Format(DateSerial(Annee, Lemois, A), "ddd dd-mm-yy")
On Error GoTo 0
End With
Next A

Sheets("Modèle").Visible = xlSheetHidden
Application.ScreenUpdating = True

End Sub

Patrick Bastard wrote:
>
> Hi,
>
> In my workbook, 3 sheets :
> Premier, Modele, Total.
> I create and insert one sheet for each day of a month between Premier and
> Modele with this code :
> *************************
> For A = 1 To LastDay Step 1
> Sheets("Modèle").Select
> Sheets("Modèle").Copy Before:=Sheets("Modèle")
> Set Sh = Worksheets(Worksheets.Count - 2)
> With Sh
> .Visible = True
> .Name = Format(DateSerial(Annee, LeMois, A), "ddd dd-mm-yy")
> If Err <> 0 Then
> Err = 0
> End If
> End With
> Next
> *************************
> If sheets named Modele and Premier are visible, OK
> If sheets Modele and Premier are hidden, NOK
> Why, and how correct ?
>
> Thank You.
>
> Best regards,
>
> Patrick

--

Dave Peterson
ec35720@netscape.com

Patrick
Sun Nov 07 02:58:23 CST 2004

Hi, *Dave Peterson*.

> First, you can't select a sheet that's hidden. So drop that line.
> And excel seems to ignore the before if the sheet is hidden:

> Sheets("Modèle").Copy Before:=Sheets("Modèle")
>
> I think I'd just unhide the sheet, do all the copy|renames and then
> rehide the sheet:

It is The Solution, ant with it, i can end my project.

So, many thanks to you.
;-)


--
Best regards,
=====================================
P. Bastard.
Pour me contacter, remplacez "Chez" par @ dans l'adresse

Une foule de conseils utiles sur le site de Misange :
http://www.excelabo.net