Copy from one open workbook to another? 11-22-09

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
0
Utf
11/22/2009 5:47:02 PM
excel.programming 6508 articles. 2 followers. Follow

3 Replies
591 Views

Similar Articles

[PageSpeed] 17

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!).



Geoff wrote:
> 
> 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
0
Dave
11/22/2009 6:24:05 PM
On Sun, 22 Nov 2009 09:47:02 -0800, Geoff <gebobs@clear.net> wrote:

>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.
0
CellShocked
11/22/2009 7:16:40 PM
On Sun, 22 Nov 2009 12:24:05 -0600, Dave Peterson
<petersod@verizonXSPAM.net> wrote:

>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.
0
CellShocked
11/22/2009 7:34:33 PM
Reply:

Similar Artilces:

Freeze Excel Spreadsheet till template open first then spreadsheet
Hi, When I export the data from Access 2002 to excel spreadsheet after select excel templates. I couldn't open the spreadsheet until i open the template and then closed the template. I open the spreadsheet where i get the data from Access 2002. It works. Here is the area that I have trouble with. 'Hide warnings on the spreadsheet ExcellApplication.DisplayAlerts = False 'Save the workbook ExcellApplication.ActiveWorkbook.Save 'Turn spreadsheet warnings back on ExcellApplication.DisplayAlerts = True 'Make it visible ExcellApplication.V...

Opening publisher 97 with a later version
I have publisher 97 on my windows xp and it works fine. However, I had someone refine some work I had done and apparently they used a later version of Publisher because when I loaded their CD, I got the message "Publisher cannot load files from a different version" What can I do--does this mean I have to buy a later version to match the version he used, or do I have to buy one of those programs that can open other programs. If I do the later, will I be able to use my 97 version to make changes once I get the files open on the later version Thanks rjda Refer to http://www.mvps....

open two different Access reports
Hi, I was wondering if i can get some help here. I have two different reports that i want to open when a user clicks a button to view the reports for printing. Is there any way of popping them up at the same time in VBA? Thank you in advance Associates wrote: >Hi, > >I was wondering if i can get some help here. I have two different reports >that i want to open when a user clicks a button to view the reports for >printing. Is there any way of popping them up at the same time in VBA? > >Thank you in advance Yes. Call the DoCmd.OpenReport command twic...

Unable to open Outlook Express after upgrade to Win. XP
I upgraded from windows98 to windows XP Pro. Now when I try to use OE I get two error messages. "Outlook Express could not be started. The application was unable to open the OE message store. Your compter may be out of memory or your disk is full.....0x800c012e,3" "OE could not be started because MSOE.DLL could not be initialized. OE maynot be installd correctly" I found a similiar problem fix for window2000 which said to delete OE, rename the old folder, then edit the registry and reinstall. I did the above but was not allowed to rename the old folder and th...

Opening an *.MSG file with it's respective attachment.
Hello, The file *.MSG is a message file. This can be viewed by any editor, like Notepad. However, when there's an attachment to it, we see garbage. Is there a way to open the attachments present inside the *.MSG files ? Thank you Yes, by opening it with Outlook. -- Roady [MVP] www.sparnaaij.net Microsoft Office and Microsoft Office related News Also Outlook FAQ, How To's, Downloads and more... Tips of the month: -Create your own fully customized Toolbar -Creating a Classic View in Outlook 2003 Subscribe to the newsletter to receive news and tips & tricks in your mailbox!...

OWA jumping mouse
I have two computers on my network that are having an interesting issue. When they are creating a message and typing away in the body of the message, randomly when they hit a key, any key, they system will act as if the mouse was clicked. If the mouse is over the send button, it would have sent the email. If the mouse was over the start button, the start menu would have come up. Where ever the mouse was left at, it will act as if it was clicked. It is very random but often. This only happens in OWA, not in outlook or any other place. Both systems are running XP pro SP2 with IE 6 SP2. ...

Can't open 2005 data file after reinstalling Money 2005
I am experiencing a recurring problem. I have had to reinstall Windows XP and MS Money 2005. I am now unable to open my previously converted 2005 file or restore any backup version. I consistently get the following error message: "Money cannot locate filename or cannot open it, possibly because it is a read-only file, you do not have permission to change it, or your disk drive is write- protected. If you have chosen the correct file and it cannot be accessed, you will need to click OK and then Restore your most recent backup file." Any help or thoughts would be greatly ap...

