Copy Sheet from Open Workbook to Closed Excel Template.

[Kayslover]

Hi All,

I have a need to copy sheet called Lookup from an Open workbook which is a macro enabled worksheet to a Macro Enabled Template called Buylist V2.xltm.

Both the Open workbook and the Closed Template are in the same folder. I use

fPath = ThisWorkbook.Path & "\"

to get the folder information.

There is already a sheet called Lookup in the Closed Template.

Sheet Lookup in the closed workbook should be replaced with the one from the Open workbook and the Template saved with the same filename (i.e. Buylist V2.xltm).

Please note that there are formulas in the closed workbook that use sheet called Lookup, and these formulas should not be affected.

 

[Kayslover]

This is a FYI and hopefully help others should they have the same problems as I did.

I couldn’t achieve what I wanted with a closed workbook.

This is what I have had to do:-

• Open the Template. The open statement had to have "

  , editable:=True

  " appended. This enables it to be opened so that you can edit it.
  
  
• Inserted the following ThisWorkbook.Sheets("Lookup").Copy Before:=Workbooks("Buylist V2.xltm").Sheets("VAT + or -"). This inserts sheet Lookup before sheet VAT + or – in the Open Template. However as Sheet Lookup already exists in the Template, it creates a new sheet called Lookup (2).
  
  
• Inserted code to delete Lookup and then rename the new Lookup (2) to Lookup.
  
  
• As I had formulas in Sheet called Buylist that referenced sheet called Lookup, all these returned #REF when sheet Lookup was deleted.
  
  
• Had to use

  Cells.Replace What:="#REF", Replacement:="Lookup", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False

  
  
• To replace all #REF with Lookup.
  
  
• Saved the Template with the .XLTM extension and

  fileFormat:=xlOpenXMLTemplateMacroEnabled

  appended to it.

Hope the above may help someone in the future.