Page 1 of 2 1 2 LastLast
Results 1 to 10 of 12

Thread: Macro to copy a range of data from one sheet to another workbook

  1. #1

    Macro to copy a range of data from one sheet to another workbook



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi, I am new to VBA and was wondering if someone could help me out please? I have a request to copy a range of data from one sheet in the source workbook to a sheet in the target workbook and save it down with a date stamp and version number. I've looked online but can't get any of the VBA codes to work for me without errors such as "subscript out of range" and runtime 400 errors. Please help. Thanks a lot. Here is an example of what I would like, a 3G connection won't allow me to upload excel workbooks as examples :-/.SourceBook.xls
    tab name:Source
    Data1 Data2 Data3 Data4
    A2 B2 C2 D2
    A3 B3 C3 D3
    A4 B4 C4 D4
    A5 B5 C5 D5
    A6 B6 C6 D6
    A7 B7 C7 D7
    A8 B8 C8 D8
    A9 B9 C9 D9

    BEFORE RUNNING MACRO
    TargetBook.xls
    tab name:Target
    Data1 Data2 Data3 Data4
    AFTER RUNNING MACRO
    TargetBook.xls
    tab name:Target
    Data1 Data2 Data3 Data4
    A2 B2 C2 D2
    A3 B3 C3 D3
    A4 B4 C4 D4
    A5 B5 C5 D5
    A6 B6 C6 D6
    A7 B7 C7 D7
    A8 B8 C8 D8
    A9 B9 C9 D9

  2. #2
    Acolyte patel's Avatar
    Join Date
    Feb 2014
    Location
    Italy
    Posts
    59
    Articles
    0
    Code:
    Sub Open_File_CopyRange_to_Sheet()
    SelFile = "C:\test\TargetBook.xls"
    Set Rng = Sheets("Source").Range("A2:D9")
    Workbooks.Open Filename:=SelFile
    Set wb1 = ActiveWorkbook
    Rng.Copy
    wb1.Sheets("Target").Range("A2").PasteSpecial
    'wb1.Close
    End Sub

  3. #3
    [Sub Open_File_CopyRange_to_Sheet()
    SelFile = "C:\Users\joebloggs\Documents\2000.Test\TargetBook.xls"
    Set Rng = Sheets("Source").Range("A29")
    Workbooks.Open Filename:=SelFile
    Set wb1 = ActiveWorkbook
    Rng.Copy
    wb1.Sheets("Target").Range("A2").PasteSpecial
    'wb1.Close
    End Sub[/CODE][/QUOTE]
    Thanks Patel for some reason I can't get your code to work for me, any ideas please? Also I want to copy the data to a new workbook named target.

  4. #4
    Please see a link to the file https://drive.google.com/folderview?...ng.Essentially I want to provide links in the code to copy from one to another i.e. source=sourcebook.xls, target=targetbook.xls
    Last edited by setanta; 2014-03-09 at 07:57 PM.

  5. #5
    Acolyte patel's Avatar
    Join Date
    Feb 2014
    Location
    Italy
    Posts
    59
    Articles
    0
    Code:
    Sub Open_File_CopyRange_to_Sheet()
    SelFile = "C:\Users\michfitzgerald\Documents\2000.Test\SourceBook.xlsx"
    Set wb1 = ActiveWorkbook
    Workbooks.Open Filename:=SelFile
    Sheets("Source").Range("A2:D9").Copy
    wb1.Sheets("Target").Range("A2").PasteSpecial
    End Sub

  6. #6
    Thanks for the reply Patel, however this macro does not allow a source and a target in 2 different workbooks, please advise.

  7. #7
    Acolyte patel's Avatar
    Join Date
    Feb 2014
    Location
    Italy
    Posts
    59
    Articles
    0
    i'm sorry, but I can not understand your goal

  8. #8
    Hi Patel, sorry maybe my explanation wasn't that clear. I've updated the attached excel files. Essentially I don't want to copy data between sheets. I want to copy data between the source and target excel workbooks. I have removed the target tab from the sourcebook.xls and the source tab from the targetbook.xls. I have attached a ppt slide to the google drive to show what I mean. https://drive.google.com/#folders/0B...WhFcy1mUDB1Smc. Thanks

  9. #9
    Conjurer Simi's Avatar
    Join Date
    Feb 2012
    Location
    Utah, USA
    Posts
    187
    Articles
    0
    you can do this by adding another workbook variable in the code.
    You can look at this other thread that Ken Puls help'd me out with.
    http://www.excelguru.ca/forums/showt...ht=consolidate

    in the example code above simply add the following.


    Code:
    Sub Open_File_CopyRange_to_Sheet()
    SelFile = "C:\Users\michfitzgerald\Documents\2000.Test\SourceBook.xlsx"
    Set wb1 = ActiveWorkbook
    
    '*** add the line below
    set wb2 = workbooks.open("your target file path")
    '***
    
     Workbooks.Open Filename:=SelFileSheets("Source").Range("A2:D9").Copy
    
    '*** change this to wb2
    wb2.Sheets("Target").Range("A2").PasteSpecial
    '***
    End Sub
    I'm not sure how this will work with opening 2 workbooks without closing one.
    Last edited by Simi; 2014-03-12 at 01:03 AM.

  10. #10
    Acolyte patel's Avatar
    Join Date
    Feb 2014
    Location
    Italy
    Posts
    59
    Articles
    0
    the code I attached is good, but you have to paste it only in TargetBook and in a standard module, not in ThisWorkbook module, see attached
    Attached Files Attached Files

Page 1 of 2 1 2 LastLast

Posting Permissions

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