Adding Sheet to active Workbook including Formulas

GioJoe · Aug 19, 2015

Hi, I am a rookie and just started with vba for Excel and I hope somebody can help me with my question(s).
I would like to import a Sheet "Work (ORG)" from a template workbook named „Template.xltx“ into the current active Workbook that contains a Sheet called "Work". The Code works more or less but I fail to close the Template with error code 438. The strange thing is that each time I open the Template.xltx with the script it adds a number at end of the filename eg. Template1.xltx – Template2.xltx – Template3.xltx etc..

The second step would be to import all the formulas (not the values) from the Template.xltx sheet "Work (ORG)" but reffering to the sheets in the Active Workbook. Eg. instead of =[Template]Work!A13 it should be =Work!A13

Can somebody help me to fix the code I have so far please ?

Code:

Sub CopySheet()

Dim wkbSource As Workbook
Dim activeWB As Workbook
Dim shtToCopy As Worksheet

Set activeWB = Application.ActiveWorkbook
Set wkbSource = Workbooks.Open("C:\Users\UserName\AppData\Roaming\Microsoft\Templates\Template.xltx")
Set shtToCopy = wkbSource.Sheets("Work (ORG)")
 
  activeWB.Activate
  shtToCopy.Copy After:=activeWB.Sheets("Work")
  Sheets("Work (ORG)").Select
  Range("A13:X1300").Select
  Selection.Copy
  activeWB.Activate
  Range("A13").Select
  Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
  SkipBlanks:=False, Transpose:=False
  wkbSource("Template.xltx").Close savechanges:=False

Application.ScreenUpdating = True
Application.DisplayAlerts = False

End Sub

GioJoe · Aug 23, 2015

Hi I've managed to solve the issues I have had by myself.
Thread can be closed.
Thanks vm

Adding Sheet to active Workbook including Formulas

GioJoe

New Member

Excel Facts

GioJoe

New Member

Similar threads

Forum statistics

Share this page

Adding Sheet to active Workbook including Formulas

GioJoe

New Member

Excel Facts

GioJoe

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock