Hi EXCEL VBA Wizards!

I keep improving a template I use but each time I improve it, I need to
retest it with data from a number of past examples to make sure I haven't
compromised any functionality. In effect, I need to copy and paste various
blocks of data from the same-named worksheet of an old file to the
same-named worksheet (and locations) of the new template. I think I can do
this via simple recording of the macro. The only thing I don't know is the
code to have it, at the outset, ask me what the name of the other EXCEL file
is and, then, how to tell it to go to that filename, before each copy
command. Can you help me, please, with the specific coding to do this?

I'm thinking something like:

1) have it ask the user via some sort of input message box, for the filename
I want to use for copying into the current file that will contain the macro.

2) assign the response and identify it as "X"

3) go to filename X.xls

Thank you,
Jill

Hi Jill

See if you can use this:

Sub test()
Dim F As Variant
F = Application.GetOpenFilename("Workbooks (*.xls), *.xls", _
, "Select a file to copy into:")
If F = False Then Exit Sub
Workbooks.Open F
End Sub

HTH. Best wishes Harald

"Grace" skrev i melding
...
Hi EXCEL VBA Wizards!

I keep improving a template I use but each time I improve it, I need to
retest it with data from a number of past examples to make sure I haven't
compromised any functionality. In effect, I need to copy and paste
various
blocks of data from the same-named worksheet of an old file to the
same-named worksheet (and locations) of the new template. I think I can
do
this via simple recording of the macro. The only thing I don't know is
the
code to have it, at the outset, ask me what the name of the other EXCEL
file
is and, then, how to tell it to go to that filename, before each copy
command. Can you help me, please, with the specific coding to do this?

I'm thinking something like:

1) have it ask the user via some sort of input message box, for the
filename
I want to use for copying into the current file that will contain the
macro.

2) assign the response and identify it as "X"

3) go to filename X.xls

Thank you,
Jill

With a little learning on my part, this works nicely. Only a couple of
follow-up questions. Your macro codes the file as F. I know that before
each copy operation, I don't want to re-open the file, but would rather
activate it. I tried several versions of:

Windows("F.xls").Activate

and each yields a bug. What is the proper syntax for activating this file
we call F, after it has been opened?

One other thing, I use a similar command (that DOES work, for the name of
the file with the macro in it. Specifically:

Windows("test.xls").Activate

and that works fine. However, if I save the template with a new name, it
won't adjust to it. So, I need some way to tell it to always paste into the
current filename, the one with the macro. What is the best way to define
this, so I can activate it over and over again? I'm thinking of something
like G = the current filename with this macro, then I activate G over and
over again as I am pasting

Great work. Thanks!

"Harald Staff" wrote in message
...
Hi Jill

See if you can use this:

Sub test()
Dim F As Variant
F = Application.GetOpenFilename("Workbooks (*.xls), *.xls", _
, "Select a file to copy into:")
If F = False Then Exit Sub
Workbooks.Open F
End Sub

HTH. Best wishes Harald

"Grace" skrev i melding
...
Hi EXCEL VBA Wizards!

I keep improving a template I use but each time I improve it, I need to
retest it with data from a number of past examples to make sure I
haven't
compromised any functionality. In effect, I need to copy and paste
various
blocks of data from the same-named worksheet of an old file to the
same-named worksheet (and locations) of the new template. I think I can
do
this via simple recording of the macro. The only thing I don't know is
the
code to have it, at the outset, ask me what the name of the other EXCEL
file
is and, then, how to tell it to go to that filename, before each copy
command. Can you help me, please, with the specific coding to do this?

I'm thinking something like:

1) have it ask the user via some sort of input message box, for the
filename
I want to use for copying into the current file that will contain the
macro.

2) assign the response and identify it as "X"

3) go to filename X.xls

Thank you,
Jill

"Grace" skrev i melding
...
I know that before
each copy operation, I don't want to re-open the file, but would rather
activate it.

Usually one doesn't have to select or activate anything to move/copy stuff
from one place to another. It just slows things down while looking ugly.

Best wishes Harald

Perhaps I am confused but I don't think you have answered my question. What
I do is copy data from a source file to the same location in a destination
file. I do this for many different areas on many different tabs.

So, I go to the source file (which you have called F) and I highlight, say,
worksheet tab 1, then a range of cells on that tab. Then I activate the
destination file, find the same tab and same cell area and paste it.

Then, I go back to the source file and repeat for another tab and area. So,
each time I am toggling between two files. I was trying to find a way to
call the source file each time. I thought the command:

Windows(F.xls).Activate

