Copy Data from 1 workbook to another workbook and paste it below

gerio24

New Member
Joined
Feb 1, 2018
Messages
3
Hi Guys, need help in copying data from 1 workbook and paste it in another workbook but in the bottom area.
[TABLE="width: 256"]
<colgroup><col width="64" span="4" style="width:48pt"> </colgroup><tbody>[TR]
[TD="class: xl64, width: 256, colspan: 4"][TABLE="width: 256"]
<colgroup><col width="64" span="4" style="width:48pt"> </colgroup><tbody>[TR]
[TD="class: xl64, width: 256, colspan: 4"]
From[/TD]
[/TR]
[TR]
[TD="class: xl65"]Branch[/TD]
[TD="class: xl65"]Sales person[/TD]
[TD="class: xl65"]Series[/TD]
[TD="class: xl66"]Date[/TD]
[/TR]
[TR]
[TD="class: xl67"]ALB[/TD]
[TD="class: xl67"]Emelito Esteban[/TD]
[TD="class: xl67, align: right"]11013[/TD]
[TD="class: xl68, align: right"]01/31/18[/TD]
[/TR]
[TR]
[TD="class: xl67"]ALB[/TD]
[TD="class: xl67"]Jan Michael Raymundo[/TD]
[TD="class: xl67, align: right"]11014[/TD]
[TD="class: xl68, align: right"]01/31/18[/TD]
[/TR]
[TR]
[TD="class: xl67"]ALB[/TD]
[TD="class: xl67"]Jan Michael Raymundo[/TD]
[TD="class: xl67, align: right"]11014[/TD]
[TD="class: xl68, align: right"]01/31/18[/TD]
[/TR]
[TR]
[TD="class: xl67"]ALB[/TD]
[TD="class: xl67"]Jan Michael Raymundo[/TD]
[TD="class: xl67, align: right"]11014[/TD]
[TD="class: xl68, align: right"]01/31/18[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]


