Results 1 to 3 of 3
  1. #1
    2 Star Lounger
    Join Date
    Apr 2015
    Posts
    153
    Thanks
    70
    Thanked 0 Times in 0 Posts

    Copy Data from One Workbook and Move it to Another Workbook

    Hi Pro's,

    This code doesn't work. I haven't been able to find out why.

    It crashes here: wbTarget.Range("A1").Select

    This code is something I am work on, but what I am hoping to accomplish is to find something that will copy data from columns A to I for all the rows on the sheet and then paste into the target file.

    Any ideas or thoughts would be appreciated.



    Sub CopyOpenItems()
    '
    ' CopyOpenItems Macro
    ' Copy open items to sheet.
    '
    '
    '
    Dim wbTarget As Workbook 'workbook where the data is to be pasted
    Dim wbThis As Workbook 'workbook from where the data is to copied
    Dim strName As String 'name of the source sheet/ target workbook

    'set to the current active workbook (the source book)
    Set wbThis = ActiveWorkbook

    'get the active sheetname of the book
    strName = ActiveSheet.Name

    'open a workbook that has same name as the sheet name
    Set wbTarget = Workbooks.Open("C:\Users\Jack\Desktop\Excel Stuff\target")

    'select cell A1 on the target book
    wbTarget.Range("A1").Select

    'clear existing values form target book
    wbTarget.Range("A1:M51").ClearContents

    'activate the source book
    wbThis.Activate

    'clear any thing on clipboard to maximize available memory
    Application.CutCopyMode = False

    'copy the range from source book
    wbThis.Range("A12:M62").Copy

    'paste the data on the target book
    wbTarget.Range("A1").PasteSpecial

    'clear any thing on clipboard to maximize available memory
    Application.CutCopyMode = False

    'save the target book
    wbTarget.Save

    'close the workbook
    wbTarget.Close

    'activate the source book again
    wbThis.Activate

    'clear memory
    Set wbTarget = Nothing
    Set wbThis = Nothing

    End Sub

  2. #2
    2 Star Lounger
    Join Date
    Aug 2011
    Posts
    112
    Thanks
    35
    Thanked 3 Times in 3 Posts
    Because you need to specify the sheet name

    Code:
    wbTarget.Sheets(1).Range("A1").Select
    Here is a cleaned up version of the code. Change the sheet name to what is appropriate for your situation.

    Sheet/Worksheet CodeNames

    You typically don't even need to use .Select when working with an object.

    Beyond the macro recorder

    Code:
    Sub CopyOpenItems()
        Dim wbTarget As Workbook    'workbook where the data is to be pasted
        Dim wbThis As Workbook    'workbook from where the data is to copied
        Dim strName As String    'name of the source sheet/ target workbook
        Set wbThis = ActiveWorkbook
        strName = ActiveSheet.Name
        Set wbTarget = Workbooks.Open("C:\Users\Jeff\Desktop\" & strName & ".xlsx")
        wbTarget.Sheets(1).Range("A1:M51").ClearContents
        wbThis.Activate
        Application.CutCopyMode = False
        wbThis.Sheets(strName).Range("A12:M62").Copy wbTarget.Sheets(1).Range("A1")
        Application.CutCopyMode = False
        wbTarget.Save
        wbTarget.Close
        wbThis.Activate
        Set wbTarget = Nothing
        Set wbThis = Nothing
    End Sub

  3. #3
    2 Star Lounger
    Join Date
    Apr 2015
    Posts
    153
    Thanks
    70
    Thanked 0 Times in 0 Posts
    Thanks jrb. I was able to get the code to work with your help.

Posting Permissions

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