Copy Excel Worksheet from one workbook to another in VB

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
sporenzaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

gtglonerCommented:
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
Wayne Taylor (webtubbs)Commented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Juan VelasquezCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.