Macro to link template to master workbook

sejie

New Member
Joined
Mar 24, 2018
Messages
2
Hi
I have a master workbook which contains sales teams monthly and annual data. I also have a template that is used for monthly sales. How do I set it up so that when I use the template to create a new month this will link to the master workbook?
Thanks
Sarah
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
.
Hopefully this will accomplish your need. Paste into a Routine Module :

Code:
Option Explicit


Sub CreateNew()
Dim wsRecipes As Worksheet, wsTEMP As Worksheet, wasVISIBLE As Boolean
Dim shNAMES As Range, Nm As Range
Dim i As Long
Dim wsIndex
On Error Resume Next




With ThisWorkbook                                               'keep focus in this workbook
Application.CopyObjectsWithCells = False
    Set wsTEMP = .Sheets("Template")                            'sheet to be copied
    wasVISIBLE = (wsTEMP.Visible = xlSheetVisible)              'check if it's hidden or not
    If Not wasVISIBLE Then wsTEMP.Visible = xlSheetVisible      'make it visible
      
    Application.ScreenUpdating = False                          'speed up macro


    wsTEMP.Copy After:=.Sheets(.Sheets.Count)                   '...create it from template
    
    wsIndex.Activate                                            'return to the master sheet
    If Not wasVISIBLE Then wsTEMP.Visible = xlSheetHidden       'hide the template if necessary
    Application.ScreenUpdating = True                           'update screen one time at the end


Application.CopyObjectsWithCells = True
End With
Sheets(Sheets.Count).Select


Dim szTodayDate As String
szTodayDate = Format(Date + 1, "mm-dd-yy")
ActiveSheet.Name = szTodayDate
MsgBox "New sheet created. "
End Sub

Anything you place on the TEMPLATE sheet will be in the newly created sheet. The last four lines of the macro code names the new sheet tab to be THE CURRENT DATE. You can edit that part of the code to make the tab name
whatever you want or you can manually change the tab name after creation.

If this is not what you were seeking, please re-describe your goal.
 
Upvote 0
Thanks, I think that is almost it however when I open the daily template and create a new month's data, I want the totals of that months data to be added to the master (rather than a whole sheet) if that makes sense? So my master looks like below- I want each column to then be populated by the new month's data from each new month's workbook:
JanuaryFebruaryMarchAprilMayJuneJulyAugustSeptemberOctoberNovemberDecemberANNUAL TOTAL
SalesMember100
SalesMember200
SalesMember300
SalesMember400
SalesMember500
TOTALS00

<colgroup><col><col span="12"><col></colgroup><tbody>
</tbody>
 
Upvote 0
.
I apologize for my inability to understand what you are attempting to do. It sounds like you want to copy existing figures from somewhere and insert them into the new Template Month Sheet.

Maybe this is just a bad time for me to be concentrating on this. So sorry.

Perhaps another Forum member will be willing to assist.
 
Upvote 0

Forum statistics

Threads
1,213,565
Messages
6,114,337
Members
448,568
Latest member
Honeymonster123

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
Back
Top