I need to copy data from one sheet to another by following these conditions: 1) User should select/browse for a file, from the opened file, it copies data from column A to Column D from sheet1 2) Column A has unique numbers while column C & D has duplication, it should ignore duplicate.it copies only unique numbers in Column A and their respective data in column B, C & D 3) It paste the Data in Dashboard file(main file) in sheet RawData at column AH to AK by looking into last used row Image: as shown in image, it should copy the highlighted data and ignore duplication
I have written below code but it,s not according to my requirement, please help me
Dashboard - Copy.xlsmAnalytical_Report_Pivot (3) (2).xlsmDashboard - Copy.xlsmAnalytical_Report_Pivot (3) (2).xlsm
Private Sub copy()
Dim wsCopy As Worksheet
Dim wsDest As Worksheet
Dim lCopyLastRow As Long
Dim lDestLastRow As Long
Dim FileToOpen As Variant
Dim OpenBook As Workbook
Application.ScreenUpdating = False
FileToOpen = Application.GetOpenFilename(Title:="Browse for Berkhund File & Import",
FileFilter:="Excel Files (*.xls*),*xls*")
If FileToOpen <> False Then
Set OpenBook = Application.Workbooks.Open(FileToOpen)
'Set variables for copy and destination sheets
Set wsCopy = OpenBook.Sheets(1)
Set wsDest = ThisWorkbook.Worksheets(1)
'1. Find last used row in the copy range based on data in column A
lCopyLastRow = wsCopy.Cells(wsCopy.Rows.Count, "A").End(xlUp).Row
'2. Find first blank row in the destination range based on data in column AH
'Offset property moves down 1 row
lDestLastRow = wsDest.Cells(wsDest.Rows.Count, "AH").End(xlUp).Offset(1).Row
'3. Copy & Paste Data
wsCopy.Range("A2:D" & lCopyLastRow).copy _
wsDest.Range("AH" & lDestLastRow)
OpenBook.Close False
'Optional - Select the destination sheet
wsDest.Activate
'End Sub
End If
Application.ScreenUpdating = True
Sheets("RawData").Activate
End Sub