Copy from one open workbook to another?

[Geoff]

I asked the following question a few days ago and was given a link to a Ron
Debruin page (with more code I didn't understand). What I would prefer is for
someone to please comment on why my code below doesn't work. I am trying to
learn Excel VBA and considering that the help that comes with the program is
only marginally better than useless, I was hoping for an explanation here.

Please...no more links to Ron Debruin. I'm sure that code is genius, but it
hardly helps if I don't understand it.

Here goes....

-------------------------------------------------------------------------
I have a workbook that gets a lot of use by others. Periodically, I
distribute revisions so I wrote some code to clear all my data (inventory
levels, etc.) but leave the base sheet data (product attributes, etc.), That
was relatively easy.

Now I want to be able to take a cleaned file and put my data back in which
is stumping me because I don't really know how to switch back and forth
between sheets.

One wrinkle is that I don't want to refer to the actual filenames in the
code since they do change.

So I tried this:

iBlank = 1
iData = 2

Windows(iData).Activate
Range("C2:C4").Copy
Windows(iBlank).Activate
Range("C2:C4").Select
ActiveSheet.Paste

I will always be running this from the empty datafile, so if I'm not
mistaken that will have Index = 1 and the one I am copying from will have
Index = 2 if I understand this correctly. There are many ranges that need to
be copied.

So it would seem to me that the above:
* activates the datafile
* copies the range in the datafile
* activates the blankfile
* pastes in the range in the blankfile

The data gets copied and I see the marching ants. But it does not get copied
to the blankfile. I think it just copies it right back to the datafile.

TIA...Geoff

 

[Dave Peterson]

First, I would never trust the windows collection at all -- or the workbooks
collection if I were using a counter.

I'd do something like:

Dim FromWks as worksheet
dim ToWks as worksheet
dim Resp as long

I'd use some other way to determine the workbook that gets the update

maybe even the activeworkbook???
set towks = activeworkbook.worksheets("SomeToNameHere")
or the active sheet

resp = msgbox(Prompt:="You're about to update the activesheet, right?", _
buttons:=vbokcancel)

if resp = vbcancel then
msgbox "try later after you activate the correct sheet"
exit sub
end if

set towks = activesheet

Then if the worksheet that contains the range to copy:
set fromwks = thisworkbook.worksheets("Somesheetnamehere")

or if I wanted to have the code open the workbook:
set fromwks = workbooks.open(filename:="C:\path\newworkbookname.xls") _
.worksheets("someothersheetnamehere")


Then do the copy|paste

fromwks.range("c2:c4").copy _
destination:=towks.range("c2") 'let excel determine the extent of the paste

=====
Depending on activating the window means that the correct worksheet is active.
And I wouldn't trust all those stars to align (for me, anyway!).

 

[CellShocked]

I asked the following question a few days ago and was given a link to a Ron
Debruin page (with more code I didn't understand). What I would prefer is for
someone to please comment on why my code below doesn't work. I am trying to
learn Excel VBA and considering that the help that comes with the program is
only marginally better than useless, I was hoping for an explanation here.

Please...no more links to Ron Debruin. I'm sure that code is genius, but it
hardly helps if I don't understand it.

Here goes....

-------------------------------------------------------------------------
I have a workbook that gets a lot of use by others. Periodically, I
distribute revisions so I wrote some code to clear all my data (inventory
levels, etc.) but leave the base sheet data (product attributes, etc.), That
was relatively easy.

Now I want to be able to take a cleaned file and put my data back in which
is stumping me because I don't really know how to switch back and forth
between sheets.

One wrinkle is that I don't want to refer to the actual filenames in the
code since they do change.

So I tried this:

iBlank = 1
iData = 2

Windows(iData).Activate
Range("C2:C4").Copy
Windows(iBlank).Activate
Range("C2:C4").Select
ActiveSheet.Paste

I will always be running this from the empty datafile, so if I'm not
mistaken that will have Index = 1 and the one I am copying from will have
Index = 2 if I understand this correctly. There are many ranges that need to
be copied.

So it would seem to me that the above:
* activates the datafile
* copies the range in the datafile
* activates the blankfile
* pastes in the range in the blankfile

The data gets copied and I see the marching ants. But it does not get copied
to the blankfile. I think it just copies it right back to the datafile.

TIA...Geoff

So, bring up the macro recorder, and carefully perform the same task
manually, then stop recording and examine the code.

OR

When you switch to the blank file, your cursor should be in A1, so you
do not need to do anything other than the paste operation (ooops).
Selecting those cells may be what causes the failure. Just select the
upper left corner cell in the paste destination location which matches
your target range.

 

[CellShocked]

destination:=towks.range("c2")


'let excel determine the extent of the paste

This is what he wanted to know about why his may not have worked.