insert a sheet between sheets hidden

  • Thread starter Thread starter Patrick Bastard
  • Start date Start date
P

Patrick Bastard

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
 
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
 
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:
Click to expand...
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:
Click to expand...

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