improving workbook activate

[cereldine]

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?

[Bob Phillips]

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" <[email protected]> wrote
in message news:[email protected]...
>
> 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
>

[cereldine]

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]

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

[Bob Phillips]

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" <[email protected]> wrote
in message news:[email protected]...
>
> 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
>

[cereldine]

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