Hi, i'm writing a very long procedure that will automate a long manual
task. The task involves copying and pasting raw data from many
different workbooks into other workbooks. I started the code from
scratch and because of my limited excel vba knowledge have used the
method of declaring a workbook variable as either a name or
active.workbook and then using something like wb1.activate to point at
correct book.

As my code grows ive noticed im using this statement a considerible
amount of times and was wondering if there was another method i should
be made aware of before its to late to change?


--
cereldine
------------------------------------------------------------------------
cereldine's Profile: http://www.excelforum.com/member.php...o&userid=32069
View this thread: http://www.excelforum.com/showthread...hreadid=538000

There is rarely a need to activate anything. When you open a workbook, do it
something like

Set oWb = workbooks.open(Filename:="test.xls")

this gives you a reference to that workbook which you use like

oWb.Worksheets("Sheet1").Range("A1").Value = "abc"


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"cereldine" wrote
in message ...

Hi, i'm writing a very long procedure that will automate a long manual
task. The task involves copying and pasting raw data from many
different workbooks into other workbooks. I started the code from
scratch and because of my limited excel vba knowledge have used the
method of declaring a workbook variable as either a name or
active.workbook and then using something like wb1.activate to point at
correct book.

As my code grows ive noticed im using this statement a considerible
amount of times and was wondering if there was another method i should
be made aware of before its to late to change?


--
cereldine
------------------------------------------------------------------------
cereldine's Profile:
http://www.excelforum.com/member.php...o&userid=32069
View this thread: http://www.excelforum.com/showthread...hreadid=538000

I've tried experimenting with the code but are not having much joy,
im trying to find the value in workbook two, copy the range go back to
workbook 1 and paste it then find the next value to paste, the loop
concened with this is

Set dCell = bk2.Worksheets("i_calcs").Cells.Find(sCode)

''bk2.Worksheets("i_calcs").Range(dCell).Offset(1,
0).Select
Range(Selection, dCell.End(xlDown)).Copy

bk1.Activate
rng.Offset(0, 1).Select
Selection.PasteSpecial Paste:=xlValues,
Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True


Set rng = rng.Offset(1, 0)
Application.CutCopyMode = False

Loop Until rng = ""

the bit in pink produces the error, can you see what is wrong with it,
normally i would use something like
wb1.activate
sheets("i_calc").activate
Range(dCell).Offset(1, 0).Select

this is what i'm trying to get away from, thanks


--
cereldine
------------------------------------------------------------------------
cereldine's Profile: http://www.excelforum.com/member.php...o&userid=32069
View this thread: http://www.excelforum.com/showthread...hreadid=538000

hi can
I would like to use the following statement but it does not work!!

bk2.Worksheets("i_calcs").Range(dCell).Offset(1, 0).Select

dcell is a range that i have already declared, if i change it to th
following then it works (but is not what i need), what is it that
need to change? the above brings up error message 438

bk2.Worksheets("i_calcs").Range("a1").Offset(1, 0).Selec

--
cereldin
-----------------------------------------------------------------------
cereldine's Profile: http://www.excelforum.com/member.php...fo&userid=3206
View this thread: http://www.excelforum.com/showthread.php?threadid=53800

If dCell is really a range, all you need is

dCell.Offset(1, 0).Select


--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

"cereldine" wrote
in message ...

hi can
I would like to use the following statement but it does not work!!

bk2.Worksheets("i_calcs").Range(dCell).Offset(1, 0).Select

dcell is a range that i have already declared, if i change it to the
following then it works (but is not what i need), what is it that i
need to change? the above brings up error message 438

bk2.Worksheets("i_calcs").Range("a1").Offset(1, 0).Select


--
cereldine
------------------------------------------------------------------------
cereldine's Profile:
http://www.excelforum.com/member.php...o&userid=32069
View this thread: http://www.excelforum.com/showthread...hreadid=538000

