copy sheet macro problem

[dado6612]

Hi to all

I have recorded macro which copy Sheet1
That works fine until Sheet1 is veryhidden
Sheet1 suppose to be veryhidden cause its template sheet
So, when user fill up sheet2 he need to press button and he will get Sheet1, named Sheet3, u know how that works

I tried avoid that by this:

Sub copy()
Sheet1.Visible = xlSheetVeryHidden = False
    Sheets("Sheet1").Select
    Sheets("Sheet1").Copy Before:=Sheets(1)
Sheet1.Visible = xlSheetVeryHidden = True
End Sub

That didn't help me, I get part "Sheets("Sheet1").Select" colored yellow

I don't know how to fix this, can someone help me?

Thanks

 

[VoG]

Try

Sub copysheet()
Sheet1.copy after:=Sheets(Sheets.Count)
Sheets(Sheets.Count).Visible = xlSheetVisible
End Sub

 

[dado6612]

I didn't get desired result
I get error again

After pressing the button, sheet1 need to be copied to the start of workbook and must remain hidden, so new sheet will have name Sheet1(2)

When Sheet1 is visible, it works perfectly

 

[VoG]

What error did you get and on which line of code.

It worked for me when I tested it.

 

[dado6612]

runtimeerror 1004
method copy of object worksheet failed

and "Sheet1.Copy after:=Sheets(Sheets.Count)" is highlighted

 

[VoG]

The only way I can reproduce that is if the workbook is protected. Is it?

 

[dado6612]

Yes, that sheet is protected
But, as I said, when Sheet1 is visible, this works just fine
Sheet must be protected

 

[VoG]

No, is the workbook protected?

 

[dado6612]

Oh, workbook is not protected (as I know, and I should know cause I made it)

 

[VoG]

Apologies - it does error if the sheet is very hidden, not just hidden. Try

Sub copysheet()
Application.ScreenUpdating = False
With Sheet1
    .Visible = xlSheetVisible
    .copy after:=Sheets(Sheets.Count)
    .Visible = xlSheetVeryHidden
End With
Application.ScreenUpdating = True
End Sub