RE: Copy last row of data and paste based on last row in another workbook

Keerthi03

New Member
Joined
Jun 14, 2020
Messages
8
Office Version
  1. 2016
Platform
  1. Windows
Hi all,

I need to copy a range of data eg: (B9: M9) till the last row of the current worksheet >Temp, and paste it in another workbook of another sheet -> "Data" based on the last row from the column B.

Tried out macro recordings and code stuff. But still, it does not work out.

Attached the sample source data screenshot for your reference.

CODE:
VBA Code:
Sub sample()

Dim LRSrc As Long, LRDest As Long, SrcRng As Range

fromPath = Sheets("Instructions").Range("B2")


Set wkb = ThisWorkbook

Set wkbFrom = Workbooks.Open(fromPath)

wkb.Sheets("Temp").Select
    With ThisWorkbook.Sheets("Temp")
    LRSrc = .Cells(.Rows.Count, 2).End(xlUp).Row
    Set SrcRng = .Range("B9:Z3000" & LRSrc)

End With
' With Sheets("Data")
With wkbFrom.Sheets("Data")
    LRDest = .Cells(.Rows.Count, 2).End(xlUp).Row
    SrcRng.copy .Cells(LRDest + 1, 2)
End With

End Sub




Please help out.
 

Attachments

  • Temp.PNG
    Temp.PNG
    53 KB · Views: 51

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
hi,
Assuming your origin workbook is book1 and destination workbook is book2 and both workbooks are open at the same time, try the following

VBA Code:
Sub copy_data()
Dim twb As Workbook, dwb As Workbook, trng As Range, drng As Range

Set twb = Workbooks("book1")
Set dwb = Workbooks("book2")

tlrow = twb.Sheets("temp").Cells(Rows.Count, 2).End(xlUp).Row
dlrow = dwb.Sheets("data").Cells(Rows.Count, 2).End(xlUp).Row

Set trng = twb.Sheets("temp").Range("b2:m" & tlrow)

If dlrow = 1 Then
    Set drng = dwb.Sheets("data").Range("b" & 1)
Else
    Set drng = dwb.Sheets("data").Range("b" & dlrow + 1)
End If

trng.Copy drng

End Sub

hth...
 
Upvote 0
By the looks of it you just need to change (assuming that you want column Z as in the code and not column M as in the question)
VBA Code:
Set SrcRng = .Range("B9:Z3000" & LRSrc)
to
VBA Code:
Set SrcRng = .Range("B9:Z" & LRSrc)
 
Upvote 0
hi,
Assuming your origin workbook is book1 and destination workbook is book2 and both workbooks are open at the same time, try the following

VBA Code:
Sub copy_data()
Dim twb As Workbook, dwb As Workbook, trng As Range, drng As Range

Set twb = Workbooks("book1")
Set dwb = Workbooks("book2")

tlrow = twb.Sheets("temp").Cells(Rows.Count, 2).End(xlUp).Row
dlrow = dwb.Sheets("data").Cells(Rows.Count, 2).End(xlUp).Row

Set trng = twb.Sheets("temp").Range("b2:m" & tlrow)

If dlrow = 1 Then
    Set drng = dwb.Sheets("data").Range("b" & 1)
Else
    Set drng = dwb.Sheets("data").Range("b" & dlrow + 1)
End If

trng.Copy drng

End Sub

hth...
Hi,

Thanks for your code and it works. But two things need to be fixed. Please check and revert back.

1) Refer screenshot, In column "H" values will be added column by column, hence that also needs to be included in the source data while copying - is that possible to do?

2) Another workbook will be opened, can that be changed or assigned to a variable ( dynamic way -> since file name will be changing with Month name) instead of workbook name


Thanks for your help in advance
 
Upvote 0
Hi,
sorry for late reply.
for your 1st question, the code copies the data starting from cell b2 all the way till lastrow of colum m, so the column H you are referring to is already included and should be copied along.

for the later part, you can select file initially and then proceed with the copy paste.
try the following.

VBA Code:
Sub copy_data()
Dim twb As Workbook, dwb As Workbook, trng As Range, drng As Range, fd As Office.FileDialog

Set fd = Application.FileDialog(msoFileDialogFilePicker)
fd.Show                                      'select destination file
fpath = fd.SelectedItems(1)
Workbooks.Open fpath             'open destination file

Set twb = Workbooks("book1.xlsm")
Set dwb = ActiveWorkbook

tlrow = twb.Sheets("temp").Cells(Rows.Count, 2).End(xlUp).Row
dlrow = dwb.Sheets("data").Cells(Rows.Count, 2).End(xlUp).Row

Set trng = twb.Sheets("temp").Range("b2:m" & tlrow)
If dlrow = 1 Then
    Set drng = dwb.Sheets("data").Range("b" & 1)
Else
    Set drng = dwb.Sheets("data").Range("b" & dlrow + 1)
End If

trng.Copy drng

With dwb
    .Save
    .Close
End With

End Sub

hth....
 
Upvote 0
Solution
Hi,
sorry for late reply.
for your 1st question, the code copies the data starting from cell b2 all the way till lastrow of colum m, so the column H you are referring to is already included and should be copied along.

for the later part, you can select file initially and then proceed with the copy paste.
try the following.

VBA Code:
Sub copy_data()
Dim twb As Workbook, dwb As Workbook, trng As Range, drng As Range, fd As Office.FileDialog

Set fd = Application.FileDialog(msoFileDialogFilePicker)
fd.Show                                      'select destination file
fpath = fd.SelectedItems(1)
Workbooks.Open fpath             'open destination file

Set twb = Workbooks("book1.xlsm")
Set dwb = ActiveWorkbook

tlrow = twb.Sheets("temp").Cells(Rows.Count, 2).End(xlUp).Row
dlrow = dwb.Sheets("data").Cells(Rows.Count, 2).End(xlUp).Row

Set trng = twb.Sheets("temp").Range("b2:m" & tlrow)
If dlrow = 1 Then
    Set drng = dwb.Sheets("data").Range("b" & 1)
Else
    Set drng = dwb.Sheets("data").Range("b" & dlrow + 1)
End If

trng.Copy drng

With dwb
    .Save
    .Close
End With

End Sub

hth....
Hi Fadee2,

Thanks for your code. Code working fine but still if column " H " alone has extra values ( as per screenshot) it is not being copied into the destination as per your code.

Anyhow, I have made reference to column H and updated the code, and its works.


Thank you so much for your help.
 
Upvote 0
you are very welcome..... glad u sorted it out... always happey to help... ?????
 
Upvote 0

Forum statistics

Threads
1,213,482
Messages
6,113,913
Members
448,532
Latest member
9Kimo3

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