CODEWITHSUNDEEP
vbaexcel
Sevpoint
Sevpoint

Reputation: 213

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

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

Upvotes: 0

Views: 5707

Answers (2)

Cyril
Cyril

Reputation: 6829

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

Upvotes: 1

Apurv Pawar
Apurv Pawar

Reputation: 424

Would this work for you. defining srcLastRow as below.

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

Upvotes: 0

Related Questions