would work but it bombs the macro out. What is the right syntax to toggle
back to that sheet?

Thanks,
Grace



"Harald Staff" wrote in message
...
"Grace" skrev i melding
...
I know that before
each copy operation, I don't want to re-open the file, but would rather
activate it.

Usually one doesn't have to select or activate anything to move/copy stuff
from one place to another. It just slows things down while looking ugly.

Best wishes Harald

I don't know who this mona001 is, who has posted within my thread. My reply
was to Harald Staff and I hope he will reply once more.

Thanks!

"Grace" wrote in message
...
Perhaps I am confused but I don't think you have answered my question.
What
I do is copy data from a source file to the same location in a destination
file. I do this for many different areas on many different tabs.

So, I go to the source file (which you have called F) and I highlight,
say,
worksheet tab 1, then a range of cells on that tab. Then I activate the
destination file, find the same tab and same cell area and paste it.

Then, I go back to the source file and repeat for another tab and area.
So,
each time I am toggling between two files. I was trying to find a way to
call the source file each time. I thought the command:

Windows(F.xls).Activate

would work but it bombs the macro out. What is the right syntax to toggle
back to that sheet?

Thanks,
Grace



"Harald Staff" wrote in message
...
"Grace" skrev i melding
...
I know that before
each copy operation, I don't want to re-open the file, but would
rather
activate it.

Usually one doesn't have to select or activate anything to move/copy
stuff
from one place to another. It just slows things down while looking ugly.

Best wishes Harald

Grace

I didn't answer your question because I believed it was the wrong solution
to a problem. What you want done is to copy from one place to another, not
to activate windows. So I still won't tell you, but see if this does what
you want:

Sub test()
Dim F As Variant
Dim wbSource As Workbook
Dim wbTarget As Workbook

Set wbTarget = ThisWorkbook 'or ActiveWorkbook
F = Application.GetOpenFilename("Workbooks (*.xls), *.xls", _
, "Select a file to copy into:")
If F = False Then Exit Sub
Set wbSource = Workbooks.Open(F)
wbSource.Sheets(1).Range("A1:F14").Copy wbTarget.Sheets(2).Range("D1:I14")
wbSource.Saved = True
wbSource.Close

End Sub

OR: Do you need to manually select a range in the middle of your macro ?

HTH. Best wishes Harald


"Grace" skrev i melding
...
Perhaps I am confused but I don't think you have answered my question.
What
I do is copy data from a source file to the same location in a destination
file. I do this for many different areas on many different tabs.

So, I go to the source file (which you have called F) and I highlight,
say,
worksheet tab 1, then a range of cells on that tab. Then I activate the
destination file, find the same tab and same cell area and paste it.

Then, I go back to the source file and repeat for another tab and area.
So,
each time I am toggling between two files. I was trying to find a way to
call the source file each time. I thought the command:

Windows(F.xls).Activate

would work but it bombs the macro out. What is the right syntax to toggle
back to that sheet?

Thanks,
Grace



"Harald Staff" wrote in message
...
"Grace" skrev i melding
...
I know that before
each copy operation, I don't want to re-open the file, but would
rather
activate it.

Usually one doesn't have to select or activate anything to move/copy
stuff
from one place to another. It just slows things down while looking ugly.

Best wishes Harald

This certainly seems a lot quicker and I think I realize you don't want to
teach me inefficient programming. However, what little I do know is just
that so, going this route will require more clarifications, so please bear
with me!

First, do I replace the name of the worksheet I want to copy from in place
of "Sheets(1)". I tried that for a worksheet named "inputs" as follows:

wbSource.Inputs.Range("C11:E12").Copy wbTarget.Inputs.Range("C11:E12")

and it bombed the macro. If not, how do I identify the worksheet name?

Also, do I need to use your:

wbSource.Close

after each paste operation, or just at the end? If not, what is this
command doing? Also what would be the command for unprotecting a worksheet
before copying?

Thanks,
G


"Harald Staff" wrote in message
...
Grace

I didn't answer your question because I believed it was the wrong solution
to a problem. What you want done is to copy from one place to another, not
to activate windows. So I still won't tell you, but see if this does what
you want:

Sub test()
Dim F As Variant
Dim wbSource As Workbook
Dim wbTarget As Workbook

Set wbTarget = ThisWorkbook 'or ActiveWorkbook
F = Application.GetOpenFilename("Workbooks (*.xls), *.xls", _
, "Select a file to copy into:")
If F = False Then Exit Sub
Set wbSource = Workbooks.Open(F)
wbSource.Sheets(1).Range("A1:F14").Copy wbTarget.Sheets(2).Range("D1:I14")
wbSource.Saved = True
wbSource.Close

