Message Alert Box in Excel 2003

135 pts.
Tags:
Excel 2003
Excel 2003 functions
Excel 2003 macros
Excel macros
I have a two files, one is the consolidated time monitoring and the other file is the individual time monitoring.

The individual time monitoring file has 30 sheets which is the name of each agent and in shared mode. On that file, agents are responsible to provide/indicate the things that they've done everyday...for the whole week. The total time should not be less that 40:00 hrs (8hrs x 5 days). Each sheet ha a formula in "B2" that sums all time they entered in column J.

The Consolidated time monitoring, is a file wherein all data in Individual Time Monitoring is placed in 1 sheet. This file has a macro that I run to copypaste all data in each sheet of Individual Time Monitoring. My proble is, I am having a hard time with the total number of hours because I have to check each sheet if its already 40hrs or more. My question is, how can i add a macro where in a message alert will pop-up and show that a certain sheet/s' total time is not yet 40hrs and if possible to give the name of the sheet where the total time in "B2" is not yet 40 hrs.

Here is the macro that I run in the Consolidated Time Monitoring File.

    ChDir "P:MY REPORTDecemberDecember 20 - 25"     Workbooks.Open Filename:= _         "P:MY REPORTDecemberDecember 20 - 25December 20 - 25  Data Team Utilization Report.xls"     Sheets("Mon").Select     Range("A6:K1000").Select     Application.CutCopyMode = False     Selection.Copy     Windows("Consolidated - Utilization Report.xls").Activate     Range("A65536").End(xlUp).Select     ActiveCell.Offset(1, 0).Select     Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _         :=False, Transpose:=False     Windows("December 20 - 25  Data Team Utilization Report.xls").Activate     Sheets("Alma").Select     Range("A6:K1000").Select     Application.CutCopyMode = False     Selection.Copy     Windows("Consolidated - Utilization Report.xls").Activate     Range("A65536").End(xlUp).Select     ActiveCell.Offset(1, 0).Select     Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _         :=False, Transpose:=False     Windows("December 20 - 25  Data Team Utilization Report.xls").Activate     Sheets("Ivan").Select     Range("A6:K1000").Select     Application.CutCopyMode = False     Selection.Copy     Windows("Consolidated - Utilization Report.xls").Activate     Range("A65536").End(xlUp).Select     ActiveCell.Offset(1, 0).Select     Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _         :=False, Transpose:=False     Windows("December 20 - 25  Data Team Utilization Report.xls").Activate     Sheets("RC").Select     Range("A6:K1000").Select     Application.CutCopyMode = False     Selection.Copy     Windows("Consolidated - Utilization Report.xls").Activate     Range("A65536").End(xlUp).Select     ActiveCell.Offset(1, 0).Select     Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _         :=False, Transpose:=False     Windows("December 20 - 25  Data Team Utilization Report.xls").Activate     Sheets("Tala").Select     Range("A6:K1000").Select     Application.CutCopyMode = False     Selection.Copy     Windows("Consolidated - Utilization Report.xls").Activate     Range("A65536").End(xlUp).Select     ActiveCell.Offset(1, 0).Select     Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _         :=False, Transpose:=False     Windows("December 20 - 25  Data Team Utilization Report.xls").Activate     Sheets("Peter").Select     Range("A6:K1000").Select     Application.CutCopyMode = False     Selection.Copy     Windows("Consolidated - Utilization Report.xls").Activate     Range("A65536").End(xlUp).Select     ActiveCell.Offset(1, 0).Select     Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _         :=False, Transpose:=False     Windows("December 20 - 25  Data Team Utilization Report.xls").Activate     Sheets("Gerry").Select     Range("A6:K1000").Select     Application.CutCopyMode = False     Selection.Copy     Windows("Consolidated - Utilization Report.xls").Activate     Range("A65536").End(xlUp).Select     ActiveCell.Offset(1, 0).Select     Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _         :=False, Transpose:=False     Windows("December 20 - 25  Data Team Utilization Report.xls").Activate     Sheets("Zhaque").Select     Range("A6:K1000").Select     Application.CutCopyMode = False     Selection.Copy     Windows("Consolidated - Utilization Report.xls").Activate     Range("A65536").End(xlUp).Select     ActiveCell.Offset(1, 0).Select     Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _         :=False, Transpose:=False     Windows("December 20 - 25  Data Team Utilization Report.xls").Activate     Sheets("Rona").Select     Range("A6:K1000").Select     Application.CutCopyMode = False     Selection.Copy     Windows("Consolidated - Utilization Report.xls").Activate     Range("A65536").End(xlUp).Select     ActiveCell.Offset(1, 0).Select     Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _         :=False, Transpose:=False     Windows("December 20 - 25  Data Team Utilization Report.xls").Activate     Sheets("Ize").Select     Range("A6:K1000").Select     Application.CutCopyMode = False     Selection.Copy     Windows("Consolidated - Utilization Report.xls").Activate     Range("A65536").End(xlUp).Select     ActiveCell.Offset(1, 0).Select     Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _         :=False, Transpose:=False     Windows("December 20 - 25  Data Team Utilization Report.xls").Activate     Sheets("Dana").Select     Range("A6:K1000").Select     Application.CutCopyMode = False     Selection.Copy     Windows("Consolidated - Utilization Report.xls").Activate     Range("A65536").End(xlUp).Select     ActiveCell.Offset(1, 0).Select     Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _         :=False, Transpose:=False     Windows("December 20 - 25  Data Team Utilization Report.xls").Activate     Sheets("Jojie").Select     Range("A6:K1000").Select     Application.CutCopyMode = False     Selection.Copy     Windows("Consolidated - Utilization Report.xls").Activate     Range("A65536").End(xlUp).Select     ActiveCell.Offset(1, 0).Select     Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _         :=False, Transpose:=False     Windows("December 20 - 25  Data Team Utilization Report.xls").Activate     Sheets("Erwin").Select     Range("A6:K1000").Select     Application.CutCopyMode = False     Selection.Copy     Windows("Consolidated - Utilization Report.xls").Activate     Range("A65536").End(xlUp).Select     ActiveCell.Offset(1, 0).Select     Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _         :=False, Transpose:=False     Windows("December 20 - 25  Data Team Utilization Report.xls").Activate     Sheets("Nino").Select     Range("A6:K1000").Select     Application.CutCopyMode = False     Selection.Copy     Windows("Consolidated - Utilization Report.xls").Activate     Range("A65536").End(xlUp).Select     ActiveCell.Offset(1, 0).Select     Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _         :=False, Transpose:=False     Windows("December 20 - 25  Data Team Utilization Report.xls").Activate     Sheets("Anne").Select     Range("A6:K1000").Select     Application.CutCopyMode = False     Selection.Copy     Windows("Consolidated - Utilization Report.xls").Activate     Range("A65536").End(xlUp).Select     ActiveCell.Offset(1, 0).Select     Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _         :=False, Transpose:=False     Windows("December 20 - 25  Data Team Utilization Report.xls").Activate     Sheets("Heizel").Select     Range("A6:K1000").Select     Application.CutCopyMode = False     Selection.Copy     Windows("Consolidated - Utilization Report.xls").Activate     Range("A65536").End(xlUp).Select     ActiveCell.Offset(1, 0).Select     Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _         :=False, Transpose:=False     Windows("December 20 - 25  Data Team Utilization Report.xls").Activate     Sheets("Aaron").Select     Range("A6:K1000").Select     Application.CutCopyMode = False     Selection.Copy     Windows("Consolidated - Utilization Report.xls").Activate     Range("A65536").End(xlUp).Select     ActiveCell.Offset(1, 0).Select     Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _         :=False, Transpose:=False     Windows("December 20 - 25  Data Team Utilization Report.xls").Activate     Sheets("Delia").Select     Range("A6:K1000").Select     Application.CutCopyMode = False     Selection.Copy     Windows("Consolidated - Utilization Report.xls").Activate     Range("A65536").End(xlUp).Select     ActiveCell.Offset(1, 0).Select     Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _         :=False, Transpose:=False     Windows("December 20 - 25  Data Team Utilization Report.xls").Activate     Sheets("Edsel").Select     Range("A6:K1000").Select     Application.CutCopyMode = False     Selection.Copy     Windows("Consolidated - Utilization Report.xls").Activate     Range("A65536").End(xlUp).Select     ActiveCell.Offset(1, 0).Select     Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _         :=False, Transpose:=False     Windows("December 20 - 25  Data Team Utilization Report.xls").Activate     Sheets("Bless").Select     Range("A6:K1000").Select     Application.CutCopyMode = False     Selection.Copy     Windows("Consolidated - Utilization Report.xls").Activate     Range("A65536").End(xlUp).Select     ActiveCell.Offset(1, 0).Select     Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _         :=False, Transpose:=False     Windows("December 20 - 25  Data Team Utilization Report.xls").Activate     Sheets("Jaz").Select     Range("A6:K1000").Select     Application.CutCopyMode = False     Selection.Copy     Windows("Consolidated - Utilization Report.xls").Activate     Range("A65536").End(xlUp).Select     ActiveCell.Offset(1, 0).Select     Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _         :=False, Transpose:=False     Windows("December 20 - 25  Data Team Utilization Report.xls").Activate     Sheets("PJ").Select     Range("A6:K1000").Select     Application.CutCopyMode = False     Selection.Copy     Windows("Consolidated - Utilization Report.xls").Activate     Range("A65536").End(xlUp).Select     ActiveCell.Offset(1, 0).Select     Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _         :=False, Transpose:=False     Windows("December 20 - 25  Data Team Utilization Report.xls").Activate     Sheets("Bran").Select     Range("A6:K1000").Select     Application.CutCopyMode = False     Selection.Copy     Windows("Consolidated - Utilization Report.xls").Activate     Range("A65536").End(xlUp).Select     ActiveCell.Offset(1, 0).Select     Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _         :=False, Transpose:=False     Windows("December 20 - 25  Data Team Utilization Report.xls").Activate     Sheets("Richmon").Select     Range("A6:K1000").Select     Application.CutCopyMode = False     Selection.Copy     Windows("Consolidated - Utilization Report.xls").Activate     Range("A65536").End(xlUp).Select     ActiveCell.Offset(1, 0).Select     Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _         :=False, Transpose:=False     Windows("December 20 - 25  Data Team Utilization Report.xls").Activate     Sheets("Billy").Select     Range("A6:K1000").Select     Application.CutCopyMode = False     Selection.Copy     Windows("Consolidated - Utilization Report.xls").Activate     Range("A65536").End(xlUp).Select     ActiveCell.Offset(1, 0).Select     Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _         :=False, Transpose:=False     Windows("December 20 - 25  Data Team Utilization Report.xls").Activate     Sheets("Ryan").Select     Range("A6:K1000").Select     Application.CutCopyMode = False     Selection.Copy     Windows("Consolidated - Utilization Report.xls").Activate     Range("A65536").End(xlUp).Select     ActiveCell.Offset(1, 0).Select     Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _         :=False, Transpose:=False     Windows("December 20 - 25  Data Team Utilization Report.xls").Activate     Sheets("Cris").Select     Range("A6:K1000").Select     Application.CutCopyMode = False     Selection.Copy     Windows("Consolidated - Utilization Report.xls").Activate     Range("A65536").End(xlUp).Select     ActiveCell.Offset(1, 0).Select     Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _         :=False, Transpose:=False     Windows("December 20 - 25  Data Team Utilization Report.xls").Activate     Sheets("Dean").Select     Range("A6:K1000").Select     Application.CutCopyMode = False     Selection.Copy     Windows("Consolidated - Utilization Report.xls").Activate     Range("A65536").End(xlUp).Select     ActiveCell.Offset(1, 0).Select     Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _         :=False, Transpose:=False     Windows("December 20 - 25  Data Team Utilization Report.xls").Activate     Sheets("Irish").Select     Range("A6:K1000").Select     Application.CutCopyMode = False     Selection.Copy     Windows("Consolidated - Utilization Report.xls").Activate     Range("A65536").End(xlUp).Select     ActiveCell.Offset(1, 0).Select     Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _         :=False, Transpose:=False     Windows("December 20 - 25  Data Team Utilization Report.xls").Activate     Sheets("Patrick").Select     Range("A6:K1000").Select     Application.CutCopyMode = False     Selection.Copy     Windows("Consolidated - Utilization Report.xls").Activate     Range("A65536").End(xlUp).Select     ActiveCell.Offset(1, 0).Select     Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _         :=False, Transpose:=False     Windows("December 20 - 25  Data Team Utilization Report.xls").Activate     Sheets("Gem").Select     Range("A6:K1000").Select     Application.CutCopyMode = False     Selection.Copy     Windows("Consolidated - Utilization Report.xls").Activate     Range("A65536").End(xlUp).Select     ActiveCell.Offset(1, 0).Select     Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _         :=False, Transpose:=False     Windows("December 20 - 25  Data Team Utilization Report.xls").Activate     Sheets("Aileen").Select     Range("A6:K1000").Select     Application.CutCopyMode = False     Selection.Copy     Windows("Consolidated - Utilization Report.xls").Activate     Range("A65536").End(xlUp).Select     ActiveCell.Offset(1, 0).Select     Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _         :=False, Transpose:=False     Windows("December 20 - 25  Data Team Utilization Report.xls").Activate     Sheets("Carlo").Select     Range("A6:K1000").Select     Application.CutCopyMode = False     Selection.Copy     Windows("Consolidated - Utilization Report.xls").Activate     Range("A65536").End(xlUp).Select     ActiveCell.Offset(1, 0).Select     Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _         :=False, Transpose:=False

    Windows("December 20 - 25  Data Team Utilization Report.xls").Activate     Range("G6").Select     Application.CutCopyMode = False     Selection.Copy     Windows("Consolidated - Utilization Report.xls").Activate     Range("G2:G6000").Select     Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _         SkipBlanks:=False, Transpose:=False     Windows("December 20 - 25  Data Team Utilization Report.xls").Activate     Range("I6").Select     Application.CutCopyMode = False     Selection.Copy     Windows("Consolidated - Utilization Report.xls").Activate     Range("I2:I2030").Select     Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _         SkipBlanks:=False, Transpose:=False     Range("A2").Select

    End Sub



