Copy from two files
 

Is is possible for a macro to copy a range of cells from one file to the same
range in another file? The two files would be open; but the file names may
change. The recorder records absolute ranges correctly, but hard codes the
file names. Thanks for any help.
Annie

Copy from two files
 

That is a little tricky from the standpoint of which sheet would you like to
copy to. What if there are multiple books open, then which book should it
copy to. The logic is really fuzzy making the code complex.
--
HTH...

Jim Thomlinson


"Annie" wrote:

Is is possible for a macro to copy a range of cells from one file to the same
range in another file? The two files would be open; but the file names may
change. The recorder records absolute ranges correctly, but hard codes the
file names. Thanks for any help.
Annie

Copy from two files
 

Is is possible for a macro to copy a range of cells from one file to the
same
range in another file?

Yes, but you have to have some way to know which workbook is which. How do
you know which is the source and which the destination?

--
Jim

Copy from two files
 

If the one to copy from is open, is there code to prompt and wait for the
destination file to open? Probably not. Thank you Jim.
Annie

"Jim Thomlinson" wrote:

That is a little tricky from the standpoint of which sheet would you like to
copy to. What if there are multiple books open, then which book should it
copy to. The logic is really fuzzy making the code complex.
--
HTH...

Jim Thomlinson


"Annie" wrote:

Is is possible for a macro to copy a range of cells from one file to the same
range in another file? The two files would be open; but the file names may
change. The recorder records absolute ranges correctly, but hard codes the
file names. Thanks for any help.
Annie

Copy from two files
 

Thank you Jim. I added an assumption to Jim Thomlinson's post, but do not
believe this is possible. Thank you for reply! Annie

"Jim Rech" wrote:

Is is possible for a macro to copy a range of cells from one file to the
same
range in another file?

Yes, but you have to have some way to know which workbook is which. How do
you know which is the source and which the destination?

--
Jim

Copy from two files
 

So long as you have a consistent destination workbook then you are good to
go. I can give you code to copy the selected range to an identical range on a
different sheet on that workbook. Let me know if that is what you want.
--
HTH...

Jim Thomlinson


"Annie" wrote:

If the one to copy from is open, is there code to prompt and wait for the
destination file to open? Probably not. Thank you Jim.
Annie

"Jim Thomlinson" wrote:

That is a little tricky from the standpoint of which sheet would you like to
copy to. What if there are multiple books open, then which book should it
copy to. The logic is really fuzzy making the code complex.
--
HTH...

Jim Thomlinson


"Annie" wrote:

Is is possible for a macro to copy a range of cells from one file to the same
range in another file? The two files would be open; but the file names may
change. The recorder records absolute ranges correctly, but hard codes the
file names. Thanks for any help.
Annie

Copy from two files
 

And even further, how do you know what worksheet to copy to?

If you want to prompt the user, you could...

Option Explicit
Sub testme()

Dim rngF As Range
Dim rngT As Range

Set rngF = Nothing
On Error Resume Next
Set rngF = Application.InputBox(Prompt:="Select the range", _
Default:=Selection.Areas(1).Address(external:=True ), _
Type:=8).Areas(1)
On Error GoTo 0

If rngF Is Nothing Then
MsgBox "Try later"
Exit Sub
End If

Set rngT = Nothing
On Error Resume Next
Set rngT = Application.InputBox(Prompt:="Select the range", Type:=8) _
.Cells(1)
On Error GoTo 0

If rngT Is Nothing Then
MsgBox "Try later"
Exit Sub
End If

rngF.Copy _
Destination:=rngT

Application.CutCopyMode = False

End Sub


You can swap between workbooks by clicking on Window and choosing the workbook
from there.


Annie wrote:

Is is possible for a macro to copy a range of cells from one file to the same
range in another file? The two files would be open; but the file names may
change. The recorder records absolute ranges correctly, but hard codes the
file names. Thanks for any help.
Annie

--

Dave Peterson

Copy from two files
 

Whoops. Same address.

Option Explicit
Sub testme()

Dim rngF As Range
Dim rngT As Range

Set rngF = Nothing
On Error Resume Next
Set rngF = Application.InputBox(Prompt:="Select the range", _
Default:=Selection.Areas(1).Address(external:=True ), _
Type:=8).Areas(1)
On Error GoTo 0

If rngF Is Nothing Then
MsgBox "Try later"
Exit Sub
End If

Set rngT = Nothing
On Error Resume Next
Set rngT = Application.InputBox(Prompt:="Select the range", Type:=8) _
.Cells(1)
On Error GoTo 0

If rngT Is Nothing Then
MsgBox "Try later"
Exit Sub
End If

rngF.Copy _
Destination:=rngT.Parent.Range(rngF.Cells(1).Addre ss)

Application.CutCopyMode = False

End Sub

The code still prompts for a "to range", but just uses that to get the correct
sheet.

Annie wrote:

Is is possible for a macro to copy a range of cells from one file to the same
range in another file? The two files would be open; but the file names may
change. The recorder records absolute ranges correctly, but hard codes the
file names. Thanks for any help.
Annie

--

Dave Peterson

Copy from two files
 

Thank you Dave. This is perfect. I can work with this.
Annie

"Dave Peterson" wrote:

Whoops. Same address.

Option Explicit
Sub testme()

Dim rngF As Range
Dim rngT As Range

Set rngF = Nothing
On Error Resume Next
Set rngF = Application.InputBox(Prompt:="Select the range", _
Default:=Selection.Areas(1).Address(external:=True ), _
Type:=8).Areas(1)
On Error GoTo 0

If rngF Is Nothing Then
MsgBox "Try later"
Exit Sub
End If

Set rngT = Nothing
On Error Resume Next
Set rngT = Application.InputBox(Prompt:="Select the range", Type:=8) _
.Cells(1)
On Error GoTo 0

If rngT Is Nothing Then
MsgBox "Try later"
Exit Sub
End If

rngF.Copy _
Destination:=rngT.Parent.Range(rngF.Cells(1).Addre ss)

Application.CutCopyMode = False

End Sub

The code still prompts for a "to range", but just uses that to get the correct
sheet.

Annie wrote:

Is is possible for a macro to copy a range of cells from one file to the same
range in another file? The two files would be open; but the file names may
change. The recorder records absolute ranges correctly, but hard codes the
file names. Thanks for any help.
Annie

--

Dave Peterson

Copy from two files
 

Hi Jim,
Thank you. I have Dave's code I can work with. Thank you much.
Annie

"Jim Thomlinson" wrote:

So long as you have a consistent destination workbook then you are good to
go. I can give you code to copy the selected range to an identical range on a
different sheet on that workbook. Let me know if that is what you want.
--
HTH...

Jim Thomlinson


"Annie" wrote:

If the one to copy from is open, is there code to prompt and wait for the
destination file to open? Probably not. Thank you Jim.
Annie

"Jim Thomlinson" wrote:

That is a little tricky from the standpoint of which sheet would you like to
copy to. What if there are multiple books open, then which book should it
copy to. The logic is really fuzzy making the code complex.
--
HTH...

Jim Thomlinson


"Annie" wrote:

Is is possible for a macro to copy a range of cells from one file to the same
range in another file? The two files would be open; but the file names may
change. The recorder records absolute ranges correctly, but hard codes the
file names. Thanks for any help.
Annie