Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Excel VBA > Excel VBA
|
Excel VBA Discuss using VBA for Excel programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Excel VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old May 1st, 2004, 01:45 AM
Registered User
 
Join Date: Apr 2004
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Default Copy Worksheet into another workbook

Hi all!
  I m trying to copy a worksheet in workbook A into workbook B with contain same worksheet name in workbook A. I am wondering anyone in here know how to do this in marco or vb? Please help me how to! Thanks..
  one more question but i'm not sure about this work or not.. In same workbook i had one worksheet(A) contain alots of data information. But i want to select some row in that worksheet(A) into different worksheet. Here is what i really concern about? When i move my mouse over and highline the rows that i wanted then i hit a marco or VB code it will copy those highlined into the worksheet that i wanted. Can anyone tell this work or not? IF you can would give me a sample of this? I really appreciate it..
  once again thank you for your time..:)

 
Old May 1st, 2004, 03:55 PM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 150
Thanks: 0
Thanked 0 Times in 0 Posts
Default

You can't have 2 sheets with the same name in a workbook. Therefore you'll have to either delete or rename the sheet in Wbk B. If you don't, Excell will add en extention to the copied sheet.
To copy an entire sheet, use 'Move or copy sheet...' from the 'Edit' menu.
To get the code, start the 'Record new macro' from the 'Tools' - 'Macro' menu, before you start the copying.

As to your second question I'm convinced you won't save a lot of work by using a macro. You'll still have to select what's to be copied and select a destination.
However, you can try playing with the Inputbox if you want to. VBA Help have good examples on how to use an Inputbox.
 
Old May 2nd, 2004, 02:43 AM
Registered User
 
Join Date: Apr 2004
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thank you Birger for you information.
   But can you give a sample how to code for select a range without using a range select in marco already. Because my data alway change the cell. in some quarter my data have less or more information, so that why my data information range will not constant for specific range. PLease help me.I appreciate your time.Thank you



 
Old May 2nd, 2004, 03:22 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 150
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I'm not sure I understand you correct.

Are you able to determine the range of data you want to copy, e.g. the top left and bottom right corner of data in your sheet ?
 
Old March 8th, 2007, 05:17 AM
Registered User
 
Join Date: Mar 2007
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via Yahoo to admand
Default

var xlApp, xlWB, xlSheet,f2,fso,oBook,oSheet,oBook1,oSheet1,idTmr;

  xlApp = new ActiveXObject("Excel.Application");
  fso = new ActiveXObject("Scripting.FileSystemObject");

  if(fso.FileExists("c:\\Example.XLS"))
  {
    f2=fso.GetFile("c:\\Example.XLS");
    f2.Delete();
  }
  xlApp.Visible = true;
  xlApp.DisplayAlerts=false
  xlWB = xlApp.Workbooks.Add();
  xlWB.Sheets.Add(null,xlWB.Sheets(xlWB.Sheets.Count ));
  xlWB.WorkSheets(1).Name="Redmond_Enabled";
  xlWB.WorkSheets(2).Name="Redmond_Disabled";
  xlWB.WorkSheets(3).Name="PHX_Enabled";
  xlWB.WorkSheets(4).Name="PHX_Disabled";

  xlSheet = xlWB.WorkSheets(1);
  xlSheet.Activate();

  xlSheet.Cells(1,1).Value = "job name";
  xlSheet.Cells(1,2).Value = "job ID";

  xlSheet.Cells(2,1).Value = "a";
  xlSheet.Cells(2,2).Value = "a1";

  xlSheet.Cells(3,1).Value = "b";
  xlSheet.Cells(3,2).Value = "b1";

  xlSheet.Cells(4,1).Value = "c";
  xlSheet.Cells(4,2).Value = "c1";

  xlSheet = xlWB.WorkSheets(2);

  xlSheet.Activate();
  xlSheet.Cells(1,1).Value = "job name";
  xlSheet.Cells(1,2).Value = "job ID";

  xlSheet.Cells(2,1).Value = "a";
  xlSheet.Cells(2,2).Value = "a1";

  xlSheet.Cells(3,1).Value = "b";
  xlSheet.Cells(3,2).Value = "b1";

  xlSheet.Cells(4,1).Value = "c";
  xlSheet.Cells(4,2).Value = "c1";


  var oExcelApp = new ActiveXObject("Excel.Application");
  var oWkBks = oExcelApp.Workbooks;
  oWkBks.open("c:\\Example_PHX.xlsx");

  oBook = oWkBks.Item(1);
  oSheet = oBook.Worksheets.Item(1);


  oSheet.Activate();
  oSheet.Cells.Select();

  oExcelApp.Selection.Copy();

  xlSheet = xlWB.WorkSheets(3);
  xlSheet.Activate();
  xlSheet.Paste();


  oBook = oWkBks.Item(2);
  oSheet = oBook.Worksheets.Item(2);


  oSheet.Activate();
  oSheet.Cells.Select();

  oExcelApp.Selection.Copy();

  xlSheet = xlWB.WorkSheets(4);
  xlSheet.Activate();
  xlSheet.Paste();

  xlSheet.SaveAS("C:\\Example.XLSx");
  xlApp.Quit();
  xlApp = null;
  CollectGarbage(1500);

