+ Reply to Thread
Results 1 to 8 of 8

Copy data from a closed workbook and paste while create a new row instead of overwriting..

  1. #1
    Registered User
    Join Date
    01-11-2019
    Location
    India
    MS-Off Ver
    2007, 2010, 2019
    Posts
    37

    Copy data from a closed workbook and paste while create a new row instead of overwriting..

    Hi everyone,

    How do I copy data from a closed workbook and paste it on a open workbook(where the macro is) while pasting the copied values in a new row and not deleting or overwriting the data present below that.

    For example, here is how the source file looks:

    src.png

    the blank result file looks like:

    res.png

    wanted result:

    exp.png

    Code I've done:
    Please Login or Register  to view this content.
    which gives the below results:

    wrg.png

    Pls help.

    NOTE: I also want to get the data ranges dynamically instead of hardcoding(like "B3:B12", "C3:C12" etc) as the data range for different source file may vary.
    Attached Files Attached Files

  2. #2
    Registered User
    Join Date
    01-11-2019
    Location
    India
    MS-Off Ver
    2007, 2010, 2019
    Posts
    37

    Re: Copy data from a closed workbook and paste while create a new row instead of overwriti

    Here is the result file as well.
    Attached Files Attached Files

  3. #3
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    4,029

    Cool Hi ! Try this !


    According to your attachment a beginner starter demonstration you must paste to the (DEFAULT) worksheet module :

    PHP Code: 
    Sub Demo1()
      Const 
    SRC "source.xlsx"SHT "Sheet1"
        
    Dim F$, VS$, Wb As Workbook
            F 
    "ISREF('[" SRC "]" SHT "'!A1)"
            
    Evaluate(F):  If IsError(VThen Beep: Exit Sub
        
    If Not V Then
            S 
    Replace(ThisWorkbook.FullNameThisWorkbook.NameSRC):  If Dir(S) = "" Then Beep: Exit Sub
            Set Wb 
    GetObject(S)
            If 
    IsError(Evaluate(F)) Then Wb.Close VSet Wb NothingBeep: Exit Sub
        End 
    If
        
    With Workbooks(SRC).Sheets(SHT).[B1].CurrentRegion.Rows
            
    If .Count 2 Then
                    Application
    .ScreenUpdating False
                    
    If .Count 3 Then [C4:F4].Resize(.Count 3).Insert xlDown
                With 
    .Item("3:" & .Count).Columns
                    Union
    (.Item("A:B"), .Item(5)).Copy [C4]
                
    End With
                    Application
    .ScreenUpdating True
            End 
    If
        
    End With
            
    If Not V Then Wb.Close VSet Wb Nothing
    End Sub 
    Do you like it ? So thanks to click on bottom left star icon Add Reputation !
    Last edited by Marc L; 11-13-2019 at 03:41 PM. Reason: optimization

  4. #4
    Registered User
    Join Date
    01-11-2019
    Location
    India
    MS-Off Ver
    2007, 2010, 2019
    Posts
    37

    Re: Copy data from a closed workbook and paste while create a new row instead of overwriti

    Hi Marc,

    I don't know PHP. Can't this be done using VBA only?

    Thanks

  5. #5
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    4,029

    Arrow


    Oh you never have seen any VBA code as it's a VBA procedure

  6. #6
    Registered User
    Join Date
    01-11-2019
    Location
    India
    MS-Off Ver
    2007, 2010, 2019
    Posts
    37

    Re: Copy data from a closed workbook and paste while create a new row instead of overwriti

    I'm new to VBA, so I don't know a lot about it. In your code there are quite a few things I did not understand that is why I posted my last reply.

    Anyways, thanks for your help.

  7. #7
    Registered User
    Join Date
    01-11-2019
    Location
    India
    MS-Off Ver
    2007, 2010, 2019
    Posts
    37

    Re: Copy data from a closed workbook and paste while create a new row instead of overwriti

    Hi Marc,

    Can you explain your code a little (maybe add some comments in the code to show what is going on which line of code)?

    I would really appreciate it.

    Thanks.

  8. #8
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    4,029

    Arrow

    Except ISREF Excel basics worksheet function used to check if the expected sheet exists in the source workbook,
    all is VBA basics and yet in the VBA inner help so just placing the text cursor on a statement and hitting F1 key

    The procedure checks source workbook & sheet exist (open workbook if necessary), check source rows,
    insert rows in the destination if necessary, copy data
    some beginner level copy achieved startin' with the help of the Excel inner Macro Recorder.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1