End Sub

OR: Do you need to manually select a range in the middle of your macro ?

HTH. Best wishes Harald


"Grace" skrev i melding
...
Perhaps I am confused but I don't think you have answered my question.
What
I do is copy data from a source file to the same location in a
destination
file. I do this for many different areas on many different tabs.

So, I go to the source file (which you have called F) and I highlight,
say,
worksheet tab 1, then a range of cells on that tab. Then I activate the
destination file, find the same tab and same cell area and paste it.

Then, I go back to the source file and repeat for another tab and area.
So,
each time I am toggling between two files. I was trying to find a way
to
call the source file each time. I thought the command:

Windows(F.xls).Activate

would work but it bombs the macro out. What is the right syntax to
toggle
back to that sheet?

Thanks,
Grace



"Harald Staff" wrote in message
...
"Grace" skrev i melding
...
I know that before
each copy operation, I don't want to re-open the file, but would
rather
activate it.

Usually one doesn't have to select or activate anything to move/copy
stuff
from one place to another. It just slows things down while looking
ugly.

Best wishes Harald

Hi Grace

Lots of things here. I'll try:

First, do I replace the name of the worksheet I want to copy from in place
of "Sheets(1)".

wbSource.Sheets("Inputs").Range("C11:E12").Copy

Also, do I need to use your:

wbSource.Close

after each paste operation, or just at the end?

Well, it closes the newly opened workbook. If you use this in a loop with
multiple workbook openings then wbSource will be reassigned to a new file
each time and your code won't keep track of the rest of them. But if you
copy several ranges from a single workbook, don't close it until all are
done.

Also what would be the command for unprotecting a worksheet
before copying?

wbTarget.Sheets("Inputs").Unprotect
'actions
wbTarget.Sheets("Inputs").Protect

or with a passwords:

wbTarget.Sheets("Inputs").Unprotect ("GraceLockedMe")
'actions
wbTarget.Sheets("Inputs").Protect ("GraceLockedMe")

HTH. Best wishes Harald

"Grace" skrev i melding
...
This certainly seems a lot quicker and I think I realize you don't want to
teach me inefficient programming. However, what little I do know is just
that so, going this route will require more clarifications, so please bear
with me!

First, do I replace the name of the worksheet I want to copy from in place
of "Sheets(1)". I tried that for a worksheet named "inputs" as follows:

wbSource.Inputs.Range("C11:E12").Copy wbTarget.Inputs.Range("C11:E12")

and it bombed the macro. If not, how do I identify the worksheet name?

Also, do I need to use your:

wbSource.Close

after each paste operation, or just at the end? If not, what is this
command doing? Also what would be the command for unprotecting a
worksheet
before copying?

Thanks,
G


"Harald Staff" wrote in message
...
Grace

I didn't answer your question because I believed it was the wrong
solution
to a problem. What you want done is to copy from one place to another,
not
to activate windows. So I still won't tell you, but see if this does
what
you want:

Sub test()
Dim F As Variant
Dim wbSource As Workbook
Dim wbTarget As Workbook

Set wbTarget = ThisWorkbook 'or ActiveWorkbook
F = Application.GetOpenFilename("Workbooks (*.xls), *.xls", _
, "Select a file to copy into:")
If F = False Then Exit Sub
Set wbSource = Workbooks.Open(F)
wbSource.Sheets(1).Range("A1:F14").Copy
wbTarget.Sheets(2).Range("D1:I14")
wbSource.Saved = True
wbSource.Close

End Sub

OR: Do you need to manually select a range in the middle of your macro ?

HTH. Best wishes Harald


"Grace" skrev i melding
...
Perhaps I am confused but I don't think you have answered my question.
What
I do is copy data from a source file to the same location in a
destination
file. I do this for many different areas on many different tabs.

So, I go to the source file (which you have called F) and I highlight,
say,
worksheet tab 1, then a range of cells on that tab. Then I activate
the
destination file, find the same tab and same cell area and paste it.

Then, I go back to the source file and repeat for another tab and
area.
So,
each time I am toggling between two files. I was trying to find a way
to
call the source file each time. I thought the command:

Windows(F.xls).Activate

would work but it bombs the macro out. What is the right syntax to
toggle
back to that sheet?

Thanks,
Grace



"Harald Staff" wrote in message
...
"Grace" skrev i melding
...
I know that before
each copy operation, I don't want to re-open the file, but would
rather
activate it.

