Macro to copy data from one file to another in next open row
Page 1 of 2 12 LastLast
Results 1 to 10 of 14

Thread: Macro to copy data from one file to another in next open row
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Jan 2018
    Posts
    50
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Macro to copy data from one file to another in next open row

    I need some macro help. Please see my explanation below of how I want the macro to work:

    Run macro in working file --> copies a row of data --> opens another file from specified location --> copies the row of data to the next open row for designated amount of columns --> saves the file --> closes the file and returns to the working file

    The data would be pasted in a row for so many columns and the remaining columns in the same row would have formulas that will calculate data pasted. Not sure if this makes a difference, but wanted to add the information just in case.

    Any feedback would be greatly appreciated.

    Thanks,
    Brian

  2. #2
    Board Regular
    Join Date
    Nov 2008
    Location
    Netherlands
    Posts
    3,402
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Macro to copy data from one file to another in next open row

    Brian, do you want to try and code it yourself? You can find plenty examples for each step.

    Steps:
    1. Define which row needs to be copied
    2. Open the target file
    3. Get the next open row in the targetfile (and sheet). Easiest is by going up from the last row
    4. Set the range (row & columns) where the data needs to goto and
    5. set the values by using the DestionationSheet.Range("Ax:Hx").value = SourceSheet.Range("Ay:Hy").value
    6. Close the targetfile with save


    The copy/paste construction is quite slow. The above method is very fast.

    Let me know if you need more help on this
    Short Guide to Better VBA - Link: https://www.mrexcel.com/forum/showthread.php?t=712119

    Please use code tags around your code:
    [Code] Your code here... [/Code]




    Engelse lessen, persoonlijk en doelgericht. Dutch tuition tailor-made for you. https://Ennef.nl

    Wearable for people with panic attacks: sidjup https://sidjup.com

  3. #3
    New Member
    Join Date
    Jan 2018
    Posts
    50
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Macro to copy data from one file to another in next open row

    Unfortunately, I do not know how to code. I can modify code to capture where to grab from and where to paste to etc, but can't actually write code.

    I appreciate any help I can get.

    Thanks,
    Brian

  4. #4
    Board Regular
    Join Date
    Nov 2008
    Location
    Netherlands
    Posts
    3,402
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Macro to copy data from one file to another in next open row

    OK in that case I will need more information.
    1. How do I reckognise which row to copy (source)? Position, or keyword, or selected, something else?
    2. Is it just one row to copy?
    3. Which columns are to be copied?
    4. In the target file, is there a specific sheet to copy to?
    5. Is it always the same target file?
    Short Guide to Better VBA - Link: https://www.mrexcel.com/forum/showthread.php?t=712119

    Please use code tags around your code:
    [Code] Your code here... [/Code]




    Engelse lessen, persoonlijk en doelgericht. Dutch tuition tailor-made for you. https://Ennef.nl

    Wearable for people with panic attacks: sidjup https://sidjup.com

  5. #5
    New Member
    Join Date
    Jan 2018
    Posts
    50
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Macro to copy data from one file to another in next open row

    Again - I appreciate your help. Below are the answers to your question:

    1) Data in A101:AS101 will be copied. I can name it "copydata"
    2) Yes - this row will always be the row copied
    3) answered in item 1 above - A101:AS101
    4) specific sheet will be called "Cut & Etch" in the target file - copied data will be pasted in columns B:AU in the next open row of the target file
    5) Target file will always be the same

    Please let me know if you need any further information.

  6. #6
    Board Regular
    Join Date
    Nov 2008
    Location
    Netherlands
    Posts
    3,402
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Macro to copy data from one file to another in next open row

    Forgot one thing, What is the name of the target file, and where is it located? in the same directory as the working file?
    Short Guide to Better VBA - Link: https://www.mrexcel.com/forum/showthread.php?t=712119

    Please use code tags around your code:
    [Code] Your code here... [/Code]




    Engelse lessen, persoonlijk en doelgericht. Dutch tuition tailor-made for you. https://Ennef.nl

    Wearable for people with panic attacks: sidjup https://sidjup.com

  7. #7
    Board Regular
    Join Date
    Nov 2008
    Location
    Netherlands
    Posts
    3,402
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Macro to copy data from one file to another in next open row

    Code:
    Option Explicit
    
    '1) Data in A101:AS101 will be copied. I can name it "copydata"
    '2) Yes - this row will always be the row copied
    '3) answered in item 1 above - A101:AS101
    '4) specific sheet will be called "Cut & Etch" in the target file - copied data will be pasted in columns B:AU in the next open row of the target file
    '5) Target file will always be the same
    
    
    Sub Copy2Target()
        Dim lR As Long
        Dim rInp As Range
        Dim wbTarget As Workbook, wsTarget As Worksheet
        Dim sPath As String, sTargetFile As String
        
        sPath = ThisWorkbook.Path   '<<<< Assuming in same directory as source book, else put in path
        sTargetFile = "target.xlsx" '<<<< change as required
        
        Set rInp = ThisWorkbook.Sheets("CopyData").Range("A101:AAS101")
        
        'Open the target file
        Set wbTarget = Workbooks.Open(Filename:=sPath & "/" & sTargetFile, ReadOnly:=False)
        
        With wbTarget
            With .Sheets("Cut & Etch")
                ' Now find next empty row
                lR = .Cells(.Rows.Count, 2).End(xlUp).Row + 1
                
                'and copy the data
                .Range("B" & lR & ":AT" & lR).Value = rInp.Value
            End With
            .Save
            .Close
        End With
    End Sub
    Short Guide to Better VBA - Link: https://www.mrexcel.com/forum/showthread.php?t=712119

    Please use code tags around your code:
    [Code] Your code here... [/Code]




    Engelse lessen, persoonlijk en doelgericht. Dutch tuition tailor-made for you. https://Ennef.nl

    Wearable for people with panic attacks: sidjup https://sidjup.com

  8. #8
    New Member
    Join Date
    Jan 2018
    Posts
    50
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Macro to copy data from one file to another in next open row

    Thank you for your help. My macro is stopping at this portion of the code shown below. What am I suppose to put in this part of the code

    The working sheet that I am completing is on my c:\documents\my folder
    The file I am saving too is on my c:\documents\my folder

    Do I need to put these targets in somewhere in this code?

    I appreciate your help and feedback.

    Set wbTarget = Workbooks.Open(Filename:=sPath & "/" & sTargetFile, ReadOnly:=False)

  9. #9
    Board Regular
    Join Date
    Nov 2008
    Location
    Netherlands
    Posts
    3,402
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Macro to copy data from one file to another in next open row

    Ah, sorry. In that line change the "/" to a "\"
    Last edited by sijpie; Jul 24th, 2019 at 02:47 AM.
    Short Guide to Better VBA - Link: https://www.mrexcel.com/forum/showthread.php?t=712119

    Please use code tags around your code:
    [Code] Your code here... [/Code]




    Engelse lessen, persoonlijk en doelgericht. Dutch tuition tailor-made for you. https://Ennef.nl

    Wearable for people with panic attacks: sidjup https://sidjup.com

  10. #10
    Board Regular
    Join Date
    Nov 2008
    Location
    Netherlands
    Posts
    3,402
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Macro to copy data from one file to another in next open row

    Also read through the code in your VBA editor and then look for the lines at the top where the comments start with <<<< .
    Check the path and filenames there and modify with the correct ones.
    Short Guide to Better VBA - Link: https://www.mrexcel.com/forum/showthread.php?t=712119

    Please use code tags around your code:
    [Code] Your code here... [/Code]




    Engelse lessen, persoonlijk en doelgericht. Dutch tuition tailor-made for you. https://Ennef.nl

    Wearable for people with panic attacks: sidjup https://sidjup.com

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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