?
Solved

Copy Excel Worksheet from one workbook to another in VB

Posted on 2008-01-31
3
Medium Priority
?
2,161 Views
Last Modified: 2008-03-06
would like to copy from a worksheet in one workbook to a new worksheet in another workbook using VB and Excel 2007.

Also I am using late binding.. :)

So I can already open both workbooks

My quesitons are:
- how do I add a worksheet to a workbook
- how do I copy from a worksheet in a workbook
- how do I paste from that worksheet to another worksheet in a different workbook

much appreciated
0
Comment
Question by:sporenza
3 Comments
 
LVL 17

Expert Comment

by:gtgloner
ID: 20793605
Here are the code snippets for what you are asking for:

Sheets.Add

Selection.Copy

ActiveSheet.Paste

These are just specific lines of code, of course. They need to be surrounded by other code to actually do something, here is a full example with 2 workbooks named Book1 and Book2 (to see it work, type something in cell D5 in sheet1 Book1 first):
Sub Macro1()
 
    Sheets("Sheet3").Select
    Sheets.Add
    Sheets("Sheet1").Select
    Range("D5").Select
    Selection.Copy
    Windows("Book2").Activate
    Range("C8").Select
    ActiveSheet.Paste
    
End Sub

Open in new window

0
 
LVL 48

Accepted Solution

by:
Wayne Taylor (webtubbs) earned 2000 total points
ID: 20794665
See below....
'Q - how do I add a worksheet to a workbook
xlWB1.Worksheets.Add
 
'Q - how do I copy from a worksheet in a workbook
'A - Do the copy and paste in the one action.
'    The below line copies the data from WB1, Sheet1, A1:B10 and
'    pastes to WB2, Sheet3, A1.
xlWB1.Worksheets("Sheet1").Range("A1:B10").Copy xlWB2.Worksheets("Sheet3").Range("A1")

Open in new window

0
 

Expert Comment

by:chtullu135
ID: 33637261
Hello,
I've been trying the following code, specifically the copy code but I keep getting an error on the following lind.  I've included the error message details
ws.Copy(Before:=wb.Worksheets(1))
System.Runtime.InteropServices.COMException was unhandled
  ErrorCode=-2146827284
  Message="Exception from HRESULT: 0x800A03EC"
  Source="Microsoft.Office.Interop.Excel"
  StackTrace:
       at Microsoft.Office.Interop.Excel._Worksheet.Copy(Object Before, Object After)
       at BADS.frmMultplePBTMR.btnGenerateReport_Click(Object sender, EventArgs e) in C:\Documents and Settings\jvelasqu\My Documents\Visual Studio 2008\Projects\BADS\BADS\frmMultplePBTMR.vb:line 254
       at System.Windows.Forms.Control.OnClick(EventArgs e)
       at System.Windows.Forms.Button.OnClick(EventArgs e)
       at System.Windows.Forms.Button.OnMouseUp(MouseEventArgs mevent)
       at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)
       at System.Windows.Forms.Control.WndProc(Message& m)
       at System.Windows.Forms.ButtonBase.WndProc(Message& m)
       at System.Windows.Forms.Button.WndProc(Message& m)
       at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
       at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
       at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
       at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg)
       at System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(Int32 dwComponentID, Int32 reason, Int32 pvLoopData)
       at System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context)
       at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context)
       at System.Windows.Forms.Application.Run(ApplicationContext context)
       at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.OnRun()
       at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.DoApplicationModel()
       at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.Run(String[] commandLine)
       at BADS.My.MyApplication.Main(String[] Args) in 17d14f5c-a337-4978-8281-53493378c1071.vb:line 81
       at System.AppDomain._nExecuteAssembly(Assembly assembly, String[] args)
       at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)
       at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
       at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
       at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
       at System.Threading.ThreadHelper.ThreadStart()
  InnerException: 

Open in new window

0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

588 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question