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