Software/Hardware used:
MS Excel

Answer Wiki

Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Discuss This Question: 2  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Eron09
    Sub Consolidate()
    '
    ' Consolidate Macro
    ' Eron
    '
    
    '
        ChDir "V:MY REPORTDecemberDecember 20 - 25"
        Workbooks.Open Filename:= _
            "V:MY REPORTDecemberDecember 20 - 25December 20 - 25  Data Team Utilization Report.xls"
        Sheets("Mon").Range("A6:K1000").Copy
        Windows("Consolidated - Utilization Report.xls").Activate
        Range("A65536").End(xlUp).Select
        ActiveCell.Offset(1, 0).Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        Workbooks("December 20 - 25  Data Team Utilization Report.xls") _
       .Sheets("Alma").Range("A6:K1000").Copy
        Windows("Consolidated - Utilization Report.xls").Activate
        Range("A65536").End(xlUp).Select
        ActiveCell.Offset(1, 0).Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        Workbooks("December 20 - 25  Data Team Utilization Report.xls") _
        .Sheets("Ivan").Range("A6:K1000").Copy
        Windows("Consolidated - Utilization Report.xls").Activate
        Range("A65536").End(xlUp).Select
        ActiveCell.Offset(1, 0).Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        Workbooks("December 20 - 25  Data Team Utilization Report.xls") _
        .Sheets("RC").Range("A6:K1000").Copy
        Windows("Consolidated - Utilization Report.xls").Activate
        Range("A65536").End(xlUp).Select
        ActiveCell.Offset(1, 0).Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        Workbooks("December 20 - 25  Data Team Utilization Report.xls") _
        .Sheets("Tala").Range("A6:K1000").Copy
        Windows("Consolidated - Utilization Report.xls").Activate
        Range("A65536").End(xlUp).Select
        ActiveCell.Offset(1, 0).Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        Workbooks("December 20 - 25  Data Team Utilization Report.xls") _
        .Sheets("Peter").Range("A6:K1000").Copy
        Windows("Consolidated - Utilization Report.xls").Activate
        Range("A65536").End(xlUp).Select
        ActiveCell.Offset(1, 0).Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        Workbooks("December 20 - 25  Data Team Utilization Report.xls") _
        .Sheets("Gerry").Range("A6:K1000").Copy
        Windows("Consolidated - Utilization Report.xls").Activate
        Range("A65536").End(xlUp).Select
        ActiveCell.Offset(1, 0).Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        Workbooks("December 20 - 25  Data Team Utilization Report.xls") _
        .Sheets("Zhaque").Range("A6:K1000").Copy
        Windows("Consolidated - Utilization Report.xls").Activate
        Range("A65536").End(xlUp).Select
        ActiveCell.Offset(1, 0).Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        Workbooks("December 20 - 25  Data Team Utilization Report.xls") _
        .Sheets("Rona").Range("A6:K1000").Copy
        Windows("Consolidated - Utilization Report.xls").Activate
        Range("A65536").End(xlUp).Select
        ActiveCell.Offset(1, 0).Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        Workbooks("December 20 - 25  Data Team Utilization Report.xls") _
        .Sheets("Ize").Range("A6:K1000").Copy
        Windows("Consolidated - Utilization Report.xls").Activate
        Range("A65536").End(xlUp).Select
        ActiveCell.Offset(1, 0).Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        Workbooks("December 20 - 25  Data Team Utilization Report.xls") _
        .Sheets("Dana").Range("A6:K1000").Copy
        Windows("Consolidated - Utilization Report.xls").Activate
        Range("A65536").End(xlUp).Select
        ActiveCell.Offset(1, 0).Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        Workbooks("December 20 - 25  Data Team Utilization Report.xls") _
        .Sheets("Jojie").Range("A6:K1000").Copy
        Windows("Consolidated - Utilization Report.xls").Activate
        Range("A65536").End(xlUp).Select
        ActiveCell.Offset(1, 0).Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        Workbooks("December 20 - 25  Data Team Utilization Report.xls") _
        .Sheets("Erwin").Range("A6:K1000").Copy
        Windows("Consolidated - Utilization Report.xls").Activate
        Range("A65536").End(xlUp).Select
        ActiveCell.Offset(1, 0).Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        Workbooks("December 20 - 25  Data Team Utilization Report.xls") _
        .Sheets("Nino").Range("A6:K1000").Copy
        Windows("Consolidated - Utilization Report.xls").Activate
        Range("A65536").End(xlUp).Select
        ActiveCell.Offset(1, 0).Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        Workbooks("December 20 - 25  Data Team Utilization Report.xls") _
        .Sheets("Anne").Range("A6:K1000").Copy
        Windows("Consolidated - Utilization Report.xls").Activate
        Range("A65536").End(xlUp).Select
        ActiveCell.Offset(1, 0).Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        Workbooks("December 20 - 25  Data Team Utilization Report.xls") _
        .Sheets("Heizel").Range("A6:K1000").Copy
        Windows("Consolidated - Utilization Report.xls").Activate
        Range("A65536").End(xlUp).Select
        ActiveCell.Offset(1, 0).Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        Workbooks("December 20 - 25  Data Team Utilization Report.xls") _
        .Sheets("Aaron").Range("A6:K1000").Copy
        Windows("Consolidated - Utilization Report.xls").Activate
        Range("A65536").End(xlUp).Select
        ActiveCell.Offset(1, 0).Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        Workbooks("December 20 - 25  Data Team Utilization Report.xls") _
        .Sheets("Delia").Range("A6:K1000").Copy
        Windows("Consolidated - Utilization Report.xls").Activate
        Range("A65536").End(xlUp).Select
        ActiveCell.Offset(1, 0).Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        Workbooks("December 20 - 25  Data Team Utilization Report.xls") _
        .Sheets("Edsel").Range("A6:K1000").Copy
        Windows("Consolidated - Utilization Report.xls").Activate
        Range("A65536").End(xlUp).Select
        ActiveCell.Offset(1, 0).Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        Workbooks("December 20 - 25  Data Team Utilization Report.xls") _
        .Sheets("Bless").Range("A6:K1000").Copy
        Windows("Consolidated - Utilization Report.xls").Activate
        Range("A65536").End(xlUp).Select
        ActiveCell.Offset(1, 0).Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        Workbooks("December 20 - 25  Data Team Utilization Report.xls") _
        .Sheets("Jaz").Range("A6:K1000").Copy
        Windows("Consolidated - Utilization Report.xls").Activate
        Range("A65536").End(xlUp).Select
        ActiveCell.Offset(1, 0).Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        Workbooks("December 20 - 25  Data Team Utilization Report.xls") _
        .Sheets("PJ").Range("A6:K1000").Copy
        Windows("Consolidated - Utilization Report.xls").Activate
        Range("A65536").End(xlUp).Select
        ActiveCell.Offset(1, 0).Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        Workbooks("December 20 - 25  Data Team Utilization Report.xls") _
        .Sheets("Bran").Range("A6:K1000").Copy
        Windows("Consolidated - Utilization Report.xls").Activate
        Range("A65536").End(xlUp).Select
        ActiveCell.Offset(1, 0).Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        Workbooks("December 20 - 25  Data Team Utilization Report.xls") _
        .Sheets("Richmon").Range("A6:K1000").Copy
        Windows("Consolidated - Utilization Report.xls").Activate
        Range("A65536").End(xlUp).Select
        ActiveCell.Offset(1, 0).Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        Workbooks("December 20 - 25  Data Team Utilization Report.xls") _
        .Sheets("Billy").Range("A6:K1000").Copy
        Windows("Consolidated - Utilization Report.xls").Activate
        Range("A65536").End(xlUp).Select
        ActiveCell.Offset(1, 0).Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        Workbooks("December 20 - 25  Data Team Utilization Report.xls") _
        .Sheets("Ryan").Range("A6:K1000").Copy
        Windows("Consolidated - Utilization Report.xls").Activate
        Range("A65536").End(xlUp).Select
        ActiveCell.Offset(1, 0).Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        Workbooks("December 20 - 25  Data Team Utilization Report.xls") _
        .Sheets("Cris").Range("A6:K1000").Copy
        Windows("Consolidated - Utilization Report.xls").Activate
        Range("A65536").End(xlUp).Select
        ActiveCell.Offset(1, 0).Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        Workbooks("December 20 - 25  Data Team Utilization Report.xls") _
        .Sheets("Dean").Range("A6:K1000").Copy
        Windows("Consolidated - Utilization Report.xls").Activate
        Range("A65536").End(xlUp).Select
        ActiveCell.Offset(1, 0).Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        Workbooks("December 20 - 25  Data Team Utilization Report.xls") _
        .Sheets("Irish").Range("A6:K1000").Copy
        Windows("Consolidated - Utilization Report.xls").Activate
        Range("A65536").End(xlUp).Select
        ActiveCell.Offset(1, 0).Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        Workbooks("December 20 - 25  Data Team Utilization Report.xls") _
        .Sheets("Patrick").Range("A6:K1000").Copy
        Windows("Consolidated - Utilization Report.xls").Activate
        Range("A65536").End(xlUp).Select
        ActiveCell.Offset(1, 0).Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        Workbooks("December 20 - 25  Data Team Utilization Report.xls") _
        .Sheets("Gem").Range("A6:K1000").Copy
        Windows("Consolidated - Utilization Report.xls").Activate
        Range("A65536").End(xlUp).Select
        ActiveCell.Offset(1, 0).Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        Workbooks("December 20 - 25  Data Team Utilization Report.xls") _
        .Sheets("Aileen").Range("A6:K1000").Copy
        Windows("Consolidated - Utilization Report.xls").Activate
        Range("A65536").End(xlUp).Select
        ActiveCell.Offset(1, 0).Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        Workbooks("December 20 - 25  Data Team Utilization Report.xls") _
        .Sheets("Carlo").Range("A6:K1000").Copy
        Windows("Consolidated - Utilization Report.xls").Activate
        Range("A65536").End(xlUp).Select
        ActiveCell.Offset(1, 0).Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
    
        Windows("December 20 - 25  Data Team Utilization Report.xls").Activate
        Range("G6").Select
        Application.CutCopyMode = False
        Selection.Copy
        Windows("Consolidated - Utilization Report.xls").Activate
        Range("G2:G6000").Select
        Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
            SkipBlanks:=False, Transpose:=False
        Windows("December 20 - 25  Data Team Utilization Report.xls").Activate
        Range("I6").Select
        Application.CutCopyMode = False
        Selection.Copy
        Windows("Consolidated - Utilization Report.xls").Activate
        Range("I2:I2030").Select
        Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
            SkipBlanks:=False, Transpose:=False
        Range("A2").Select
    
        
    End Sub
    135 pointsBadges:
    report
  • Chippy088
    That is quite a macro. You could make a consolidation sheet in the December 20 - 25 Data Team Utilization Report.xls and enter the following formula to check whether the total hours for each person are less than 40 hours ("Under hours"), over the 40 hours ("Over hours") or 40 hours, ("OK") =IF(A1<40,"Under hours",(IF(A1>40,"Over hours","OK"))) Change the A1 reference to point to the sum of total hours for the week cell. Once you have set the sheet up, refreshing (F9) will pull all the totals from each sheet and the formula will flag the cell status. Then you could write the macro to check the status cells and do any action you want. Advantages to doing it this way are 1. Easier adding new names, just add the row and the formula in the consolidation sheet, after you've create the new sheet. Same for deletion, delete sheet and then the corresponding row. 2. You do not have to trawl through lines of macro instructions to add or delete names, you just have to work on 1 sheet. And the paste link function will work between sheets as well. 3. If you still want to use a macro to populate the "Consolidated - Utilization Report.xls", then you could cut and paste the whole table, that should cut down the amount of times you have to switch between sheets doing it with your macro. Hope this helps.
    4,625 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following