Usually one doesn't have to select or activate anything to move/copy
stuff
from one place to another. It just slows things down while looking
ugly.

Best wishes Harald

OK, it took some thinking but it's working now. Actually, I don't even want
to close the source file, as I need to compare my new target template result
to it, to make sure the new one is processing the scenario data the same as
before. Also, I note that you had

wbSource.Saved = True

and I'm not sure why I would want to do that. I'm not changing the source,
only the target file, right? And if I inadvertently did, I wanted want to
save those changes. Right? Is your command intended to accomplish
something else?

Though your approach is very quick and clean, the only thing I don't like is
that I don't see the commands being carried out. Often this is my way of
realizing the macro isn't working right, for some odd case (and in initially
troubleshooting the macro). Is there any easy way to allow me to see the
files as this is being carried out. I can afford the extra 100 milliseconds
it might cost me.

Thanks so much for your great help!

G

"Harald Staff" wrote in message
...
Hi Grace

Lots of things here. I'll try:

First, do I replace the name of the worksheet I want to copy from in
place
of "Sheets(1)".

wbSource.Sheets("Inputs").Range("C11:E12").Copy

Also, do I need to use your:

wbSource.Close

after each paste operation, or just at the end?

Well, it closes the newly opened workbook. If you use this in a loop with
multiple workbook openings then wbSource will be reassigned to a new file
each time and your code won't keep track of the rest of them. But if you
copy several ranges from a single workbook, don't close it until all are
done.

Also what would be the command for unprotecting a worksheet
before copying?

wbTarget.Sheets("Inputs").Unprotect
'actions
wbTarget.Sheets("Inputs").Protect

or with a passwords:

wbTarget.Sheets("Inputs").Unprotect ("GraceLockedMe")
'actions
wbTarget.Sheets("Inputs").Protect ("GraceLockedMe")

HTH. Best wishes Harald

"Grace" skrev i melding
...
This certainly seems a lot quicker and I think I realize you don't want
to
teach me inefficient programming. However, what little I do know is
just
that so, going this route will require more clarifications, so please
bear
with me!

First, do I replace the name of the worksheet I want to copy from in
place
of "Sheets(1)". I tried that for a worksheet named "inputs" as
follows:

wbSource.Inputs.Range("C11:E12").Copy wbTarget.Inputs.Range("C11:E12")

and it bombed the macro. If not, how do I identify the worksheet name?

Also, do I need to use your:

wbSource.Close

after each paste operation, or just at the end? If not, what is this
command doing? Also what would be the command for unprotecting a
worksheet
before copying?

Thanks,
G


"Harald Staff" wrote in message
...
Grace

I didn't answer your question because I believed it was the wrong
solution
to a problem. What you want done is to copy from one place to another,
not
to activate windows. So I still won't tell you, but see if this does
what
you want:

Sub test()
Dim F As Variant
Dim wbSource As Workbook
Dim wbTarget As Workbook

Set wbTarget = ThisWorkbook 'or ActiveWorkbook
F = Application.GetOpenFilename("Workbooks (*.xls), *.xls", _
, "Select a file to copy into:")
If F = False Then Exit Sub
Set wbSource = Workbooks.Open(F)
wbSource.Sheets(1).Range("A1:F14").Copy
wbTarget.Sheets(2).Range("D1:I14")
wbSource.Saved = True
wbSource.Close

End Sub

OR: Do you need to manually select a range in the middle of your macro
?

HTH. Best wishes Harald


"Grace" skrev i melding
...
Perhaps I am confused but I don't think you have answered my
question.
What
I do is copy data from a source file to the same location in a
destination
file. I do this for many different areas on many different tabs.

So, I go to the source file (which you have called F) and I
highlight,
say,
worksheet tab 1, then a range of cells on that tab. Then I activate
the
destination file, find the same tab and same cell area and paste it.

Then, I go back to the source file and repeat for another tab and
area.
So,
each time I am toggling between two files. I was trying to find a
way
to
call the source file each time. I thought the command:

Windows(F.xls).Activate

would work but it bombs the macro out. What is the right syntax to
toggle
back to that sheet?

Thanks,
Grace



"Harald Staff" wrote in message
...
"Grace" skrev i melding
...
I know that before
each copy operation, I don't want to re-open the file, but would
rather
activate it.

Usually one doesn't have to select or activate anything to
move/copy
stuff
from one place to another. It just slows things down while looking
ugly.

Best wishes Harald

wbSource.Saved = true
doesn't save the workbook. It just tells excel to consider the workbook
"clean"--no changes since the last save.

