Problem copying a range to another file
 

Hi,

This small test macro does not work.
I am trying to copy a range variable to another file.
I cannot figure out why.

Can somebody help?

Sub TestCopy()
' I am working in Windows("1.xls")
Dim PipoRecord As Range
Set PipoRecord = Range("A1:H1")
Windows("2.xls").Activate
Range("A1").Select
Range("PipoRecord").Copy Destination:=ActiveCell
End Sub

Thanks in advance
Bob

Problem copying a range to another file
 

This small test macro does not work.
I am trying to copy a range variable to another file.
I cannot figure out why.

Can somebody help?

Sub TestCopy()
' I am working in Windows("1.xls")
Dim PipoRecord As Range
Set PipoRecord = Range("A1:H1")
Windows("2.xls").Activate
Range("A1").Select


*workbooks(\"1xls\").activesheet.range(\"piporecor d\").copy
workbooks(\"2.xls\").activesheet.range(\"a1\" ).pastespecia
xlpastevalues
*
Here is some more code that might hel

--
davesexce
-----------------------------------------------------------------------
davesexcel's Profile: http://www.excelforum.com/member.php...fo&userid=3170
View this thread: http://www.excelforum.com/showthread.php?threadid=52407

Problem copying a range to another file
 

There is no need to put \ in front of your quotes as DavesExcel shows.

For the code you show:

Sub TestCopy()
' I am working in Windows("1.xls")
Dim PipoRecord As Range
Set PipoRecord = Range("A1:H1")
Windows("2.xls").Activate
Range("A1").Select
PipoRecord.Copy Destination:=ActiveCell
End Sub

PipoRecord is a range reference, so you don't need to use it as a string
argument to range.

Shorter would be:

Sub TestCopy()
Activesheet.Range("A1:H!").Copy Destination:= _
Workbooks("2.xls").Worksheets(1).Range("A1")
End Sub

You can replace the 1 in Worksheets to a specific sheet name

--
Regards,
Tom Ogilvy



"Bob Lehrer" wrote in message
...
Hi,

This small test macro does not work.
I am trying to copy a range variable to another file.
I cannot figure out why.

Can somebody help?

Sub TestCopy()
' I am working in Windows("1.xls")
Dim PipoRecord As Range
Set PipoRecord = Range("A1:H1")
Windows("2.xls").Activate
Range("A1").Select
Range("PipoRecord").Copy Destination:=ActiveCell
End Sub

Thanks in advance
Bob

Problem copying a range to another file
 

Tom:

On a side note,,
I see the use of
Windows("2.xls").Activate etc ' And Windows("1.xls").Activate
and I've always wondered what's the difference in this and
Workbooks("2.xls").Activate and Workbooks("1.xls").Activate ???

Tia,

Jim May


"Tom Ogilvy" wrote in message
...
There is no need to put \ in front of your quotes as DavesExcel shows.

For the code you show:

Sub TestCopy()
' I am working in Windows("1.xls")
Dim PipoRecord As Range
Set PipoRecord = Range("A1:H1")
Windows("2.xls").Activate
Range("A1").Select
PipoRecord.Copy Destination:=ActiveCell
End Sub

PipoRecord is a range reference, so you don't need to use it as a string
argument to range.

Shorter would be:

Sub TestCopy()
Activesheet.Range("A1:H!").Copy Destination:= _
Workbooks("2.xls").Worksheets(1).Range("A1")
End Sub

You can replace the 1 in Worksheets to a specific sheet name

--
Regards,
Tom Ogilvy



"Bob Lehrer" wrote in message
...
Hi,

This small test macro does not work.
I am trying to copy a range variable to another file.
I cannot figure out why.

Can somebody help?

Sub TestCopy()
' I am working in Windows("1.xls")
Dim PipoRecord As Range
Set PipoRecord = Range("A1:H1")
Windows("2.xls").Activate
Range("A1").Select
Range("PipoRecord").Copy Destination:=ActiveCell
End Sub

Thanks in advance
Bob

Problem copying a range to another file
 

There is not difference in terms of results.

On works with the windows collection and one the workbooks collection. But
from the immediate window:

? activewindow.Parent.name
Book2
? activeworkbook.Parent.name
Microsoft Excel

? activeworkbook.Windows(1).Caption
Book2
? windows(1).Caption
Book2
? windows(1).Parent.name
Book2

? windows.Parent.name
Microsoft Excel
? activeworkbook.Windows.Parent.name
Book2

the application has a windows and a workbooks collection. That is basically
what you are describing.

the workbook itself has a windows collection (a workbook can have more than
one window).

In any event, the end result is the same if the workbook has only one window
open. If it has two (or more)
workbooks("2.xls").Activate
would go to the 2.xls:1 window
Windows("2.xls").Activate
would raise an error since it is now 2.xls:1

--
Regards,
Tom Ogilvy


--
Regards,
Tom Ogilvy


"Jim May" wrote in message
news:H1kTf.270781$oG.194128@dukeread02...
Tom:

On a side note,,
I see the use of
Windows("2.xls").Activate etc ' And Windows("1.xls").Activate
and I've always wondered what's the difference in this and
Workbooks("2.xls").Activate and Workbooks("1.xls").Activate ???

Tia,

Jim May


"Tom Ogilvy" wrote in message
...
There is no need to put \ in front of your quotes as DavesExcel shows.

For the code you show:

Sub TestCopy()
' I am working in Windows("1.xls")
Dim PipoRecord As Range
Set PipoRecord = Range("A1:H1")
Windows("2.xls").Activate
Range("A1").Select
PipoRecord.Copy Destination:=ActiveCell
End Sub

PipoRecord is a range reference, so you don't need to use it as a string
argument to range.

Shorter would be:

Sub TestCopy()
Activesheet.Range("A1:H!").Copy Destination:= _
Workbooks("2.xls").Worksheets(1).Range("A1")
End Sub

You can replace the 1 in Worksheets to a specific sheet name

--
Regards,
Tom Ogilvy



"Bob Lehrer" wrote in message
...
Hi,

This small test macro does not work.
I am trying to copy a range variable to another file.
I cannot figure out why.

Can somebody help?

Sub TestCopy()
' I am working in Windows("1.xls")
Dim PipoRecord As Range
Set PipoRecord = Range("A1:H1")
Windows("2.xls").Activate
Range("A1").Select
Range("PipoRecord").Copy Destination:=ActiveCell
End Sub

Thanks in advance
Bob

Problem copying a range to another file
 

WOW!!
Appreciate the in-depth comparison.
I need to spend more time in the immediate
window, as I see you demonstrate often.
Thanks again,
Jim May


"Tom Ogilvy" wrote in message
...
There is not difference in terms of results.

On works with the windows collection and one the workbooks collection.
But
from the immediate window:

? activewindow.Parent.name
Book2
? activeworkbook.Parent.name
Microsoft Excel

? activeworkbook.Windows(1).Caption
Book2
? windows(1).Caption
Book2
? windows(1).Parent.name
Book2

? windows.Parent.name
Microsoft Excel
? activeworkbook.Windows.Parent.name
Book2

the application has a windows and a workbooks collection. That is
basically
what you are describing.

the workbook itself has a windows collection (a workbook can have more
than
one window).

In any event, the end result is the same if the workbook has only one
window
open. If it has two (or more)
workbooks("2.xls").Activate
would go to the 2.xls:1 window
Windows("2.xls").Activate
would raise an error since it is now 2.xls:1

--
Regards,
Tom Ogilvy


--
Regards,
Tom Ogilvy


"Jim May" wrote in message
news:H1kTf.270781$oG.194128@dukeread02...
Tom:

On a side note,,
I see the use of
Windows("2.xls").Activate etc ' And Windows("1.xls").Activate
and I've always wondered what's the difference in this and
Workbooks("2.xls").Activate and Workbooks("1.xls").Activate ???

Tia,

Jim May


"Tom Ogilvy" wrote in message
...
There is no need to put \ in front of your quotes as DavesExcel shows.

For the code you show:

Sub TestCopy()
' I am working in Windows("1.xls")
Dim PipoRecord As Range
Set PipoRecord = Range("A1:H1")
Windows("2.xls").Activate
Range("A1").Select
PipoRecord.Copy Destination:=ActiveCell
End Sub

PipoRecord is a range reference, so you don't need to use it as a
string
argument to range.

Shorter would be:

Sub TestCopy()
Activesheet.Range("A1:H!").Copy Destination:= _
Workbooks("2.xls").Worksheets(1).Range("A1")
End Sub

You can replace the 1 in Worksheets to a specific sheet name

--
Regards,
Tom Ogilvy



"Bob Lehrer" wrote in message
...
Hi,

This small test macro does not work.
I am trying to copy a range variable to another file.
I cannot figure out why.

Can somebody help?

Sub TestCopy()
' I am working in Windows("1.xls")
Dim PipoRecord As Range
Set PipoRecord = Range("A1:H1")
Windows("2.xls").Activate
Range("A1").Select
Range("PipoRecord").Copy Destination:=ActiveCell
End Sub

Thanks in advance
Bob