opening .pst archives
I am trying to open an outlook e-mail archive file (pst) that was made when I had Office 98 and transferred to my new computer (Office 2003 Pro). I am looking for an old e-mail that has now become evidence in a lawsuit. Can anyone help? You didn't say if the PST file was on a CDR/RW, hard drive, or network share. In any case, the PST file must be located on a local/network drive where you have full access (read, write, modify, delete, .etc) rights. Once the file is located in said place, you can open the PST file via File | Open | Outlook Data File. To close the PST file when you ...

Windows Server 2008 R2 04-09-10
Windows Server 2008 R2 and Windows 7 share the same code? how is that possible when Windows 7 has both 32 bit and 64 bit versions and windows server 2008 r2 is only 64 bit Hello Charle, As Microsoft is going to use only 64bit versions for servers they don't built the 32bit version. Sharing the same code doesn't mean that the server OS use exaclty the same files, there are a lot more and different ones. But the basic code is the same. Best regards Meinolf Weber Disclaimer: This posting is provided "AS IS" with no warranties, and confers no rights. ...

Macro for Sharepoint List and another Table?? Same # records?
I am creating a database which we have a form made up of prepopulated data (from a sharepoint list) and also data input needed by a user (for example, comments, etc.). I have two tables: one sharepoint list that is linked to sharepoint website and another table that houses the additional user inputs [table 2]. I have linked the files based on the primary key. So, every record in my table 2 has a plus sign and it will expand and you will see the linked data from the sharepoint list. Is there a way that I can automatically create a record for every corresponding record ...

changing values of one field based on another
How can I best change the values of one field in a table based on values of another field of the same table. We have an existing table of thousands of entries and I would like to use the following logic to populate a new boolean field. If field1 = "Done" Then BooleanFieldCompleted = True I have some Excel VBA experience but limited Access. I dont want to do this manually! Any assistance appreciated. In general, you'd use an Update query. However, in this case I don't see why you'd need such a field. Why not just create a query with a computed field that returns True...

