copy and paste whole worksheet from one workbook to another

I need to copy an entire worksheet from one workbook to another.  Currently, 
i am using the following code:

Set objExcel = CreateObject("Excel.Application")
set objWorkbook = objExcel.Workbooks.Open(Filename1)	
set objWorksheet = objworkbook.worksheets(1)	
objExcel.displayalerts=false
Set objRange = objWorksheet.UsedRange	
objRange.copy

set objworkbook = objExcel.workbooks.open(filename2)
set objworksheet = objworkbook.worksheets("FS Data")
Set objRange2 = objexcel.Range("A1")
objrange2.activate
objworksheet.paste
objworkbook.save 

My data gets there, but only if i never edit the destination sheet or even 
type on it.  I need my copy to always start in A1.  It does not. the copy 
will start from whereever the cursor was last on that sheet when the file was 
saved.  I have to start sharing the script and the file with other users who 
will not be as careful as i will.
-- 
Allen Whitelock
Systems Administrator
World Class Automotive

0
Utf
12/23/2009 5:36:01 AM
excel.programming 6508 articles. 2 followers. Follow

3 Replies
847 Views

Similar Articles

[PageSpeed] 48

Try the below

Set objExcel = CreateObject("Excel.Application")
set objWorkbook1 = objExcel.Workbooks.Open(Filename1)	
set objworkbook2 = objExcel.workbooks.open(filename2)

objworkbook1.worksheets(1).UsedRange.Copy _
objworkbook2.worksheets("FS Data").Range("A1") 	

objworkbook2.save



-- 
Jacob


"Allen the Computer Guy" wrote:

> I need to copy an entire worksheet from one workbook to another.  Currently, 
> i am using the following code:
> 
> Set objExcel = CreateObject("Excel.Application")
> set objWorkbook = objExcel.Workbooks.Open(Filename1)	
> set objWorksheet = objworkbook.worksheets(1)	
> objExcel.displayalerts=false
> Set objRange = objWorksheet.UsedRange	
> objRange.copy
> 
> set objworkbook = objExcel.workbooks.open(filename2)
> set objworksheet = objworkbook.worksheets("FS Data")
> Set objRange2 = objexcel.Range("A1")
> objrange2.activate
> objworksheet.paste
> objworkbook.save 
> 
> My data gets there, but only if i never edit the destination sheet or even 
> type on it.  I need my copy to always start in A1.  It does not. the copy 
> will start from whereever the cursor was last on that sheet when the file was 
> saved.  I have to start sharing the script and the file with other users who 
> will not be as careful as i will.
> -- 
> Allen Whitelock
> Systems Administrator
> World Class Automotive
> 
0
Utf
12/23/2009 6:33:01 AM
Hi Allen

This will open a designated workbook paste the data from the sheet you
run it from, close the workbook saving the changes.  You will have to
adjust the sheet name, the file path and the copy range.

Take care

Marcus

Option Explicit

Sub OpenXL()

Dim oWbk As Workbook
Dim sFil As String
Dim sPath As String
Dim twbk As Workbook
Dim lr As Integer
Dim lw As Integer
Dim strFullName As String

Set twbk = ActiveWorkbook
Application.DisplayAlerts = False
Application.ScreenUpdating = False

lw = Range("A" & Rows.Count).End(xlUp).Row
twbk.Sheets("Sheet1").Range("A2:A" & lw).Copy
sPath = "R:\"  'Cell B2 of Cal sheet, location of files
ChDir sPath
sFil = Dir("Test.xls") 'change or add formats
strFullName = sPath & sFil

Set oWbk = Workbooks.Open(strFullName)
lr = oWbk.Sheets("FS Data").Range("A" & Rows.Count).End(xlUp).Row + 1
oWbk.Sheets("FS Data").Range("A" & lr).PasteSpecial xlPasteValues
oWbk.Close True 'close the workbook, saving changes
sFil = Dir

 End Sub




0
marcus
12/23/2009 6:35:50 AM
This worked perfectly and was too easy.  I am kicking myself for not figuring 
this one out on my own.  

Thanks.
-- 
Allen Whitelock
Systems Administrator
World Class Automotive



"Jacob Skaria" wrote:

> Try the below
> 
> Set objExcel = CreateObject("Excel.Application")
> set objWorkbook1 = objExcel.Workbooks.Open(Filename1)	
> set objworkbook2 = objExcel.workbooks.open(filename2)
> 
> objworkbook1.worksheets(1).UsedRange.Copy _
> objworkbook2.worksheets("FS Data").Range("A1") 	
> 
> objworkbook2.save
> 
> 
> 
> -- 
> Jacob
> 
> 
> "Allen the Computer Guy" wrote:
> 
> > I need to copy an entire worksheet from one workbook to another.  Currently, 
> > i am using the following code:
> > 
> > Set objExcel = CreateObject("Excel.Application")
> > set objWorkbook = objExcel.Workbooks.Open(Filename1)	
> > set objWorksheet = objworkbook.worksheets(1)	
> > objExcel.displayalerts=false
> > Set objRange = objWorksheet.UsedRange	
> > objRange.copy
> > 
> > set objworkbook = objExcel.workbooks.open(filename2)
> > set objworksheet = objworkbook.worksheets("FS Data")
> > Set objRange2 = objexcel.Range("A1")
> > objrange2.activate
> > objworksheet.paste
> > objworkbook.save 
> > 
> > My data gets there, but only if i never edit the destination sheet or even 
> > type on it.  I need my copy to always start in A1.  It does not. the copy 
> > will start from whereever the cursor was last on that sheet when the file was 
> > saved.  I have to start sharing the script and the file with other users who 
> > will not be as careful as i will.
> > -- 
> > Allen Whitelock
> > Systems Administrator
> > World Class Automotive
> > 
0
Utf
12/29/2009 10:06:01 PM
Reply:

Similar Artilces:

If cell value is greater than another cell value, clear contents.
Okay, hopefully this is the last question today! I've found the below formula in one of the posts here, but my brain has completely failed on me and I'm not sure how to adapt it to my requirements. I have a value which is entered by the user after a prompt, that populates in cell G4 in a sheet called 'Lookup'. For arguments sake, the current value is 30/11/09. I have another worksheet, 'Data', which has a column populated with dates (say column B). I'd like a macro that looks in column B on the data sheet, and clears out any dates which are gr...

Use the same name on multiple sheets in same workbook
I am new to Excel 2002. In prior versions I could create sheet specific names easily. With Excel 2002 every time I try to re use a name on another sheet (same workbook) the name gets moved to the new sheet. I use several workbooks (one book for sales, one for cost of goods, etc), with 12 sheets named for each month of the year. Each sheet is a duplicate of the first. As the year progresses I fill in the data at the end of each month. In the past I used the same names(Gross.Sales for example) on each sheet in the workbook. I then can build monthly summary reports using these names...

Copying a doc from Excel and pasting it into Word.
I have a 2 page doc in Word. When I paste the excel spreadsheet to page 2, it doesn't fit. I have tried MS help. I still need help. Thanks. -- KEN Hi KC, When you paste an object such as an Excel workbook into Word, Word will only display as much as will fit onto a single page in Word, regardless of how the workbook is configured in Excel. You can, of course, reduce the object's scaling so that more of it will fit - you can increase the # of rows/columns by opening the embedded object then dragging the handles at the sides/top/bottom. If that's not what you want, you can ma...

lookup tables across two workbooks
Hi I have two workbooks to which i wish to do a lookup for. The first wookbook contains totals via subtotals as below A1 0741.702.113 B1 $250.00 C1 EXPENSES A5 0745.753.112 B5 $2510.00 C5 BANK FEES The other workbook contains a vlookup to get the dollar amount from the first workbook and inserts into the second workbook.(column 2) It appears it doesn't work because the first workbook is total made up of subtotals with the other rows being hidden. Do i have to use the vlookup combined with match etc?? or maybe the format is the problem?? Thankyou in advance Darr...

VB Scripting/Macro
Question: What VB code will allow me to rewrite just the worksheet reference in a function/cell reference. For example it says =January!$AH40 and I would like a macro that would change it to =February!$AH40 Any suggestions would be greatly appreciated!!! :) Background: I have 12 cells in a row that each refer to the same cell location on twelve different worksheets within the same workbook. The fill function does not "fill" the worksheet reference. It is too time consuming to manually input or edit 2016 cells. Hi you may have a look at the INDIRECT function for this ...

What is involved in using my copy of PUB2002 on a network?
I have a copy of PUB2002 I purchased several years ago and would like to uninstall it at home and install it on the network at my church where I volunteer. I read a little about License Packs but am not sure if this is the answer, how to go about getting it, cost, etc? We are not able to afford to purchase Publisher for the whole group of us, but I do the weekly Woship bulletin, currently in Word, and want to convert to Publisher, so I thought I could save us some money by using my copy. Any good and legal info would be appreciated. Carroll Open Publisher, select Help, and type &q...

Server 2003 slow copying from external drives
I just re-installed SBS 2003 when the drives in the RAID array failed. I rebuilt it without a RAID. I am experiencing excrutiatingly slow copying from external USB drives. All Windows freeze while copying a large file (2GB+) from the external drive to the C drive. I have tried more than one external drive to eliminate drive problems as a cause. I have scanned the c: drive (chkdsk /R /X c:) for errors and found none. Copying from internal drive to internal drive does not cause the freezing or very slow copy speeds. This is a Dell Poweredge sc420 with dual Hitachi 500...

