Help with VBA code to copy/paste into another workbook then email

[bh24524]

Hi, I am trying to implement some code in my template sheet. This sheet gets weekly updated department hours and totals and those figures are then copied into another workbook. I'm trying to automate this a little more but I was struggling some with the coding. Here are a couple illustrations of the sheets in question.

From the template workbook:

From the weekly totals workbook:

Each week we create a new tab in this weekly totals sheet named the previous week's ending date. We then go to the template sheet and paste just those totals pictured into the weekly sheet(not the position listings) on that newly created date tab. We then hit the button on the weekly sheet to email the weekly sheet off. I'm really looking to combine that entire process all in one macro, even the email portion if possible. I tried my hand at some coding and some of it was successful until I got to the portion where I have to paste into the weekly sheet.

Can I get some help with this for what code could be used? This is what I tried:

Sub Export_Weekly_Totals()
    Dim curDate As Date
    curDate = DateValue([I1])
    
    Workbooks.Open "X:\Group\Payroll\SHARED\WEEKLY PAYROLL REPORTING\Weekly Dept. Totals for GM.xlsm"
    ActiveSheet.Copy Before:=ActiveSheet
    ActiveSheet.Range("A1").CurrentRegion.ClearContents
    Workbooks("C:\Group\Payroll\SHARED\WEEKLY PAYROLL REPORTING\Cost Center Template.xlsm").Worksheets("Dept. Totals").Range("A1").CurrentRegion.Copy
    Workbooks("C:\Group\Payroll\SHARED\WEEKLY PAYROLL REPORTING\Weekly Dept. Totals for GM.xlsm").Worksheets(ActiveSheet).Paste
    Worksheets("Dept. Totals").Name = Format([I1], "mmddyy")
        
    
End Sub

I'm not really familiar with using macros where more than one workbook is involved but thought I'd try it out. I did declare the variable for the date and have a hidden date in I1. This date is basically the end date of the previous week and that's what I want the new tab on the weekly sheet to be named. If there is some kind of coding that would make having a date in that cell unnecessary, I would be all for it instead. It just would nee to follow the format of the other tabs is all. The macro I have set up in the weekly sheets is called Email_GM. Would it just be a matter of calling that macro in this coding to get it to email or is there something better to be used?

 

[bh24524]

I figured most of out. I used the following code:

Sub Export_Weekly_Totals()
    Dim Wkly As Workbook
       
    Workbooks.Open "X:\Group\Payroll\SHARED\WEEKLY PAYROLL REPORTING\Weekly Dept. Totals for GM.xlsm"
    ActiveSheet.Copy Before:=ActiveSheet
    ActiveSheet.Range("A1").CurrentRegion.ClearContents
    ThisWorkbook.Activate
    Worksheets("Dept. Totals").Range("A1").CurrentRegion.Copy
    Set Wkly = Workbooks.Open("C:\Group\Payroll\SHARED\WEEKLY PAYROLL REPORTING\Weekly Dept. Totals for GM.xlsm")
    Range("A1").PasteSpecial
    ThisWorkbook.Activate
    Worksheets("Dept. Totals").Range("I1").Copy
    Set Wkly = Workbooks.Open("C:\Group\Payroll\SHARED\WEEKLY PAYROLL REPORTING\Weekly Dept. Totals for GM.xlsm")
    Range("I1").PasteSpecial
   
    ActiveSheet.Name = Format([I1], "mmddyy")
    ActiveWorkbook.Save   
   
End Sub

I'm just not sure how to get it to run the email macro from the weekly totals workbook as part of this macro. Isn't there a way to do it without having to paste its code in this macro? I could certainly do that but it got me curious, I feel like that should be possible?