Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 6 of 6

Thread: Macro: Copy Worksheet To Another Workbook

  1. #1
    Join Date
    9th November 2006
    Posts
    103

    Macro: Copy Worksheet To Another Workbook

    Hi All, i am a beginnner in macro.
    I need to come up with a macro code to copy 2 worksheet from 2 workbooks to my current workbook. I do not know what is the code such that i can copy different worksheets from other workbooks to a currently opened workbook.

    Below is the scenerio i want to achieve:

    worksheet1 from workbook1 need to be copied and paste as a worksheet in workbook3.
    worksheet1 from workbook2 need to be copied and paste as a worksheet in workbook3.
    workbooks will eventually contain 2 worksheets which are from workbook1 & 2.

    PLs help. Thank
    Last edited by Raynon; November 9th, 2006 at 23:33.

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    9th June 2004
    Location
    England
    Posts
    1,047

    Re: To Copy Worksheets From Other Workbooks To A Currently Opened Workbook

    If you use this macro it will copy Sheet1 Workbook1 to Workbook3 etc so you will be left with your originals in Workbook1 and Workbook2.


    VB:
    Sub MoveSheets() 
        Windows("Workbook1.xls").Activate 
        Sheets("Sheet1").Select 
        Sheets("Sheet1").Copy Before:=Workbooks("Workbook3.xls").Sheets(1) 
         
        Windows("Workbook1.xls").Activate 
        Sheets("Sheet2").Select 
        Sheets("Sheet2").Copy Before:=Workbooks("Workbook3.xls").Sheets(2) 
         
        Windows("Workbook2.xls").Activate 
        Sheets("Sheet1").Select 
        Sheets("Sheet1").Copy Before:=Workbooks("Workbook3.xls").Sheets(3) 
         
        Windows("Workbook2.xls").Activate 
        Sheets("Sheet2").Select 
        Sheets("Sheet2").Copy Before:=Workbooks("Workbook3.xls").Sheets(4) 
    End Sub 
    
    

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,717

    Re: To Copy Worksheets From Other Workbooks To A Currently Opened Workbook

    Another way

    VB:
    With Workbooks("Workbook1.xls") 
        .Sheets(Array("Sheet1", "Sheet2", "Sheet3")).Copy _ 
        Before:=Workbooks("Workbook3.xls").Sheets(1) 
    End If 
    
    

  4. #4
    Join Date
    9th November 2006
    Posts
    103

    Re: To Copy Worksheets From Other Workbooks To A Currently Opened Workbook

    Quote Originally Posted by Dave Hawley
    Another way

    VB:
    With Workbooks("Workbook1.xls") 
        .Sheets(Array("Sheet1", "Sheet2", "Sheet3")).Copy _ 
        Before:=Workbooks("Workbook3.xls").Sheets(1) 
    End If 
    
    

    Hi guys thanks alot for your infomation.
    Dave, what does the underscore after the copy mean "Copy _"?

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    30th March 2011
    Posts
    14

    Re: Macro: Copy Worksheet To Another Workbook

    That's just to skip to a new line of code. it is ignored.

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    23rd April 2013
    Posts
    1

    Re: Macro: Copy Worksheet To Another Workbook

    Hi all,

    I also want to copy all sheets of the workbook to another workbook. However, I wonder about how can I code with "for" or sth. like that.

    I want to copy all sheets of workbook named "27032013" to a workbook named "MasterFile".

    And on which workbook should I write the necessary codes?

    Thanks

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Replies: 3
    Last Post: April 20th, 2007, 06:57
  2. Add Workbook And Copy Worksheet
    By spud in forum EXCEL HELP
    Replies: 4
    Last Post: February 14th, 2007, 21:12
  3. Macro to copy worksheet to another workbook
    By baoneill in forum EXCEL HELP
    Replies: 3
    Last Post: September 16th, 2006, 12:35
  4. Replies: 15
    Last Post: January 28th, 2005, 17:27

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
porno