[TABLE="width: 256"]
<colgroup><col width="64" span="4" style="width:48pt"> </colgroup><tbody>[TR]
[TD="class: xl66, width: 256, colspan: 4"]To[/TD]
[/TR]
[TR]
[TD="class: xl67"]Branch[/TD]
[TD="class: xl67"]Sales person[/TD]
[TD="class: xl67"]Series[/TD]
[TD="class: xl68"]Date[/TD]
[/TR]
[TR]
[TD="class: xl69"]CBO[/TD]
[TD="class: xl69"]Emelito Esteban[/TD]
[TD="class: xl69, align: right"]11013[/TD]
[TD="class: xl70, align: right"]01/31/18[/TD]
[/TR]
[TR]
[TD="class: xl69"]CBO[/TD]
[TD="class: xl69"]Jan Michael Raymundo[/TD]
[TD="class: xl69, align: right"]11014[/TD]
[TD="class: xl70, align: right"]01/31/18[/TD]
[/TR]
[TR]
[TD="class: xl69"]CBO[/TD]
[TD="class: xl69"]Jan Michael Raymundo[/TD]
[TD="class: xl69, align: right"]11014[/TD]
[TD="class: xl70, align: right"]01/31/18[/TD]
[/TR]
[TR]
[TD="class: xl69"]CBO[/TD]
[TD="class: xl69"]Jan Michael Raymundo[/TD]
[TD="class: xl69, align: right"]11014[/TD]
[TD="class: xl70, align: right"]01/31/18[/TD]
[/TR]
[TR]
[TD="class: xl71"]ALB[/TD]
[TD="class: xl71"]Emelito Esteban[/TD]
[TD="class: xl71, align: right"]11013[/TD]
[TD="class: xl72, align: right"]01/31/18[/TD]
[/TR]
[TR]
[TD="class: xl71"]ALB[/TD]
[TD="class: xl71"]Jan Michael Raymundo[/TD]
[TD="class: xl71, align: right"]11014[/TD]
[TD="class: xl72, align: right"]01/31/18[/TD]
[/TR]
[TR]
[TD="class: xl71"]ALB[/TD]
[TD="class: xl71"]Jan Michael Raymundo[/TD]
[TD="class: xl71, align: right"]11014[/TD]
[TD="class: xl72, align: right"]01/31/18[/TD]
[/TR]
[TR]
[TD="class: xl71"]ALB[/TD]
[TD="class: xl71"]Jan Michael Raymundo[/TD]
[TD="class: xl71, align: right"]11014[/TD]
[TD="class: xl72, align: right"]01/31/18[/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
gerio,

what is the name of each Workbook and then the name of the Worksheet in each Workbook where data is copied from and then to?
Are both Workbooks open or do I need to check to see if the 'Copy To' Workbook is Open and then open it if it is not open?
Is the data shown above in Columns A through D?
 
Upvote 0
gerio,

what is the name of each Workbook and then the name of the Worksheet in each Workbook where data is copied from and then to?
Are both Workbooks open or do I need to check to see if the 'Copy To' Workbook is Open and then open it if it is not open?
Is the data shown above in Columns A through D?



The workbook where data will copied is come from different workbook but worksheet named is fixed to "Sheet2" and the workbook where the data will be pasted is named Daily Sales and worksheet name is EBT. Thanks in advanced.
 
Upvote 0
gerio, give this code a try and let me know if you have any issues.

Code:
Option Explicit
Sub CopyComplete()
Dim CFwb As Workbook
Dim CFws As Worksheet
Dim CTwb As Workbook
Dim CTws As Worksheet
Dim CFfn As String
Dim CTfn As String
Dim CFlr As Long
Dim CTlr As Long
Dim CTRow As Long
Dim xRet As Boolean
Dim filepath As String


Set CFwb = ThisWorkbook
filepath = "C:\"


'  Check to see if Daily Sales file is open, if not Open the file
xRet = IsWorkBookOpen("Daily Sales.xlsx")
If xRet Then
    Set CTwb = Workbooks("Daily Sales.xlsx")
Else
    Set CTwb = Workbooks.Open(filepath & "Daily Sales.xlsx")
End If


Set CFws = CFwb.Sheets("Sheet2")
Set CTws = CTwb.Sheets("EBT")


'   Determine last rows in both Workbooks
    CFlr = CFws.Cells(CFws.Rows.Count, "B").End(xlUp).Row
    CTlr = CTws.Cells(Rows.Count, "B").End(xlUp).Row
    CTRow = CTlr + 1
    
'   Copy Data
CTws.Range("A" & CTRow & ":D" & (CTRow + (CFlr - 2))).Value = CFws.Range("A2:D" & CFlr).Value


End Sub


Function IsWorkBookOpen(Name As String) As Boolean
    Dim xWb As Workbook
    On Error Resume Next
    Set xWb = Application.Workbooks.Item(Name)
    IsWorkBookOpen = (Not xWb Is Nothing)
End Function
 
Upvote 0
gerio, give this code a try and let me know if you have any issues.

Code:
option explicit
sub copycomplete()
dim cfwb as workbook
dim cfws as worksheet
dim ctwb as workbook
dim ctws as worksheet
dim cffn as string
dim ctfn as string
dim cflr as long
dim ctlr as long
dim ctrow as long
dim xret as boolean
dim filepath as string


set cfwb = thisworkbook
filepath = "c:\"


'  check to see if daily sales file is open, if not open the file
xret = isworkbookopen("daily sales.xlsx")
if xret then
    set ctwb = workbooks("daily sales.xlsx")
else
    set ctwb = workbooks.open(filepath & "daily sales.xlsx")
end if


set cfws = cfwb.sheets("sheet2")
set ctws = ctwb.sheets("ebt")


'   determine last rows in both workbooks
    cflr = cfws.cells(cfws.rows.count, "b").end(xlup).row
    ctlr = ctws.cells(rows.count, "b").end(xlup).row
    ctrow = ctlr + 1
    
'   copy data
ctws.range("a" & ctrow & ":d" & (ctrow + (cflr - 2))).value = cfws.range("a2:d" & cflr).value


end sub


function isworkbookopen(name as string) as boolean
    dim xwb as workbook
    on error resume next
    set xwb = application.workbooks.item(name)
    isworkbookopen = (not xwb is nothing)
end function



thank you very much sir! :)
 
Upvote 0

Forum statistics

Threads
1,223,228
Messages
6,170,871
Members
452,363
Latest member
merico17

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top