Copy Sheet from Open Workbook to Closed Excel Template.

Kayslover

Board Regular
Joined
Sep 22, 2020
Messages
138
Office Version
  1. 2013
Platform
  1. Windows
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
VBA Code:
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.
 

Some videos you may like

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

Kayslover

Board Regular
Joined
Sep 22, 2020
Messages
138
Office Version
  1. 2013
Platform
  1. Windows
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 "
    VBA Code:
    , 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
    VBA Code:
    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
    VBA Code:
    fileFormat:=xlOpenXMLTemplateMacroEnabled
    appended to it.
Hope the above may help someone in the future.
 
Solution

Watch MrExcel Video

Forum statistics

Threads
1,127,848
Messages
5,627,244
Members
416,233
Latest member
Riddlemethis

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top