Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy Files with Links | Excel Discussion (Misc queries) | |||
copy many files into one | Excel Programming | |||
copy files | Excel Programming | |||
Copy Files | Excel Programming | |||
copy files from one dir to another | Excel Programming |