For what you are trying to do you need to get you head around 3 different
types of objects. Workbook, Worksheet and Range. A workbook contains 1 or
more worksheets and a worksheet contains ranges. A range is a group of 1 or
more cells... In VBA we can Set (note the key work set since we are dealing
with objects) objects that will essentially point to the 3 different kinds of
objects. We can create as many objects as are necessary to do the voodoo we
want to do. Here is a very quick example...

Dim wbkDataFileOne as Workbook
Dim wbkReportFileOne as Workbook
dim wksFrom as worksheet
dim wksDestination as Worksheet
dim rngFrom as range
dim rngDestination as range

Set wbkDataFileOne = Workbook("dataOne.xls")
Set wbkReportFileOne = Workbook ("reportone.xls")
set wksfrom = wbkDataFileOne.sheets("Sheet1")
set wksDestination = wbkReportFileOne .sheets("Sheet1")
set rngfrom = wksfrom.range("A1:B2")
set rngDestination = wksdestination.range("A1")
rngfrom.copy Destination:=rngDestination
set rngfrom = wksfrom.Range("B3:C4")
rngfrom.copy
rngdestination.offset(5,5).pasteSpecial xlFormats

Note that we can repoint the objects as we go by SETting (pointing) them to
different books, sheets or ranges... By properly declaring your objects the
intellisense dropdown will function as you write your code so you will know
all of the appropriate preperties and methodes of each object...

--
HTH...

Jim Thomlinson


"NewBike" wrote:

Hi there -
I am trying to make some code more efficient.

Currently, I have a workbook that is opened via MS Scheduler, a
Workbook_Open Event starts a macro that opens approximately 7 report
workbooks and updates those with approx 16 data files.

According to what I have read, it is much more efficient to leave as many
files as possible inactive and just pull the data. However, I am at a loss
as to the proper way to focus on the inactive workbooks as I need them.

I have Dim'd them all as:

'data files
Dim df_One as string
Set df_One = Workbook("dataOne.xls")
'onward til all are named

'report files
Dim rpt_One as String
Set rpt_One = Workbook ("reportone.xls")
'and so on

Now lets say I want to work with data from df_five.range(c1:ba1009) copy it
to rpt_six, then get data from df_two.range (a2:c1010) and copy it to rpt_six.

What is the most efficient way to accomplish this?