none
Macro - Copy data of one sheet from one Workbook to ANOTHER Workbook RRS feed

  • Question

  • Hi there,

    I am very new to MACRO but I have to use it with the following purpose:

    1) To create a new workbook with a determined cell data which is B1 (I can achieve this)

    2) To copy an entire sheet called "raw" from Workbook "a" to Workbook "n", where "n" is created through step1

    The following is my code:

    Private Sub CommandButton1_Click()
    Application.ScreenUpdating = False
    Dim n As String
    n = ThisWorkbook.Path & "\" & "Monthly Highlights for " & Sheets("Output").Range("B1").Value & ".xlsx"
    Workbooks.Add
    ActiveWorkbook.SaveAs n

    Dim a As String
    a = Range("G10") & "\" & Range("H13")  <---G10 and H13 contains data that related to the path of my files

        Dim sourceSheet As Worksheet
        Dim destSheet As Worksheet
        '' copy from the source
        Workbooks.Open Filename:=a
        Set sourceSheet = Worksheets("raw")
        sourceSheet.Activate
        sourceSheet.Cells.Select
        Selection.Copy
               
        '' paste to the destination
        Workbooks.Open Filename:=n
        Set destSheet = Worksheets("Sheet1")
        destSheet.Activate
        destSheet.Cells.Select
        ActiveSheet.Paste  <------------ The problem appears HERE! <-- it says, "Paste method of Worksheet class failed (I have tried "Selection.Paste" but it doesn't work.. I tried "ActiveSheet.Paste Link:True" and I failed as well)

        '' save & close
        ActiveWorkbook.Save
        ActiveWorkbook.Close
        Application.ScreenUpdating = True
    End Sub

    Anyway, the ultimate goal for me is to copy one sheet from Workbook a to Workbook n.
    And I have to rename the copied sheet in Workbook n as "raw" but not "Sheet1" while this is not yet solved by me.
    It would be great if anyone can help solve this as well.

    Thanks in advance!


    • Edited by Jackynck Wednesday, June 20, 2012 3:07 AM
    Wednesday, June 20, 2012 3:06 AM

Answers

  • Perhaps one of the workbooks is .xls and the other .xlsx? If so, the number of rows is different.

    Try this version:

    Private Sub CommandButton1_Click()
        Dim n As String
        Dim a As String
        Dim sourceBook As Workbook
        Dim destBook As Workbook
        Dim sourceSheet As Worksheet
        Dim destSheet As Worksheet
    
        Application.ScreenUpdating = False
        n = ThisWorkbook.Path & "\" & "Monthly Highlights for " & _
            ThisWorkbook.Worksheets("Output").Range("B1").Value & ".xlsx"
        a = Range("G10") & "\" & Range("H13")
        ' open source
        Set sourceBook = Workbooks.Open(Filename:=a)
        Set sourceSheet = sourceBook.Worksheets("raw")
        ' create destination
        Set destBook = Workbooks.Add(xlWBATWorksheet)
        Set destSheet = destBook.Worksheets(1)
        destSheet.Name = "raw"
        ' copy source to destination
        sourceSheet.UsedRange.Copy Destination:=destSheet.Range("A1")
        Application.CutCopyMode = False
        ' save & close
        destBook.SaveAs n
        destBook.Close
        Application.ScreenUpdating = True
    End Sub


    Regards, Hans Vogelaar

    • Marked as answer by Jackynck Wednesday, June 20, 2012 6:01 AM
    Wednesday, June 20, 2012 5:33 AM

All replies

  • Perhaps one of the workbooks is .xls and the other .xlsx? If so, the number of rows is different.

    Try this version:

    Private Sub CommandButton1_Click()
        Dim n As String
        Dim a As String
        Dim sourceBook As Workbook
        Dim destBook As Workbook
        Dim sourceSheet As Worksheet
        Dim destSheet As Worksheet
    
        Application.ScreenUpdating = False
        n = ThisWorkbook.Path & "\" & "Monthly Highlights for " & _
            ThisWorkbook.Worksheets("Output").Range("B1").Value & ".xlsx"
        a = Range("G10") & "\" & Range("H13")
        ' open source
        Set sourceBook = Workbooks.Open(Filename:=a)
        Set sourceSheet = sourceBook.Worksheets("raw")
        ' create destination
        Set destBook = Workbooks.Add(xlWBATWorksheet)
        Set destSheet = destBook.Worksheets(1)
        destSheet.Name = "raw"
        ' copy source to destination
        sourceSheet.UsedRange.Copy Destination:=destSheet.Range("A1")
        Application.CutCopyMode = False
        ' save & close
        destBook.SaveAs n
        destBook.Close
        Application.ScreenUpdating = True
    End Sub


    Regards, Hans Vogelaar

    • Marked as answer by Jackynck Wednesday, June 20, 2012 6:01 AM
    Wednesday, June 20, 2012 5:33 AM
  • This works!! Thanks a lot!!

    Wednesday, June 20, 2012 6:02 AM
  • One more thing... would be great if you can advise as well:

    If I still have some files to be copied to n.. like I still have workbook b.sheet("raw") and workbook c.sheet("raw") to copy to workbook n as sheet2(would like to name it as "raw2") and sheet3(and this would be "raw3"), what is the corresponding code?
    P.S. b and c would be still same as a, which contains the Range() and "\" ( I mean they are some paths as well)

    Thank you so much !

    Wednesday, June 20, 2012 6:10 AM