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.

 

[cocopops2001]

no, I have only use VBA for a couple of months and only fully started on it this week while trying to get this thing up and running. basically i want the cells to reference the cover sheet in the new active workbook but i have no idea where the active workbook would be as it could be on any machine and used by multiple users. I am maybe at the wrong stage as during the copy sheet process i would like a form where the user can either select a file to use or create a new one.

from this would it be quite simple to store the file name somewhere and then reference this variable in my change links part of the macro?

I am not new to programming but have never really used it in any significant way.

 

[Andrew Poulsom]

Why don't you try it?

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

 

[cocopops2001]

Have you tried using ActiveWorkbook.FullName?

when i use this after 'NewName:=' i get a runtime error of 1004 and it tells me that the formulas in this workbook do not have valid references.

would this make sense as the book is trying to reference itself?

 

[Andrew Poulsom]

How about?

ActiveWorkbook.ChangeLink Name:="intro.xls", NewName:=ActiveWorkbook.Name, Type:=xlExcelLinks

 

[cocopops2001]

still the same error message im afraid

 

[Andrew Poulsom]

If you record a macro what do you get. This is what I got:

Sub Macro2()
'
' Macro2 Macro
' Macro recorded 20/04/2011 by Andrew
'
'
    ActiveWorkbook.ChangeLink Name:="Book1.xls", NewName:="Book2.xls", Type:= _
        xlExcelLinks
End Sub

The worksheet being referenced does have the same name in both workbooks doesn't it?

 

[cocopops2001]

i get

Sub Macro5()
'
' Macro5 Macro
' Macro recorded 20/04/2011 by Stewart Livingston
'

'
    ActiveWorkbook.ChangeLink Name:="intro.xls", NewName:= _
        "M:\spreadsheets\New Microsoft Excel Worksheet.xls", Type:=xlExcelLinks
End Sub

yes the sheet being referenced is the same in the master and in the new book

 

[cocopops2001]

got it working now, thanks very much for all your help. i had the code the wrong way round. i was trying to paste in the new sheet before checking that a cover sheet existed and if not create one. silly me

 

[cocopops2001]

the code that was given to me earlier works fine as long as the master file is called 'intro.xls' but i want to put in something that will still work if the master filename is changed i have tried

ActiveWorkbook.ChangeLink Name:=ThisWorkbook.Name, NewName:=ActiveWorkbook.Name, Type:=xlExcelLinks

but i get a runtime error 1004 "Method 'ChangeLink' of object '_Workbook' failed "

i have also tried

dim wb as string 
set wb = thisworkbook.name

and put "wb" & ".xls" in place of thisworkbook.name but it gives me the error object required whenever it gets to the line with set wb=