Announcement

Collapse
No announcement yet.

Macro: Copy Worksheet To Another Workbook

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • 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 10th, 2006, 00:33.

  • #2
    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.


    Code:
    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

    Comment


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

      Another way

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

      Comment


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

        Originally posted by Dave Hawley
        Another way

        Code:
         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 _"?

        Comment


        • #5
          Re: Macro: Copy Worksheet To Another Workbook

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

          Comment


          • #6
            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

            Comment

            Working...
            X