wbSource.Save 'would actually save the workbook.

wbSource.Saved = true
'followed by
wbSource.close

means you won't get prompted with "Do you want to save changes to your
workbook?" right before it closes.

I like this line as an alternative:
wbSource.close savechanges:=false

=======
When I'm testing code, I'll put some breakpoints in the code (to stop it when it
hits a certain line). Then I'll go look at excel to see if things are
progressing ok.

You could also step through the code with F8's (inside the VBE).





Grace wrote:

OK, it took some thinking but it's working now. Actually, I don't even want
to close the source file, as I need to compare my new target template result
to it, to make sure the new one is processing the scenario data the same as
before. Also, I note that you had

wbSource.Saved = True

and I'm not sure why I would want to do that. I'm not changing the source,
only the target file, right? And if I inadvertently did, I wanted want to
save those changes. Right? Is your command intended to accomplish
something else?

Though your approach is very quick and clean, the only thing I don't like is
that I don't see the commands being carried out. Often this is my way of
realizing the macro isn't working right, for some odd case (and in initially
troubleshooting the macro). Is there any easy way to allow me to see the
files as this is being carried out. I can afford the extra 100 milliseconds
it might cost me.

Thanks so much for your great help!

G

"Harald Staff" wrote in message
...
Hi Grace

Lots of things here. I'll try:

First, do I replace the name of the worksheet I want to copy from in
place
of "Sheets(1)".

wbSource.Sheets("Inputs").Range("C11:E12").Copy

Also, do I need to use your:

wbSource.Close

after each paste operation, or just at the end?

Well, it closes the newly opened workbook. If you use this in a loop with
multiple workbook openings then wbSource will be reassigned to a new file
each time and your code won't keep track of the rest of them. But if you
copy several ranges from a single workbook, don't close it until all are
done.

Also what would be the command for unprotecting a worksheet
before copying?

wbTarget.Sheets("Inputs").Unprotect
'actions
wbTarget.Sheets("Inputs").Protect

or with a passwords:

wbTarget.Sheets("Inputs").Unprotect ("GraceLockedMe")
'actions
wbTarget.Sheets("Inputs").Protect ("GraceLockedMe")

HTH. Best wishes Harald

"Grace" skrev i melding
...
This certainly seems a lot quicker and I think I realize you don't want
to
teach me inefficient programming. However, what little I do know is
just
that so, going this route will require more clarifications, so please
bear
with me!

First, do I replace the name of the worksheet I want to copy from in
place
of "Sheets(1)". I tried that for a worksheet named "inputs" as
follows:

wbSource.Inputs.Range("C11:E12").Copy wbTarget.Inputs.Range("C11:E12")

and it bombed the macro. If not, how do I identify the worksheet name?

Also, do I need to use your:

wbSource.Close

after each paste operation, or just at the end? If not, what is this
command doing? Also what would be the command for unprotecting a
worksheet
before copying?

Thanks,
G


"Harald Staff" wrote in message
...
Grace

I didn't answer your question because I believed it was the wrong
solution
to a problem. What you want done is to copy from one place to another,
not
to activate windows. So I still won't tell you, but see if this does
what
you want:

Sub test()
Dim F As Variant
Dim wbSource As Workbook
Dim wbTarget As Workbook

Set wbTarget = ThisWorkbook 'or ActiveWorkbook
F = Application.GetOpenFilename("Workbooks (*.xls), *.xls", _
, "Select a file to copy into:")
If F = False Then Exit Sub
Set wbSource = Workbooks.Open(F)
wbSource.Sheets(1).Range("A1:F14").Copy
wbTarget.Sheets(2).Range("D1:I14")
wbSource.Saved = True
wbSource.Close

End Sub

OR: Do you need to manually select a range in the middle of your macro
?

HTH. Best wishes Harald


"Grace" skrev i melding
...
Perhaps I am confused but I don't think you have answered my
question.
What
I do is copy data from a source file to the same location in a
destination
file. I do this for many different areas on many different tabs.

So, I go to the source file (which you have called F) and I
highlight,
say,
worksheet tab 1, then a range of cells on that tab. Then I activate
the
destination file, find the same tab and same cell area and paste it.

Then, I go back to the source file and repeat for another tab and
area.
So,
each time I am toggling between two files. I was trying to find a
way
to
call the source file each time. I thought the command:

Windows(F.xls).Activate

would work but it bombs the macro out. What is the right syntax to
toggle
back to that sheet?

Thanks,
Grace



"Harald Staff" wrote in message
...
"Grace" skrev i melding
...
I know that before
each copy operation, I don't want to re-open the file, but would
rather
activate it.

