1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Copy and paste between 2 different files,located in different place

Discussion in 'VBA Macros' started by Leonardo1234, Jul 29, 2018.

  1. Leonardo1234

    Leonardo1234 Member

    Messages:
    100
    copy data from this location C:\Users\user\Desktop\Stock Market\Sholtan\1.xlsm
    paste data to this location C:\Users\user\Desktop\Stock Market\Sholtan\Night.xlsb
    macro will be placed in Night.xlsb
    we have to copy complete sheet of sheet name= 1 from 1.xlsm
    we have to paste that data in sheet name = Sheet1 in Night.xlsb
  2. vletm

    vletm Excel Ninja

    Messages:
    4,152
    Leonardo1234
    Code (vb):

    Workbooks(from_file).Sheets("1").UsedRange.Copy Destination:=Workbooks(to_file).Sheets("Sheet1").Range("A1")
     


    Of course, there would be many 'if'-cases ...
  3. Leonardo1234

    Leonardo1234 Member

    Messages:
    100
    Sub test()
    Workbooks(from_file).Sheets("1").UsedRange.CopyDestination:=Workbooks(to_file).Sheets("Sheet1").Range("A1")
    End Sub
  4. Leonardo1234

    Leonardo1234 Member

    Messages:
    100
    this way i have to mentioned this vba code
    am i right vletm sir?
  5. Leonardo1234

    Leonardo1234 Member

    Messages:
    100
    Code (vb):
    Sub test()
    Workbooks(from_file).Sheets("1").UsedRange.CopyDestination:=Workbooks(to_file).Sheets("Sheet1").Range("A1")
    End Sub
    This way i have to right this vba code am i right vletm Sir?
  6. vletm

    vletm Excel Ninja

    Messages:
    4,152
    Leonardo1234
    As You wrote
    copy data from this location C:\Users\user\Desktop\Stock Market\Sholtan\1.xlsm
    paste data to this location C:\Users\user\Desktop\Stock Market\Sholtan\Night.xlsb

    from_file is Your copy data from this location and
    to_file is Your paste data to this location.
    Okay?
  7. Leonardo1234

    Leonardo1234 Member

    Messages:
    100
    vletm sir plz provide me a complete macro code
    i can't make the code from the details u have given plz provide me a complete macro (code)
  8. Belleke

    Belleke Active Member

    Messages:
    458
    This is what vletm means
    Code (vb):
    Sub vletm()
    Workbooks("C:\Users\user\Desktop\Stock Market\Sholtan\1.xlsm ").Sheets("1").UsedRange.Copy _
    Destination:=Workbooks("C:\Users\user\Desktop\Stock Market\Sholtan\Night.xlsb").Sheets("Sheet1").Range("A1")
    End Sub
  9. Chirag R Raval

    Chirag R Raval Member

    Messages:
    638
    Dear All,

    Sorry for hacke this thread...but just to learning purpose.

    Is it can be offline process?
    Code reside in any file or newly created blank file?
    Or both file closed & run from (through) macro in personnel.xlsb?
    Is source file opened and targeted file closed?
    Both workbooks must be opened when run this process ?
    What happen when target file already have some data on it? How can make dynamic?
    Can be more possibility?

    Regards,

    Chirag Raval
  10. vletm

    vletm Excel Ninja

    Messages:
    4,152
    Chirag R Raval
    What would You mean about 'offline process'?
    Both files have to be created and open.
    #5 Reply can use with those variables.
    This would overwrite to target file.
    'dynamic' ... before 'paste' gotta find next empty row or if 'paste' over then gotta find that range.
    Many things are possible ... gotta know what would like to happen.
    This case was from file to other file ...
    Why all Your macros should be in personnel.xlsb?
    Chirag R Raval likes this.
  11. Leonardo1234

    Leonardo1234 Member

    Messages:
    100
    Belleke I am getting error with the code that u have given
    Plz run the macro and see the error
  12. Leonardo1234

    Leonardo1234 Member

    Messages:
    100
    Hi chirag, I use xlsb file bcoz it takes less space and its best
  13. vletm

    vletm Excel Ninja

    Messages:
    4,152
    Leonardo1234
    That same macro with those settings can run only if have those files in those folders.
    What error it gives?
    Are Your path and filenames as written?
  14. Leonardo1234

    Leonardo1234 Member

    Messages:
    100
    The given code will be placed in Night file and we have to open file 1 then we have to copy the data from 1 file and then paste it to Night file and then close 1 file
    All this i have to do by vba
  15. Leonardo1234

    Leonardo1234 Member

    Messages:
    100
    This is a complete description Sir
    Sorry for the incomplete description in future this will not be repeated
  16. vletm

    vletm Excel Ninja

    Messages:
    4,152
    Leonardo1234
    There were few questions ...
    Previous code has one extra space, one reason for something.
    Next sample code worked here after You will copy it to correct place.
    I didn't add any error checks ....
    Code (vb):

    Sub Do_It()
        from_file = "C:\Users\user\Desktop\Stock Market\Sholtan\1.xlsm"
        to_file = ThisWorkbook.Name
        Workbooks.Open from_file
        Workbooks(from_file).Sheets("1").UsedRange.Copy _
            Destination:=Workbooks(to_file).Sheets("Sheet1").Range("A1")[/SIZE]
        Workbooks(from_file).Close savechanges = False
    End Sub
     
  17. Leonardo1234

    Leonardo1234 Member

    Messages:
    100
    vletm Sir after runing this code i am getting error (syntax error)
    plz run the macro and see
  18. vletm

    vletm Excel Ninja

    Messages:
    4,152
    Leonardo1234
    I've run that before and again ... of course with my settings and files.
    ( = I do not have those paths nor files here! )
    ... and I didn't get ANY errors!

    Where did You copy that code?
    a) Send screenshot from that error here.
    b) Send screenshot from You code.
    c) Send those files here
  19. Leonardo1234

    Leonardo1234 Member

    Messages:
    100
    see the files

    Attached Files:

  20. vletm

    vletm Excel Ninja

    Messages:
    4,152
    Leonardo1234
    I see .. that You haven't done so much VBA ...
    There were left for some mystery reason few extra marks ... and so on.
    >> code is in Sheet1 code

    Attached Files:

  21. Leonardo1234

    Leonardo1234 Member

    Messages:
    100
    i am getting error
    plz check the code once again
  22. Leonardo1234

    Leonardo1234 Member

    Messages:
    100
    run time error 9
    subscript out of range
  23. vletm

    vletm Excel Ninja

    Messages:
    4,152
    Always try to write WHERE
    Sometimes, it's challenge to find where is cannot use same settings ...

    Attached Files:

  24. Leonardo1234

    Leonardo1234 Member

    Messages:
    100
    Thnx vletm for giving ur precious time and great support to this post
    Problem Solved

Share This Page