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

  • Thread starter Thread starter JonWestcot
  • Start date Start date
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.
 
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
 
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
 
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
 
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
 
Back
Top