VBA Code copy and paste data from on workbook into an already opened workbook.

[Reetesh]

Hello.

I'm fairly new to VBA and not sure whether this could be done or not.

I want to paste two rows data of data into an already opened workbook.

I'll try to explain what i want in a bit more with an example.

Consider a workbook "A" where data is entered manually by other people. Workbook "B" will remain open on my system. The first row in workbook "B" will have headers. I want data from the last 2 rows of workbook "A" to be copied and then pasted into workbook "B", which is already open, after inserting 2 new rows below the headers.
Suppose row 10 and row 11 are the last 2 rows in workbook "A", then these two rows should be copied and then pasted into workbook "B" at row 2 and row 3 after inserting 2 new rows at the top. Data from row 10 of workbook "A" should be pasted in row 3 of workbook "B" and row 11 from workbook "A" should be copied in row 2 of workbook "B". Workbook "B" will remain open with me only all the time and others will have access workbook "A".

I really don't know whether this can be done or not and because of that reason i was not able to come up with any VBA Code which i can present here.

Because of that reason I thought of asking here. Hoping to get some guidence from the experts here.
Thanks in advance

 

[Zot]

Even if you write code that reopen opened file, it will still work but you will be prompt that the file is already opened.

You can suppress the prompt using Application.DisplayAlerts = False if not mistaken.

 

[Reetesh]

Even if you write code that reopen opened file, it will still work but you will be prompt that the file is already opened.

Even if you write code that reopen opened file, it will still work but you will be prompt that the file is already opened.

You can suppress the prompt using Application.DisplayAlerts = False if not mistaken.

Hey Zot.

Thanks for your reply.
I think "The workbook is already open" message will pop up if both workbooks are open on my system. If workbook A is used by others and workbook B is open at my system then people who are using workbook A will get a prompt which says that workbook A is already open with someone else and it will give the option to open it as read only, right?

You can suppress the prompt using Application.DisplayAlerts = False if not mistaken.

 

[Zot]

Usually it will become read only but them you are only getting data, right?

 

[Reetesh]

Usually it will become read only but them you are only getting data, right?

Yes it will only be the data.

 

[Zot]

Try this

Sub SampleCopy()

Dim Fname As Variant
Dim eRow As Long
Dim wsA1 As Worksheet, wsB1 As Worksheet
Dim wbA As Workbook, wbB As Workbook

Application.ScreenUpdating = False
Application.DisplayAlerts = False

' Define this Workbook as wbA
Set wbB = ActiveWorkbook
' Define working sheet in wbA. Change sheet name accordingly
Set wsB1 = wbB.Sheets("Sheet1")

' Search destination Workbook
Fname = Application.GetOpenFilename(FileFilter:="Excel Files (*.xls; *.xlsx; *.xlsm; *.xlsb), *.xls; *.xlsx; *.xlsm; *.xlsb", Title:="Select a File")
If Fname = False Then Exit Sub                         'CANCEL is clicked

' Define opened Workbook as wbB while opening it.
Set wbA = Workbooks.Open(Filename:=Fname, UpdateLinks:=False, ReadOnly:=True, IgnoreReadOnlyRecommended:=True)
' Define working sheet in wbB. Change sheet name accordingly
Set wsA1 = wbA.Sheets("Sheet1")

' Find end of row in wbA. This refers to last occupied row in column A. Change column if necessary.
eRow = wsA1.Cells(Rows.Count, "A").End(xlUp).Row

' Insert 2 rows on row 2 wsB1
wsB1.Rows("2:3").EntireRow.Insert

' Copy 2 rows data from wsA1 to wsB1
wsA1.Range("A" & eRow - 1, "M" & eRow).Copy wsB1.Range("A2")
wbA.Close False

Application.ScreenUpdating = True
Application.DisplayAlerts = True

End Sub