Excel Opens Without Displaying Workbook
I am having issues with opening an Excel file. The file opens, but the workbook is not displayed. I tried the resolution in the article XL97: Excel Opens Without Displaying Workbook (http://support.microsoft.com/default.aspx?scid=kb;en-us;158996&Product=xlw97), but neither of the resolutions fixed the problem. Any suggestions?? Are you using Excel 97? -John Baughman Fort Collins, CO >-----Original Message----- >I am having issues with opening an Excel file. The file opens, but the workbook is not displayed. I tried the resolution in the article XL97: Excel Opens Without Di...

tesuto 03-11-07
tesuto...

Multiple copies of email in "Forward to"contact
HIe, I am running Exchnage 2003. My CEO has a conatct in our organisation that refer to his blackberry contact. I have set the forwarding to also keep a copy in his mailbox. What happens is that three copies of his forwarded email hit his blackberry account! Only one hits his internal mailbox. What I ahve determined is that the front end Exchange server seems to be creating the additional copies. Is this how its meant to be (don think so cos its such a nuisance and my CEO keeps complaining about it). How do I tell the front end server that this guy's mail server is the one that...

Opening pub files created with older Publisher versions #2
I have just upgraded to Publisher 2003 from 2000 and am having trouble with pub files sent to me for our chuch newsletter which is using Publisher 97 The text is not wrapping around graphics boxes. Can I fix this? I really don't want to go back to Pub 200 Thank Richard this is caused by Publisher 97 not been printer independent. Even if you went back to Publisher 2000, unless you have the identical font versions and printer driver you would have issues with formatting. It sounds like the person sending you the file has a garbage HP inkjet printer. Get them to install the HP5P laser p...

Office 2003 Service Pack 3--subsequent problems opening Publisher
I run Publisher 2003 on Windows XP. On June 13, I updated my system with Office 2003 Service Pack 3 so that I could open Word documents with the file ext docx. Subsequent to the Service Pack 3 installation, whenever I open a Publisher file (which I created), I get the following message: "Publisher has detected a problem in the file you are trying to open. If you are certain that this file came from a trusted source and does not contain harmful information, click OK." What is causing this and is there a way to stop this pop-up message? All publications? Error message when you...

Copying Data in a cell in one sheet to a cell in another sheet
I've run into a problem trying to copy data from a cell in one sheet to another. I have a spreadsheet called "rating" which contains a number of formula that calculates a final number. I also have a spreadsheet called "Final" that copies over the information from "Rating". In "Final", I'm trying to copy a number from "Rating" into a particular cell. I put in =Rating! G89, but it won't work. When I press enter, a window pops up "Update value:Rating". I press enter again and in the cell where I want the number ...

Copying dbx files from a CDR back into Outlook
My computer was recently hijakced and I had to reinstall everything. I did however save all of my folders in Outlook onto a CD Rom and I do not know how to transfer them back so I can use them. Also I had an animated version of outlook where you could choose loads of diffent stationery and I cannot seem to find that upgrade anaywhere. Can you help me? Tee <tsahai33@hotmail.com> wrote: > My computer was recently hijakced and I had to reinstall > everything. I did however save all of my folders in > Outlook onto a CD Rom and I do not know how to transfer > them back so...

printing multiple copies of Sigel GmbH
I have made a tent card in a small size of 5.588cm x 4.191cm for a swing tag, using Sigel GmbH in the "other labels" section. It prints one copy perfectly, leaving room on the page for at least about another 5 copies (which is what I want), but when in the print page section it doesn't give me the option to print multiple copies on the page. This will be an enormous waste of paper if I have to print the number of copies individually on each page. HELP! -- candere What is the product number for this label? I cannot find this size label on my 2007 version. -- Mary Sau...

Effectively stoping open relay.
I have a client who started using exchange 2003 few days back. This client is having more than 12 subnets starting from 172.27.50.0, 172.16.0.0 to 172.27.0.0 and 10.172.172.0 & 10.172.173.0 subnets in different location in town which are conneted by DSL. This users are connecting to the Exchange server 172.16.5.25. Most of these users are POP3 users. I have 2 SMTP Virtual Server running. Default SMTP Virtual server 172.16.5.25 and the second for Externel. In the Mail connector I have added only the external as the local bridgehead server. In the relay properties of the internal I...

VBA to put a copy of worksheet on the desktop 05-13-10
Hi all, In my workbook XYZ I have a sheet ABC. With a button on sheet DEF I can refresh sheet ABC. When the code finishes it job I want to add the actual date (short European notation dmyy) and time (f.i. 241110 16.31) to the name of the sheet (which becomes ABC 241110 16.31) and after that make a copy of that sheet in a separate workbook and put that workbook as an icon on the desktop of my computer. Is this possible? If so, please help me with the necessary code. Thanks in advance for your assistance. Jack Sons The Netherlands ...

opening a .docx with word 2004
Version: 2004 Operating System: Mac OS X 10.3 (Panther) How can I open a .docx attachment that has been sent to me with word 2004?? Can I convert it somehow at my end or does it have to be converted from the sender end? Thanks for any help... <cscs@sympatico.ca> wrote: > How can I open a .docx attachment that has been sent to me with word > 2004?? Can I convert it somehow at my end or does it have to be > converted from the sender end? Make sure that Office is up to date (or at least in version 11.5.0 - the altest version being 11.5.1) and install the XML convertrs you'...

Help me identify my missing permission (Cannot open public folder) -2147217843 (Maybe Authentication Fails?)
The following snippet of code throws an error number -2147217843. When I googled this error code, I see many references to authentication failed. I am assuming my problem is some kind of permission related problem on the "MyNewFolder" public folder. -- start code --- Dim objFolder As New CDO.Folder Dim f As ADODB.Field 'sURL is like: file://./backofficestorage/mydomain.com/Public Folders/MyNewFolder/ objFolder.DataSource.Open sURL, , adModeReadWrite, adFailIfNotExists --- end code -- I have code that runs before this that actually creates the "MyNewFolder" publ...

Cant read one instance of recurring appointment
The problem we're having is a little different that what I've seen posted. A user randomly gets the message "Cant read one instance of recurring appointment. close any open appointments and try again, or recreate the appointment" several times a day. When you click OK the same message appears several times. Outlook doesn't indicate which recurring appointment is having the problem and the message just pops up at various times even if the user isn't in the calendar. We've deleted the Frmcache file and run the clean profile switch, but that hasn't worked. Has an...

Date format 04-11-06
Hi, Is there a possibility that the dates used in all the entities are not in the default format mm/dd/yyyy but in dd/mm/yyyy. I already adapted the Organisatonal settings, that only adapts the journal but nog the dates of an appointment. Does anyone have an idea? Thanks, ...