A Question on Ron de Bruin's "Copy a range from closed workbook" function

J

JonWestcot

Hi all:

I've got a question regarding Ron de Bruin's function "GetRange" in the
"Copy a range from closed workbook" examples at the following link:

http://www.rondebruin.nl/copy7.htm

In the GetRange function, a statement attempts to resize a destination
range with the range of the source range. However, at the point it tries to
do this, the source range hasn't been identified. The source file isn't
even opened.

Any suggestions on how to get around this problem?

Thanks!

Jon

P.S. Sorry if this is a repost; I sent this out hours ago and it hasn't
shown up yet, so I thought I'd err on the side of caution and resend it.
 
R

royUK

Have you tried using the code and received an error? If so what error
Have you used Ron's example codes whilst changing the relevan
variables

--
royU

Hope that helps, RoyUK
For tips & examples visit my 'web site
' (http://www.excel-it.com
 
R

Ron de Bruin

Hi Jon

The Source and destination are in this code line

GetRange "C:\Data", "test3.xls", "Sheet1", "A1:B100", _
Sheets("Sheet1").Range("A1")

1: Path Source file
2: Source File name
3: Source sheet name
4: Source range
5: Destination sheet/range
 
J

JonWestcot

Hi Roy, Ron, et al.:
Have you tried using the code and received an error? If so what error?
Have you used Ron's example codes whilst changing the relevant
variables?

Yes, I've tried the code. The error I get indicates that the Range
fails on the _Global object.

However, I believe I figured out part of my problem. I was specifying a
named range from the source file when I really needed to specify an actual
range. Once I did that, the aforementioned error went away.

But, now I have two more problems! :(

First, I am trying to copy a range of 8 rows, 1 column wide. The
values, in order, are:

A, B, C, , , , , X

However, when they paste, a 0 (zero) replaces the blanks. And, try as I
might, I cannot get blanks to be pasted in.

Second, I have two situations where I need to copy the entire contents
of one closed worksheet into a sheet in my open workbook. Is there a way to
specify a range generically that will let me do this?

Thanks!

Jon
 
J

JonWestcot

Hi Ron:

Thanks for the uber-fast reply! And thanks for the link. I'll try
switching things around to opening up the external files manually and see if
that helps.

Do you have any idea, though, why the blank cells were replaces with
zeroes?

Thanks again.

Jon
 
R

Ron de Bruin

We create formula links in this example first and then convert it to values.
That's why the zero.

You can hide zeros if you want
Tools>Options..View
See zero values
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top