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
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
VBA Code:
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