Sharing one outlook file between a laptop and a desktop
Does anyone know if it is possible to have the same outlook file on two machines, running different versions of outlook? I would like to buy a laptop, and use it while traveling. My desktop at home has Outlook 2002, and I would like both systems to run off the same outlook database (is it the .pst file?). When I return home, I would like to update the desktop by syncing it to the laptop, like a pocket pc, and updating the files on the desktop. Am I living on the other side of the looking glass, or is this possible? Can you this same idea work amongst a network of different machines runnin...

Downloads from Capital One with 2006 Deluxe
My downloads from Capital One Bank are consistently scrambled. One problem is the wrong payee may be assigned to the transaction. Another problem is the transaction is duplicated because payment is sent out one day and received by my bank 3 or 4 days later. Any ideas? In microsoft.public.money, setsim wrote: >My downloads from Capital One Bank are consistently scrambled. One problem >is the wrong payee may be assigned to the transaction. This might happen if something like POS etc is entered into the Payee spot. The following won't put the right payee there, but should kee...

Send messages in another "Send Items"
I have a personal and a groupmailbox in Outlook(2000). If I send a message on behalf of de groupmailbox the message is placed in my personal "Sent Items". But what I want is the message to be placed in the groupmailbox "Sent Items'. Anyone some ideas? W2000 Outlook 2000 Exchange 5.5 Thankz Peter Peter <jpmoons@yahoo.com> wrote: > I have a personal and a groupmailbox in Outlook(2000). > If I send a message on behalf of de groupmailbox the message is > placed in my personal "Sent Items". > But what I want is the message to be placed in the g...

copying cells to another document
I need to pull data from a job flow sheet into another worksheet. Approximately 10 columns, 16 Rows. I need each row to show up as a separate information sheet. This information will be used to create a file cover sheet, job name, site address, billing address, phone etc. A good way to create links between worksheets is to copy the source range (a row in your case), switch to the target sheet and select the first cell to be linked, and then do an Edit, Paste Special and click the Paste Link button. -- Jim "Jeffries'' Ex" <JeffriesEx@discussions.microsoft.com> w...

Insert spaces between pasted values #2
Hi! Is there a way to insert spaces between values that are pasted? For example, i have in rows 1,2,3 values a,b,c and i want to paste them into another list in rows 1,3,5 respectively (with a single-row space between pasted values). Thanx! M I presume you have more than just 3 rows. A general approach would be to use a helper column next to your data and to fill a simple sequence down that column for as much data as you have. Then copy all the numbers that make up that sequence and paste them immediately below the sequence in the same column, so that if you have 200 numbers for example the...

One Dll 2 programs
Hi, I need an application that will get all the setting from an XML file. But I also need to create a separate application that will read/update the XML file. Both applications must be separate as editing will only really be used for developing. So what I was thinking was using a Dll. The Dll could be called by both applications to get information but the Dll would ensure that the data is valid. The Dll must be 100% independent from the applications, that way I can update the dll and send the user the new version without having to recompile either applications. Would you know where ...

XL2007: Linked-labels reference is not copied with sheet
Dear group, when I link a chart's text-labels with cells, the formula in the label contains the sheet-name, e.g. =Mysheet!$A$1. In 2003 or below, I could duplicate the sheet inside the workbook by copying it. Now in 2007, the cell-references in the linked labels remain on the old sheet, they are not copied. Is there any better way but copying the sheet into a second workbook, renaming and then moving it back into first workbook? Thanks, Holger. Yeah, they broke this in 2007. It sounds very familiar, I'm sure I've reported it. I don't think there's a better way th...

Multiple Excel workbooks in different windows
How do I view multiple Excel workbooks in different windows. I have a dual monitor setup and want to have both open at the same time on different monitors. ...

Export GAL to another AD forest?
Hi, We're in the process of migrating to a new SBS domain. We are not upgrading,.but are starting from scratch. We need to keep the Exchange Global Address Book, as it contains hundreds of Companies and Fax #s that we use. Is there a utility that can export from one domain to another? Thanks, LDIFDE or CSVDE will do just fine in this situation. -- Ben Winzenz Exchange MVP MessageOne "Mike Herchel" <sdf> wrote in message news:u1Fg1XAeFHA.3864@TK2MSFTNGP10.phx.gbl... > Hi, > We're in the process of migrating to a new SBS domain. We are not >...