Usually one doesn't have to select or activate anything to
move/copy
stuff
from one place to another. It just slows things down while looking
ugly.

Best wishes Harald











--

Dave Peterson

Thanks for your help on the save stuff, Dave. As far as viewing my macro as
it runs, it seems like nobody wants to let me see my macro at work! How
hard is it to have it show me the steps?

Thanks
G


"Dave Peterson" wrote in message
...
wbSource.Saved = true
doesn't save the workbook. It just tells excel to consider the workbook
"clean"--no changes since the last save.

wbSource.Save 'would actually save the workbook.

wbSource.Saved = true
'followed by
wbSource.close

means you won't get prompted with "Do you want to save changes to your
workbook?" right before it closes.

I like this line as an alternative:
wbSource.close savechanges:=false

=======
When I'm testing code, I'll put some breakpoints in the code (to stop it
when it
hits a certain line). Then I'll go look at excel to see if things are
progressing ok.

You could also step through the code with F8's (inside the VBE).





Grace wrote:

OK, it took some thinking but it's working now. Actually, I don't even
want
to close the source file, as I need to compare my new target template
result
to it, to make sure the new one is processing the scenario data the same
as
before. Also, I note that you had

wbSource.Saved = True

and I'm not sure why I would want to do that. I'm not changing the
source,
only the target file, right? And if I inadvertently did, I wanted want
to
save those changes. Right? Is your command intended to accomplish
something else?

Though your approach is very quick and clean, the only thing I don't
like is
that I don't see the commands being carried out. Often this is my way
of
realizing the macro isn't working right, for some odd case (and in
initially
troubleshooting the macro). Is there any easy way to allow me to see
the
files as this is being carried out. I can afford the extra 100
milliseconds
it might cost me.

Thanks so much for your great help!

G

"Harald Staff" wrote in message
...
Hi Grace

Lots of things here. I'll try:

First, do I replace the name of the worksheet I want to copy from in
place
of "Sheets(1)".

wbSource.Sheets("Inputs").Range("C11:E12").Copy

Also, do I need to use your:

wbSource.Close

after each paste operation, or just at the end?

Well, it closes the newly opened workbook. If you use this in a loop
with
multiple workbook openings then wbSource will be reassigned to a new
file
each time and your code won't keep track of the rest of them. But if
you
copy several ranges from a single workbook, don't close it until all
are
done.

Also what would be the command for unprotecting a worksheet
before copying?

wbTarget.Sheets("Inputs").Unprotect
'actions
wbTarget.Sheets("Inputs").Protect

or with a passwords:

wbTarget.Sheets("Inputs").Unprotect ("GraceLockedMe")
'actions
wbTarget.Sheets("Inputs").Protect ("GraceLockedMe")

HTH. Best wishes Harald

"Grace" skrev i melding
...
This certainly seems a lot quicker and I think I realize you don't
want
to
teach me inefficient programming. However, what little I do know is
just
that so, going this route will require more clarifications, so
please
bear
with me!

First, do I replace the name of the worksheet I want to copy from in
place
of "Sheets(1)". I tried that for a worksheet named "inputs" as
follows:

wbSource.Inputs.Range("C11:E12").Copy
wbTarget.Inputs.Range("C11:E12")

and it bombed the macro. If not, how do I identify the worksheet
name?

Also, do I need to use your:

wbSource.Close

after each paste operation, or just at the end? If not, what is
this
command doing? Also what would be the command for unprotecting a
worksheet
before copying?

Thanks,
G


"Harald Staff" wrote in message
...
Grace

I didn't answer your question because I believed it was the wrong
solution
to a problem. What you want done is to copy from one place to
another,
not
to activate windows. So I still won't tell you, but see if this
does
what
you want:

Sub test()
Dim F As Variant
Dim wbSource As Workbook
Dim wbTarget As Workbook

Set wbTarget = ThisWorkbook 'or ActiveWorkbook
F = Application.GetOpenFilename("Workbooks (*.xls), *.xls", _
, "Select a file to copy into:")
If F = False Then Exit Sub
Set wbSource = Workbooks.Open(F)
wbSource.Sheets(1).Range("A1:F14").Copy
wbTarget.Sheets(2).Range("D1:I14")
wbSource.Saved = True
wbSource.Close

End Sub

OR: Do you need to manually select a range in the middle of your
macro
?

HTH. Best wishes Harald


"Grace" skrev i melding
...
Perhaps I am confused but I don't think you have answered my
question.
What
I do is copy data from a source file to the same location in a
destination
file. I do this for many different areas on many different
tabs.

