Copying A Value From Another Excel Workbook

[GovNewbi]

in this piece of my code...
Set wb = Workbooks.Open("Q:....xlsm")
wb.Worksheets("MasterList").Range("I" & (Range("StProj").Offset(j, 0).Value) + 1).Copy
There is something not right.

The first line (Open) works fine, but once I get to the second line I get error 1004: range of object failed. I am pretty sure I know why this is happening but I don't know how to fix it.

Range("StProj") is in the first workbook, not the the one I am opening. So when I open the Q drive workbook it can't find this StProj range. To get around this I have been trying to set Range("StProj").Offset(j, 0).Value as a variable but I cannot seem to dimension it so that the code is happy.

Help!

 

[PHV]

What about this ?
Dim myRow
myRow = Range("StProj").Offset(j, 0).Value + 1
Set wb = Workbooks.Open("Q:....xlsm")
wb.Worksheets("MasterList").Range("I" & myRow).Copy

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[xlbo]

Set wb = Workbooks.Open("Q:....xlsm")
wb.Worksheets("MasterList").Range("I" & Thisworkbook.Range("StProj").Offset(j, 0).Value + 1).Copy


Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[GovNewbi]

PHV: That worked!!!... I tried something similar but used
Set myRow = Range("StProj").Offset(j, 0).Value + 1
This always gave me error 13: type miss match. How do you know when to use set and when not to?

 

[PHV]

The Set instruction is used to create a reference to an object.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[GovNewbi]

:| ... you might have to dumb it down a bit for me lol.

 

[xlbo]

object = range, worksheet, workbook, row etc etc

Non objects = data variables, numbers, strings, arrays etc

Dim rng as Range

Set rng = worksheets("TheSheet").Range("A1")

Dim rngVal as double

rngVal = worksheets("TheSheet").Range("A1").Value

Objects have properties that can eb referenced after your variable has been set so:

dim rngVal as double

dim rng as Range

Set rng = worksheets("TheSheet").Range("A1")

rngVal = rng.Value



Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question