move a copy of my saved email files to another computer
I need to copy my saved email files at work to my work laptop. There are about two hundered. Can I copy the files to a disc? How do I up load them to Microsoft Office on my laptop? Can I send them in an email and then drag them to my in box? Create a new Outlook Data File (PST file). Copy the Message Folders you want to transfer into this file. Close Outlook. Copy this PST file and open it in the other Outlook installation. -- Russ Valentine [MVP-Outlook] "Maxx Hamilton Mix" <Maxx Hamilton Mix@discussions.microsoft.com> wrote in message news:56EB4B38-46E4-4DC8-8B4D-62...

Numbering worksheets using Auto Fill
Is it possible to number worksheets using Auto Fill Method? Your assistance will be appreciated. If you're referring to the worksheet (tab) names, no. In article <2A39D5BC-631A-4DEF-BC8B-49083DFD5DE7@microsoft.com>, Dhaval <Dhaval@discussions.microsoft.com> wrote: > Is it possible to number worksheets using Auto Fill Method? > > Your assistance will be appreciated. Hi, What do you mean by Autofill? Could you just eloborate with some example. You can only rearrange worksheet tab in the workbook an then right click to the left corner of the Worksheet tab row ...

Opening disk containing Excel worksheet
I saved an Excel worksheet to a disk in the a drive. Later I opened it to edited the material, then resaved it to the disk. When I tried to open it again, it would not open. I received an error message stating that "The file may be read-only or you may be trying to access a read- only location." Other files on the disk do open. How can I either open the file, or at least access the contents so that I can have it retyped? I find the following thread to be fairly comprehensive. http://groups.google.com/groups?hl=en&lr=lang_en&ie=UTF-8&oe=UTF-8&safe=off&...

Multiplying A Whole Column By 2 ??? Help
Hello, I recieve product stock list from my supplier in excell. 2 columns, A = products , B = prices I need a formula that will multiply all prices in column B by 2 so I can then easily forward stock list to my customers without revealing original values in column b. I have tried all the basic first thought ideas and none have worked. please provide help. thank you. joe@californiacichlids.com -- joesther37 ------------------------------------------------------------------------ joesther37's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=30031 View this threa...

Shared workbook error
Hi, Maybe someone could help me out, I recently made the regularly used excel spreadsheets on out office network shared workbooks. It worked fine for about a day or so but soon started stuffing up. We started having the problem where after opening and working in it for sometime (sometimes not even long) it would start acting like a read only file and not allow it to be saved. After closing the file it would only allow the user too open as a read only, until you rebooted. after which it would start working fine again for a short time before doing the same thing once again. This was happening...

Display report / summary based on data in two worksheets
Hi, I've uploaded the excel sheet at this location http://rapidshare.com/files/339230049/Test_SAMPLE.xls.html The first sheet has the list of features in column B. I2:W2 contain the Block# This is used to depict which features are being covered in which block. For eg. I5 and I6 show that Feature 1 and 2 are being covered in Block 1 In the second Sheet, Project Timeline, T8:T:19 show the iterations. each iteration can have one or more blocks within it. Based on the blocks selected for each iteration, the dates are calculated in column R "Bug Fix". I need ...

Can you combine data from 2 worksheets into 1 chart?
My son is working on a science project. He had to track the temperature and precipitation levels in Daytona Beach for 9 years. I put the data in 2 worksheets, one for temperature and one for precipitation. He now has to make charts for the data. We've done the 2 separate charts but now we need to make 1 chart showing all the data. Is there a way to take the data from the 2 separate worksheets and put it into 1 chart? http://peltiertech.com/Excel/ChartsHowTo/ChartFromDiffSheets.html - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom So...

opening Excel worksheet
I have a user that when he opens any of the worksheets in a particular file, the formatting is all messed up. He has excel 2002. When I open the same file, it looks fine. I have opened it in 2002 and 2003 and it seems fine. Yet, he can open other Excel worksheets in different files without the same problem. Any help would be appreicated. Thanks, I'm not sure what all messed up means. Is it the objects on the worksheet? Have they moved? Is it the fonts (characters in the cells)? Maybe it's a font problem--if you change the font to something else and save and have the pro...

Copy all incoming and outgoing mail to a Public folder
In SBS2003 all outgoing and incoming mail is copied to a public folder, how do I set up this on a standrad Exchange 2003 server? /PER http://www.microsoft.com/technet/prodtechnol/exchange/2003/library/journaling.mspx Lardy. "Per" <Per@discussions.microsoft.com> wrote in message news:BB8075BB-EC17-4117-B6EA-6D021FD076DC@microsoft.com... > In SBS2003 all outgoing and incoming mail is copied to a public folder, > how > do I set up this on a standrad Exchange 2003 server? > > /PER ...