ok thank you..
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 meCode
- 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
- 'End Sub
- End If
- Application.ScreenUpdating = True
- End Sub
Hi i have included (.Offset(1)) before .copy. Now it,s working fine..
Thank you so much for your kind time and help..
Yes it,s working thank you but it is copying header too..i don,t want header to be included..i need only data below header.
For destination they are on same position but from source position of header changes. That,s why i want to search for header and then copy it,s value to main file
I need a VBA code for a button which when clicked browse for other excel file, search for specific sheet named “Farmer History” in it. In this sheet it looks for A1 complete row and search heading “Crop Area” and copy this column data to main file(where button embedded) in sheet named “Berkhund” at address F3.
The same to be done for other 2 columns too i.e
Looks for “Target Qty” in first row at same sheet “Farmer History” and paste in main file sheet “Berkhund” at address R3
Looks for “Commulative Sold” in first row at same sheet “Farmer History” and paste in main file sheet “Berkhund” at address S13