Working with Incative Workbooks
 

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?

Working with Incative Workbooks
 

df_five.worksheets(1).range(c1:ba1009).copy _
rpt_six.worksheets(1).cells(rows.count,1).End(xlup )(2)

--
Regards,
Tom Ogilvy


"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?

Working with Incative Workbooks
 

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?

Working with Incative Workbooks
 

Hi Tom -
Thanks, but when I enter the following code:

rpt_SIX.Worksheet("EASTCST").Range("A4:k8000").Cle arContents

I get a Compile Error: Invalid qualifier

Sorry if I am being a ditz on this, I am on a VBA crash course right now LOL!!

"Tom Ogilvy" wrote:

df_five.worksheets(1).range(c1:ba1009).copy _
rpt_six.worksheets(1).cells(rows.count,1).End(xlup )(2)

--
Regards,
Tom Ogilvy


"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?

Working with Incative Workbooks
 

Thanks Jim.

So, I can redirect or reSET the properties along the way which is going to
be a huge help when selecting ranges!

With this particular code, I am declaring my workbooks in advance since the
macro bounces back and forth so much - that is ok isn't it? Then I only need
reset the ranges as I go.

But, I am still getting an error as noted in my response to Tom, and don't
understand why...or did I jst read the answer and totally miss it? LOL.

"Jim Thomlinson" wrote:

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?

Working with Incative Workbooks
 

Dim rpt_Six as Workbook
set rpt_Six = Workbooks("reportone.xls") ' not "s" in Workbooks
rpt_SIX.Worksheet("EASTCST").Range("A4:k8000").Cle arContents


would be my guess.

--
regards,
Tom Ogilvy


"NewBike" wrote in message
...
Hi Tom -
Thanks, but when I enter the following code:

rpt_SIX.Worksheet("EASTCST").Range("A4:k8000").Cle arContents

I get a Compile Error: Invalid qualifier

Sorry if I am being a ditz on this, I am on a VBA crash course right now
LOL!!

"Tom Ogilvy" wrote:

df_five.worksheets(1).range(c1:ba1009).copy _
rpt_six.worksheets(1).cells(rows.count,1).End(xlup )(2)

--
Regards,
Tom Ogilvy


"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?