VBA - release workbook object?

[MisterProzilla]

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

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

 

[RoryA]

You can't select that range unless Sheet2 is active. However, you can reduce this:

Sheet2.Range("R4:W9").Copy
Sheet2.Cells(4, stcol).Select
ActiveSheet.Paste

to this:

Sheet2.Range("R4:W9").Copy Sheet2.Cells(4, stcol)

to avoid selecting at all.

 

[MisterProzilla]

You can't select that range unless Sheet2 is active

Well-spotted I've activated Sheet2 explicitly and it does now get past that line. It was still behaving oddly so I've just spent about an hour trying to figure out why. Turns out I had On error Resume Next running, so it was skipping over lines where the sheet was protected. Lesson learned

However, you can reduce this ..

That's true, but it's easier if the range is pre-selected when I run the Replace afterwards: if I run the code as you suggest, it'll copy without selecting any range, and then I'd need to define the range afterwards before Replacing; if I select a single cell first then Paste, the selected range resizes to match the copied source, so I can just run the Replace straight afterwards


Thanks very much for your help - all seems to be working now

 

[RoryA]

it's easier if the range is pre-selected when I run the Replace afterwards

The range you copy is a fixed size, so it would be easy enough to code that into the replace part without selecting anything.

 

[MisterProzilla]

How would I include that in the replace? Would it not just 6 and two 3s, or is there a reason it's a better way to go?

 

[RoryA]

Avoiding selecting is more efficient. Probably not a big deal for what you're doing though!

 

[MisterProzilla]

Avoiding selecting is more efficient. Probably not a big deal for what you're doing though!

No prob, I'll stick with what I've got until and if it breaks again Thanks again