So, I go to the source file (which you have called F) and I
highlight,
say,
worksheet tab 1, then a range of cells on that tab. Then I
activate
the
destination file, find the same tab and same cell area and paste
it.

Then, I go back to the source file and repeat for another tab
and
area.
So,
each time I am toggling between two files. I was trying to find
a
way
to
call the source file each time. I thought the command:

Windows(F.xls).Activate

would work but it bombs the macro out. What is the right syntax
to
toggle
back to that sheet?

Thanks,
Grace



"Harald Staff" wrote in message
...
"Grace" skrev i melding
...
I know that before
each copy operation, I don't want to re-open the file, but
would
rather
activate it.

Usually one doesn't have to select or activate anything to
move/copy
stuff
from one place to another. It just slows things down while
looking
ugly.

Best wishes Harald











--

Dave Peterson

It's not very hard, but it requires either extra coding or bad coding.
You'll get pretty close if you record a macro while performing the actions
manually.

HTH. Best wishes Harald

"Grace" skrev i melding
...
Thanks for your help on the save stuff, Dave. As far as viewing my macro
as
it runs, it seems like nobody wants to let me see my macro at work! How
hard is it to have it show me the steps?

OK, thanks.

"Harald Staff" wrote in message
...
It's not very hard, but it requires either extra coding or bad coding.
You'll get pretty close if you record a macro while performing the actions
manually.

HTH. Best wishes Harald

"Grace" skrev i melding
...
Thanks for your help on the save stuff, Dave. As far as viewing my
macro
as
it runs, it seems like nobody wants to let me see my macro at work! How
hard is it to have it show me the steps?

Because of some other recommendations, the code I chose was a little different from what you wrote. It works fine except for when i try to use the wbsource, saved = true and wbSource.close commands. They bomb the macro out. I've copied the macro below (not through the very end, because the problem is earlier than the end) showing the four commands in bold that, somehow, seem to be contributing to the problem. Can you tell what's wrong? I really do want to have that capability to close out all the many files i open up. I guess I don't understand the need for both myFilename (you used "F") and wbSource.

Dim wbTarget As Workbook
Dim wbSource As Workbook
Dim myFilename As Variant
Dim RngToCopy As Range

Set wbTarget = ActiveWorkbook
myFilename = Application.GetOpenFilename("All files, *.*")

If myFilename = False Then
Exit Sub 'user hit cancel
End If
Set wbSource = Workbooks.Open(myFilename)


Workbooks.OpenText Filename:=myFilename, Origin:=437, StartRow:=1, DataType:=xlFixedWidth, FieldInfo:=Array( _
Array(0, 1), Array(7, 3), Array(17, 1), Array(32, 1), Array(40, 1), Array(48, 1)), _
TrailingMinusNumbers:=True

Application.Goto Reference:="R14C1"
For i = 1 To 1400
Selection.End(xlDown).Select
If ActiveCell.Row = 65536 Then GoTo l_pool
ActiveCell.Resize(13).EntireRow.Delete

Next i
l_pool: Rem

Selection.End(xlUp).Select
Application.Goto Reference:="R1C1"

x = Cells(Rows.Count, "a").End(xlUp).Row
'to use in range
Range("a1:f" & x).Select
Application.Calculation = xlCalculationManual ' Turn recalc off

'ActiveCell.Range("A1:F20").Select
'Range(Selection, Selection.End(xlDown)).Select
Selection.Copy

wbSource.Saved = True ' This closes a source workbook without saving
wbSource.Close

wbTarget.Activate
....


"Harald Staff" wrote in message ...
Grace

I didn't answer your question because I believed it was the wrong solution
to a problem. What you want done is to copy from one place to another, not
to activate windows. So I still won't tell you, but see if this does what
you want:

Sub test()
Dim F As Variant
Dim wbSource As Workbook
Dim wbTarget As Workbook

Set wbTarget = ThisWorkbook 'or ActiveWorkbook
F = Application.GetOpenFilename("Workbooks (*.xls), *.xls", _
, "Select a file to copy into:")
If F = False Then Exit Sub
Set wbSource = Workbooks.Open(F)
wbSource.Sheets(1).Range("A1:F14").Copy wbTarget.Sheets(2).Range("D1:I14")
wbSource.Saved = True
wbSource.Close

End Sub

OR: Do you need to manually select a range in the middle of your macro ?

HTH. Best wishes Harald


