improving workbook activate

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?

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
>

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

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

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
>

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

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

Why?

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

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