After copying 1st worksheet to the 3rd worksheet,it throws Exception..Please suggest where I am doing wrong...Is there any problmem of garbage collector

Please reply me to [email protected] or [email protected] if possible..its urgent!!!



var xlApp, xlWB, xlSheet,f2,fso,oBook,oSheet,oBook1,oSheet1,idTmr;
  
  xlApp = new ActiveXObject("Excel.Application");
  fso = new ActiveXObject("Scripting.FileSystemObject");
 
  if(fso.FileExists("c:\\Example.XLS" ))
  {
    f2=fso.GetFile("c:\\Exampl e.XLS");
    f2.Delete();
  }
  xlApp.Visible = true;
  xlApp.DisplayAlerts=false
  xlWB = xlApp.Workbooks.Add();
  xlWB.Sheets.Add(null,xlWB.Sheets(xlWB. Sheets.Count));
  xlWB.WorkSheets(1).Name="Redmond_Enabl ed";
  xlWB.WorkSheets(2).Name="Redmond_Disab led";
  xlWB.WorkSheets(3).Name="PHX_Enable d";
  xlWB.WorkSheets(4).Name="PHX_Disabled" ;
  
  xlSheet = xlWB.WorkSheets(1);
  xlSheet.Activate();
  
  xlSheet.Cells(1,1).Value = "job name";
  xlSheet.Cells(1,2).Value = "job ID";
  
  xlSheet.Cells(2,1).Value = "a";
  xlSheet.Cells(2,2).Value = "a1";

  xlSheet.Cells(3,1).Value = "b";
  xlSheet.Cells(3,2).Value = "b1";

  xlSheet.Cells(4,1).Value = "c";
  xlSheet.Cells(4,2).Value = "c1";
  
  xlSheet = xlWB.WorkSheets(2);
 
  xlSheet.Activate();
  xlSheet.Cells(1,1).Value = "job name";
  xlSheet.Cells(1,2).Value = "job ID";
  
  xlSheet.Cells(2,1).Value = "a";
  xlSheet.Cells(2,2).Value = "a1";

  xlSheet.Cells(3,1).Value = "b";
  xlSheet.Cells(3,2).Value = "b1";

  xlSheet.Cells(4,1).Value = "c";
  xlSheet.Cells(4,2).Value = "c1";

  
  var oExcelApp = new ActiveXObject("Excel.Application");
  var oWkBks = oExcelApp.Workbooks;
  oWkBks.open("c:\\Example_PHX.xlsx") ;
 
  oBook  = oWkBks.Item(1);
  oSheet = oBook.Worksheets.Item(1);

    
  oSheet.Activate();
  oSheet.Cells.Select();

  oExcelApp.Selection.Copy(); &nbsp ; 
  
  xlSheet = xlWB.WorkSheets(3);
  xlSheet.Activate();
  xlSheet.Paste();
  

  oBook  = oWkBks.Item(2);
  oSheet = oBook.Worksheets.Item(2);

    
  oSheet.Activate();
  oSheet.Cells.Select();

  oExcelApp.Selection.Copy(); &nbsp ; 
  
  xlSheet = xlWB.WorkSheets(4);
  xlSheet.Activate();
  xlSheet.Paste();

  xlSheet.SaveAS("C:\\Example.XLSx");
  xlApp.Quit();
  xlApp = null;
  CollectGarbage(1500);

After copying 1st worksheet to the 3rd worksheet,it throws Exception..Please suggest where I am doing wrong...Is there any problmem of garbage collector

Please reply me to [email protected] or [email protected] if possible..its urgent!!!





Similar Threads
Thread Thread Starter Forum Replies Last Post
Copy worksheet to another workbook and rename it Probleminfinity Excel VBA 1 August 19th, 2007 11:38 PM
Copy a worksheet range to a new workbook cej2583 Excel VBA 2 March 14th, 2006 11:55 PM
Copy workbook contents into new one crmpicco Excel VBA 0 May 17th, 2005 05:10 AM
Copy a Worksheet as HTML pakman Excel VBA 3 April 14th, 2005 11:53 AM
Find Unknown Worksheet Names in an Excel Workbook bcarmen VB.NET 2002/2003 Basics 1 March 3rd, 2005 12:20 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.