Thats what i thought, i guess i'm not declaring the range properly, when
debugging the range variables (rng,dcell) they have text values, is this
correct, it hasn't been a problem before when i make the particular
workbook activated just before the offset code. This is currently the
method that i'm using, it works but you may be able to see what i need
to change to make it more efficent.

Range("B2").Select ' look at first value to search for
Set rng = ActiveCell 'set the procedure up to read first
value in B2
Set bk2 = Workbooks.Open(filepath1)

Do
If rng = "GVA" Then
sCode = "GDPM_1622"
Else
sCode = rng.Text
End If

Set dCell =
bk2.Worksheets("i_calcs").Cells.Find(sCode)
bk2.Worksheets("i_calcs").Activate

dCell.Offset(1, 0).Select

Range(Selection, dCell.End(xlDown)).Copy

bk1.Activate ''change eventually

rng.Offset(0, 1).Select

Selection.PasteSpecial Paste:=xlValues,
Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True

Set rng = rng.Offset(1, 0)
Application.CutCopyMode = False

Loop Until rng = ""
bk2.Close
End Sub


--
cereldine
------------------------------------------------------------------------
cereldine's Profile: http://www.excelforum.com/member.php...o&userid=32069
View this thread: http://www.excelforum.com/showthread...hreadid=538000

That code works fine for me as long as the value is found. If it is not,
then dCell takes a value of Nothing, so when you try to offset that, it will
fail.

You need to test that Find worked, and take appropriate action.

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

"cereldine" wrote
in message ...

Thats what i thought, i guess i'm not declaring the range properly, when
debugging the range variables (rng,dcell) they have text values, is this
correct, it hasn't been a problem before when i make the particular
workbook activated just before the offset code. This is currently the
method that i'm using, it works but you may be able to see what i need
to change to make it more efficent.

Range("B2").Select ' look at first value to search for
Set rng = ActiveCell 'set the procedure up to read first
value in B2
Set bk2 = Workbooks.Open(filepath1)

Do
If rng = "GVA" Then
sCode = "GDPM_1622"
Else
sCode = rng.Text
End If

Set dCell =
bk2.Worksheets("i_calcs").Cells.Find(sCode)
bk2.Worksheets("i_calcs").Activate

dCell.Offset(1, 0).Select

Range(Selection, dCell.End(xlDown)).Copy

bk1.Activate ''change eventually

rng.Offset(0, 1).Select

Selection.PasteSpecial Paste:=xlValues,
Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True

Set rng = rng.Offset(1, 0)
Application.CutCopyMode = False

Loop Until rng = ""
bk2.Close
End Sub


--
cereldine
------------------------------------------------------------------------
cereldine's Profile:
http://www.excelforum.com/member.php...o&userid=32069
View this thread: http://www.excelforum.com/showthread...hreadid=538000

this is the code that i have been using in seperate worksheets, going
back to my original posting, i'm building a procedure that is going to
combine about 15 or so of these find/copy/paste procedures. That is why
i was interested in trying something different to the workbook.activate
etc, In the grand scheme of things although its going to work, its
going to become very messy and will be difficult to make ammendments to
once it is up and running!


--
cereldine
------------------------------------------------------------------------
cereldine's Profile: http://www.excelforum.com/member.php...o&userid=32069
View this thread: http://www.excelforum.com/showthread...hreadid=538000

Why?

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

"cereldine" wrote
in message ...

this is the code that i have been using in seperate worksheets, going
back to my original posting, i'm building a procedure that is going to
combine about 15 or so of these find/copy/paste procedures. That is why
i was interested in trying something different to the workbook.activate
etc, In the grand scheme of things although its going to work, its
going to become very messy and will be difficult to make ammendments to
once it is up and running!


--
cereldine
------------------------------------------------------------------------
cereldine's Profile:
http://www.excelforum.com/member.php...o&userid=32069
View this thread: http://www.excelforum.com/showthread...hreadid=538000