MisterProzilla
Active Member
- Joined
- Nov 12, 2015
- Messages
- 264
Hi there,
The code segment below is part of a larger sub which creates a copy of a template workbook and renames it under a staff name. The only way I've found to make it work on Sharepoint is by opening the Template workbook first, Saving As under a different filename, then Closing.
The problem is, when I get to the bit of code afterwards (line in bold) it's returning an error. It runs the Copy line okay but not the range.select, so I think it must still be referring to the new workbook which is now closed. I've tried explicitly activating ThisWorkbook to try and return focus, but that does nothing. I also have the problem that I can't click the Close button on the worksheet after the code runs, which I think is something to do with the new workbook object still being held in memory and needs to be released. I tried Set TemplateWLP = Nothing to see if that released it, but no luck.
Does anyone have any idea what I'm doing wrong? I've learned VBA by trial and error so there's probably something fundamental that I'm missing - do I need to add something to the brackets in the Sub title line to make the object handling work properly?
Any help would be appreciated
The code segment below is part of a larger sub which creates a copy of a template workbook and renames it under a staff name. The only way I've found to make it work on Sharepoint is by opening the Template workbook first, Saving As under a different filename, then Closing.
The problem is, when I get to the bit of code afterwards (line in bold) it's returning an error. It runs the Copy line okay but not the range.select, so I think it must still be referring to the new workbook which is now closed. I've tried explicitly activating ThisWorkbook to try and return focus, but that does nothing. I also have the problem that I can't click the Close button on the worksheet after the code runs, which I think is something to do with the new workbook object still being held in memory and needs to be released. I tried Set TemplateWLP = Nothing to see if that released it, but no luck.
Does anyone have any idea what I'm doing wrong? I've learned VBA by trial and error so there's probably something fundamental that I'm missing - do I need to add something to the brackets in the Sub title line to make the object handling work properly?
Any help would be appreciated
Code:
Sub CopyScript()
...
'Open Template, Save As staff name and Close
Dim TemplateWLP As Workbook
Set TemplateWLP = Workbooks.Open(Filename:=Sheet3.Range("D54").Value)
TemplateWLP.Sheets("Schedule of Work").Range("A3").Value = StaffName
TemplateWLP.SaveAs Filename:=Sheet3.Range("D56").Value
TemplateWLP.Close
Set TemplateWLP = Nothing
'Refresh links to template, as Save As will have shifted references to the new workbook
Call TemplateLinkProcess
'set up links to new workbook by copying template links and replacing
ThisWorkbook.Activate
Sheet2.Range("R4:W9").Copy
[B]Sheet2.Cells(4, stcol).Select[/B]
ActiveSheet.Paste
'replace Template text with staff name text
Selection.Replace what:="Template", replacement:=Cells(3, 2), Lookat:= _
xlPart, Searchorder:=xlByRows, MatchCase:=False, searchformat:=False, _
ReplaceFormat:=False