Copy sheet to new book without referencing original book

[cocopops2001]

Dear All,

Two problems, one may be simple the other not so simple.

I have a workbook which is essentially a MENU workbook that I do not want anyone to change any values, formulas, formatting, colours etc.

The first problem is that I have buttons along the top of each page with a 'template' underneath. I want users to be able to push the buttons without editing the important part of the sheet I am using the following code in a seperate module:

[/vba]

Sub Unprotect()
ActiveSheet.Protect
ActiveSheet.Range("A11:N70").Locked = True
End Sub

[/vba]

The module is called using this peice of code in the this workbook section.

[/vba]

Private Sub Worksheet_Select()
Unprotect
End Sub

[/vba]

When I run it in the VBA editor it says 'runtime error 1004' unable to set the locked property of the range class. Any ideas?

My second problem is that when I copy these 'templates' into a new workbook the cell references still refer to the original MENU workbook ie ='[menu.xls]Cover Sheet'!$H$51 when i want it to refer to the cover sheet in the new work book.

I am copying the sheets using:

[/vba]

Sub sheetcopy()
ActiveSheet.Range("a11:n70").Copy

'subroutine to open select a workbook in which to place the calc sheet(module6)
OpenCalc


Worksheets.Add after:=Worksheets(Worksheets.Count)
Worksheets(Worksheets.Count).Range("a1").PasteSpecial
Application.CutCopyMode = False
End Sub

[/vba]

the open calc module is:
[/vba]
Sub OpenCalc()

Dim fn
fn = Application.GetOpenFilename 'can add parameters. See help for details.
If fn = False Then
MsgBox "Nothing Chosen"
Else
MsgBox "You chose " & fn
Workbooks.Open fn
End If

End Sub
[/vba]

Any help on this would be great.

Thanks.

 

[GTO]

Dear All,

Two problems, one may be simple the other not so simple.

I have a workbook which is essentially a MENU workbook that I do not want anyone to change any values, formulas, formatting, colours etc.

The first problem is that I have buttons along the top of each page with a 'template' underneath. I want users to be able to push the buttons without editing the important part of the sheet I am using the following code in a seperate module:

[/vba]

Sub Unprotect()
ActiveSheet.Protect
ActiveSheet.Range("A11:N70").Locked = True
End Sub

[/vba]

For the first bit, I believe you want the sheet unprotected, then change the cells' locked property, then protect the sheet

 

[GTO]

BTW, the tags should be [code]...code...[/code]

 

[Andrew Poulsom]

Welcome to MrExcel.

You can't change the Locked property of cells on a protected worksheet.

To remove the links you need to change the source so that it refers to the workbook you copied to. That can be done in VBA. Start by recording a macro while doing it manually to get the syntax.

 

[cocopops2001]

when recording the macro i get:

ActiveCell.FormulaR1C1 = "='Cover Sheet'!R51C8"

does this mean I will have to define all of my formula cells in the copy part of the code or is there some way of gettin the code to automatically replace the [menu.xls] part with a blank so that it references the 'new' cover sheet

 

[Andrew Poulsom]

Choose Edit|Links and change the Source. What code do you get then?

 

[GTO]

...

 

[cocopops2001]

havent got round to it yet, have a bit of a problem with my book now.

i have a code that hides the 'template' sheets if macros are disabled using the xlsheetveryhidden command and shows an intro page telling the user to enable macros.

when i last closed the book i clicked cancel when it asked if i wanted to save changes and now it only shows the intro sheet whether macros are enabled or disabled. i am quite confused and cant seem to fix this at the moment.

but i think the edit links thing should work, i will let you know how i get on once i have fixed this issue.

 

[cocopops2001]

hey got my workbook up and running again. this time recording macro gives me:

ActiveWorkbook.ChangeLink Name:="intro.xls", NewName:= _

should i just set the new name to active 'activeWorkbook.Sheet1' (sheet1 will be the cover sheet)

 

[Andrew Poulsom]

Have you tried using ActiveWorkbook.FullName?