Copy data from one sheet to the last row of another sheet

Question

I'm trying to copy the data from one sheet to the last row of another sheet.

The reason why I am doing this is because I want to consolidate the data in a sheet which is already existing and my contain already a data.

Below is my code so far which only copies again to the A2 of another sheet. What approach should I do for this:

Sub Upload()

Dim Wb1 As Workbook
Dim Wb2 As Workbook
Dim MainPage As Worksheet
Set MainPage = Sheets("Main")
Dim r As Long



Application.DisplayAlerts = False
Application.ScreenUpdating = False

Set Wb1 = ActiveWorkbook

FileToOpen = Application.GetOpenFilename _
(Title:="Please choose a File", _
filefilter:="Excel File *.xlsx (*.xlsx),")

If FileToOpen = False Then

MsgBox "No File Specified.", vbExclamation, "ERROR"

Exit Sub

Else
Set Wb2 = Workbooks.Open(Filename:=FileToOpen)

With Wb2.Sheets("ALL TICKETS (excpt Open-OnHold)")

srcLastRow = .Range("A:AJ").Find("*", SearchOrder:=xlByRows, 
SearchDirection:=xlPrevious).Row
destLastRow = Wb1.Sheets("ALL TICKETS (excpt Open-OnHold)".Range("A:AJ").Find("*", SearchOrder:=xlByRows, 
SearchDirection:=xlPrevious).Row + 1

Wb1.Sheets("ALL TICKETS (excpt Open-OnHold)").Range("A2:AJ" & 
destLastRow).Value = .Range("A2", "AJ" & srcLastRow).Value

End With

Wb2.Close

End If

End Sub

Cyril · Accepted Answer · 2018-03-15T16:16:49.330

1

You know your copied range, so then you need to know the last row of the destination sheet:

dim lr as long
With Sheets("Destination")
    lr = .cells(.rows.count,1).end(xlup).row 'assumes column 1 is contiguous
End with

You can then take your source range (will use variable SrcRng) and paste to the new sheet, into a specific cell:

SrcRng.Copy Sheets("Destination").Cells(lr+1,1) 'this line does the copy and the paste

The rest of the copied range will be filled in.

Edit1:

Hard to show the code in a comment...

Dim LRSrc as Long, LRDest as Long, SrcRng as Range
With Sheets("Source")
    LRSrc = .cells(.rows.count,1).end(xlup).row 'assumes column 1 is contiguous
    Set SrcRng = .Range("A1:AJ" & LRSrc)
End with
With Sheets("Destination")
    LRDest = .cells(.rows.count,1).end(xlup).row 'assumes column 1 is contiguous
    SrcRng.Copy .Cells(LRDest+1,1)
End with

edited Mar 15 '18 at 16:16

answered Mar 15 '18 at 15:18

Cyril

6,448
1
18
31

Thanks Cyril, will I change my srcLastRow = .Range("A:AJ").Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row to srcRange = .Range("A:AJ").Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row? – Sevpoint Mar 15 '18 at 15:28
I would recommend avoiding find for that purpose. Similar to finding the last row in the destination sheet, you should find the last row in the source sheet. lrSrc and lrDest may help keep them different. You would them have SrcRng = .Range("A1:AJ" & lrSrc) – Cyril Mar 15 '18 at 16:13
Thanks a lot Cyril! I was able to achieve what I needed. :) – Sevpoint Mar 15 '18 at 16:24

score 0 · Answer 2 · answered Mar 15 '18 at 15:19

0

Would this work for you. defining srcLastRow as below.

srcLastRow = Cells(Rows.Count, 36).End(xlUp).Row

answered Mar 15 '18 at 15:19

Apurv Pawar

424
3
11

1

Use .cells(.rows.count,36).end(xlup).row; he's already using a With statement, so don't lose track of the associated sheet! – Cyril Mar 15 '18 at 15:24

Copy data from one sheet to the last row of another sheet

2 Answers2

Linked