"Grace" skrev i melding
...
Perhaps I am confused but I don't think you have answered my question.
What
I do is copy data from a source file to the same location in a destination
file. I do this for many different areas on many different tabs.

So, I go to the source file (which you have called F) and I highlight,
say,
worksheet tab 1, then a range of cells on that tab. Then I activate the
destination file, find the same tab and same cell area and paste it.

Then, I go back to the source file and repeat for another tab and area.
So,
each time I am toggling between two files. I was trying to find a way to
call the source file each time. I thought the command:

Windows(F.xls).Activate

would work but it bombs the macro out. What is the right syntax to toggle
back to that sheet?

Thanks,
Grace



"Harald Staff" wrote in message
...
"Grace" skrev i melding
...
I know that before
each copy operation, I don't want to re-open the file, but would
rather
activate it.

Usually one doesn't have to select or activate anything to move/copy
stuff
from one place to another. It just slows things down while looking ugly.

Best wishes Harald

"Grace" skrev i melding
...
Perhaps I am confused but I don't think you have answered my question.
What
I do is copy data from a source file to the same location in a destination
file. I do this for many different areas on many different tabs.

So, I go to the source file (which you have called F) and I highlight,
say,
worksheet tab 1, then a range of cells on that tab. Then I activate the
destination file, find the same tab and same cell area and paste it.

Then, I go back to the source file and repeat for another tab and area.
So,
each time I am toggling between two files. I was trying to find a way to
call the source file each time. I thought the command:

Grace,

"Harald Staff" wrote in message
...
"Grace" skrev i melding
...
Usually one doesn't have to select or activate anything to move/copy stuff
from one place to another. It just slows things down while looking ugly.

Best wishes Harald

The code below will list out the names of all opened windows in columnH, and validate the cell G1
with a dropdown list to select one from it.

Sub ListMyWBks()
For i = 1 To Windows.Count
Range("H" & i).Value = Windows(i).Caption
Next i
With Range("G1").Validation
.Delete
.Add Type:=xlValidateList, _
AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, Formula1:="=$H$1:$H$10"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
End Sub

I made the above list in Sheet6 of my source file and select one of the destination file
then select the range I want to copy (activecell of the selection is Top Left cell)
and run this macro

Sub CopyData()
DesWB = Sheets("Sheet6").Range("G1").Value
DesShName = ActiveWorkbook.ActiveSheet.Name
DesCell = ActiveCell.Address

Selection.Copy _
Destination:=Workbooks(DesWB) _
.Sheets(DesShName).Range(DesCell)
End Sub

when that file is done go to sheet6 and select another file
if same location in a destination file = True for the sheet name too
above will ease your life to some extend.

HTH
Cecil

I think this came too late to help, as I found some workarounds. Yet, I am saving it because it looks like it will really come in handy someday!

Thanks,
Grace
"Cecilkumara Fernando" <cekufdo@sltnetDOTlk wrote in message ...

"Grace" skrev i melding
...
Perhaps I am confused but I don't think you have answered my question.
What
I do is copy data from a source file to the same location in a destination
file. I do this for many different areas on many different tabs.

So, I go to the source file (which you have called F) and I highlight,
say,
worksheet tab 1, then a range of cells on that tab. Then I activate the
destination file, find the same tab and same cell area and paste it.

Then, I go back to the source file and repeat for another tab and area.
So,
each time I am toggling between two files. I was trying to find a way to
call the source file each time. I thought the command:

Grace,

"Harald Staff" wrote in message
...
"Grace" skrev i melding
...
Usually one doesn't have to select or activate anything to move/copy stuff
from one place to another. It just slows things down while looking ugly.

Best wishes Harald

The code below will list out the names of all opened windows in columnH, and validate the cell G1
with a dropdown list to select one from it.

Sub ListMyWBks()
For i = 1 To Windows.Count
Range("H" & i).Value = Windows(i).Caption
Next i
With Range("G1").Validation
.Delete
.Add Type:=xlValidateList, _
AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, Formula1:="=$H$1:$H$10"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
End Sub

I made the above list in Sheet6 of my source file and select one of the destination file
then select the range I want to copy (activecell of the selection is Top Left cell)
and run this macro

Sub CopyData()
DesWB = Sheets("Sheet6").Range("G1").Value
DesShName = ActiveWorkbook.ActiveSheet.Name
DesCell = ActiveCell.Address

Selection.Copy _
Destination:=Workbooks(DesWB) _
.Sheets(DesShName).Range(DesCell)
End Sub

when that file is done go to sheet6 and select another file
if same location in a destination file = True for the sheet name too
above will ease your life to some extend.

HTH
Cecil