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

**0Cool** · 11-13-2019, 11:00 AM

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.

**0Cool** · 11-13-2019, 11:01 AM

Here is the result file as well.

**Marc L** · 11-13-2019, 02:58 PM

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$, V, S$, Wb As Workbook 
        F = "ISREF('[" & SRC & "]" & SHT & "'!A1)" 
        V = Evaluate(F):  If IsError(V) Then Beep: Exit Sub 
    If Not V Then 
        S = Replace(ThisWorkbook.FullName, ThisWorkbook.Name, SRC):  If Dir(S) = "" Then Beep: Exit Sub 
        Set Wb = GetObject(S) 
        If IsError(Evaluate(F)) Then Wb.Close V: Set Wb = Nothing: Beep: 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 V: Set Wb = Nothing 
End Sub

Do you like it ? So thanks to click on bottom left star icon « ★ Add Reputation » !

**0Cool** · 11-13-2019, 09:41 PM

Hi Marc,

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

Thanks

**Marc L** · 11-13-2019, 09:46 PM

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

**0Cool** · 11-14-2019, 12:11 AM

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.

**0Cool** · 11-14-2019, 10:00 AM

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.

**Marc L** · 11-14-2019, 10:28 AM

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.

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

LinkBack

Thread Tools

Rate This Thread

Display

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

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

Hi ! Try this !

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

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

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

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] VBA Macro pull a certain cell in closed workbook and copy/paste into current workbook

need vba to copy sheets from closed workbook and paste into all closed wb's

Macro to copy and paste without overwriting existing data

Data Validation - Avoid overwriting due to copy/paste

[SOLVED] Copy data from activeworkbook paste to closed workbook

[SOLVED] Code to copy data from a closed workbook and paste in active workbook using named range.

Macro to copy over and paste data in to a work sheet from a closed workbook

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions