Message Alert Window

Microsoft Office excel 2003
December 20, 2010 at 07:45:29
Specs: Windows XP
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 the file is in shared mode so that all agents can access the file all at the same time. 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 than 40:00 hrs (8hrs x 5 days). Each sheet has 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 problem 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


See More: Message Alert Window

Report •

#1
December 20, 2010 at 08:25:12
Please read the How To referenced below and repost your macro.

It's impossible to read as posted and we can't be expected to try and reconstruct it.

I will add one other point:

I can see a lot of lines that look like this:

 Windows("December 20 - 25 Data Team Utilization Report.xls").Activate
  Sheets("Cris").Select
  Range("A6:K1000").Select
  Application.CutCopyMode = False
  Selection.Copy

Rarely do you have to activate an object or select a range to perform an action on it. It is very inefficient to do so.

Those 5 lines can probably be reduced to a single line:

  Workbooks("December 20 - 25 Data Team Utilization Report.xls") _
   .Sheets("Cris").Range("A6:K1000").Copy

Before you repost your code, you should clean up your code and get rid of all the object.Select...Selection.operation combinations.

That will not only make your code a lot more efficient but it will also make it a lot easier for us to read.

If it's easier for us to read, we are more likely to want to help you with your question.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
December 21, 2010 at 00:34:23
Sorry about that and thanks for the tip! Here you go!

Sub Consolidate()
'
' Consolidate Macro
' Eron
'

'
    ChDir "V:\MY REPORT\December\December 20 - 25"
    Workbooks.Open Filename:= _
        "V:\MY REPORT\December\December 20 - 25\December 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


Report •

#3
December 21, 2010 at 04:02:30
Why are you still selecting all of the ranges before copying and pasting them?

Did you read my first post in it's entirety?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •
Related Solutions


Ask Question