Copy and paste selected data without duplicates

[tmdgus]

Hi, I am new to VBA and need some help with some of the procedures I would like to automate.
I have two excel files to work on - employee and contract data.
Basically, I need to find employees who end their contracts in the current month that I open the file. (For example, if I open the file in August, I need to find employee data who are ending their contract in August). Then, I need to copy the selected data and paste them onto another file, called contract data.

I have completed both identifying and copy+paste, but it doesn't meet some of the requirements I want to apply in my file. I will put my code below.
The code below highlights rows of employee data whose employment contract expires within a month.

Sub HighlightCalc()
   Dim i As Range
   For Each i In Range("K2:K65565") '// K is the column that contains contract expiry date
      If IsDate(i) Then
         If Month(i) = Month(Now) Then
            Rows(i.Row).Interior.Color = RGB(255,255,91)
            Rows(i.Row).Font.Bold = True
         End If
      End If
   Next i
End Sub

The code below is when I transfer a selected row to another file called Contract Data

Private Sub CommandButton1_Click()
Dim wb As Workbook
ThisWorkbook.Worksheets("Sheet1").Rows(2).Select    '//Question 1: How do I select rows that I have previously highlighted above?
Selection.Copy

Set wb = Workbooks.Open("file address, didn't put the real address")
wb.Worksheets("Sheet1").Activate
lastrow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
ActiveSheet.Cells(lastrow + 1, 1).Select
ActiveSheet.Paste.     '//Question 2: Is there any way that I can only copy "new" data and paste them? Every time I run this Macro, the same data is pasted.
'//Any suggestions on copying data to other files without duplicates?
'//Question 3: Can I only copy selected cells and paste them onto the selected places?
ActiveWorkbook.Save
ActiveWorkbook.Close savechanges = True

Set wb = Nothing
ThisWorkbook.Worksheets("Sheet1").Activate
ThisWorkbook.Worksheets("Sheet1").Cells(1,1).Select
Application.CutCopyMode = False

I have included some questions in the code.
To explain them further, when I copy the data from the Employee file to the Contract File, I do not want to copy the exact same data multiple times.
There is a column that I made for a unique identifier(column A), so I guess there might be a way to do this with it, but I am still clueless about how to do so, as I am not familiar with VBA yet.
I also do not know how to select the highlighted data to copy them to the Contract file.
Most importantly, when copying data, I only want some data to be copied, not all. (For example, I want to copy the name of employees and their positions, but not their addresses or phone number).
When pasting data, I want to paste them onto their allocated cell area.

Can anyone please help me with my VBA? I am new to this and need help ASAP

 

[mumps]

It would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach a screenshot (not a picture) of your sheet. Alternately, you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary).

 

[tmdgus]

Hi, Thanks for your reply!
Here's the link to my file (I've desensitized my data)

Employment_Contract.xlsm | Powered by Box

app.box.com

There are three worksheets in the file (staff information, contract full/part time).
I have completed identifying the employee who are ending their contract in the current month from Staff_Info by highlighting and making the data bold.
This macro is available in "Expiring Contract" button on the top of "Staff_Info" sheet.

This is where I need your help. I want to copy and paste the highlighted data from Staff_Info to Contract(Full-Time) worksheet, without any duplicates. But I do not want to copy every data of the employee, but only want column A,B,C,D,F,G,H,I,L to be copied.
I tried to copy and paste the highlighted data from column A with the button "Proceed Contract Data", but it does not seem to work properly. The name of the function is called CommandButton1_Click

I will bulletpoint what I want to achieve to clarify the tasks simpler.
- From the highlighted data in 'Staff_Info' sheet, only copy column A,B,C,D,F,G,H,I,L to 'Contract(Full-Time)' sheet.
- When pasting data to 'Contract(Full-Time)' sheet, paste them in one row.
- I want to make this whole procedure as a button, called "Proceed Contract Data".

Thank you so much for your help and I am looking forward to hearing back from you!

 

[mumps]

Try:

Sub CopyData()
    Application.ScreenUpdating = False
    Dim desWS As Worksheet, srcWS As Worksheet
    Set desWS = ThisWorkbook.Sheets("Contract(Full-Time)")
    Set srcWS = ThisWorkbook.Sheets("Staff_Info")
    Dim LastRow As Long, i As Long, header As Range, x As Long
    LastRow = srcWS.Range("L" & srcWS.Rows.Count).End(xlUp).Row
    srcWS.Range("A2").CurrentRegion.AutoFilter Field:=12, Criteria1:=xlFilterThisMonth, Operator:=xlFilterDynamic
    With srcWS.Range("A:A,B:B,C:C,D:D,F:F,G:G,H:H,I:I,L:L")
        For i = 1 To .Areas.Count
            x = .Areas(i).Column
            Set header = desWS.Rows(1).Find(.Areas(i).Cells(2), LookIn:=xlValues, lookat:=xlWhole)
            If Not header Is Nothing Then
                Intersect(srcWS.Range(srcWS.Cells(3, x), srcWS.Cells(LastRow, x)), srcWS.AutoFilter.Range).Copy desWS.Cells(desWS.Rows.Count, header.Column).End(xlUp).Offset(1, 0)
            End If
        Next i
    End With
    srcWS.Range("A2").AutoFilter
    Application.ScreenUpdating = True
End Sub

 

[tmdgus]

Thank you soso much for your help!!
It really helped me a lot.
However, I was wondering if you could answer the following questions from your code:
- The columns I mentioned before are copied and pasted perfectly except column L in Sheet1(Staff Info), which is Contract End Date.
May I know how to copy column L of Sheet1(Staff Info) and paste it onto column H and K of Sheet2(Contract Data(Full-Time))?
- This is an advanced feature, but if you see my excel file, there are Contract Data for full time employers and part time employers. Is there any ways where I can only copy and paste the data of full time employees to sheet2(Contract Data(Full-Time)) and the data of part time to sheet3(Contract Data(Part-Time))? Full-time and part-time employees differentiate by the Column G(Contract type) of Sheet1, where "Monthly" contract type is for Full-Time employees and "Hourly", "Daily" for Part-Time employees.

Thank you for your help so far, I really really appreciate it!!
I am looking forward to hearing back from you

 

[mumps]

Column L of Sheet1(Staff Info) contains the Contract End Date. Sheet2 and Sheet3 contain the Contract End Date in column I. I'm a little confused when you say:

copy column L of Sheet1(Staff Info) and paste it onto column H and K of Sheet2(Contract Data(Full-Time))

Column H in Sheet2 contains Contract Start Date and column K contains Probation Start Date. Do you not want "Monthly" employees copied to column I of Sheet2 and "Hourly" employees copied to column I Sheet3? Please clarify in detail.

 

[tmdgus]

Sorry for making you confused. I will clarify my questions further.
Regarding copying Column L, "Contract End Date" of Sheet 1(Staff Info) to Column H,K, "Contract Start Date" and "Probation Start Date" of Sheet2, Yes, I want to copy contract end date of sheet 1 and copy them onto contract start and probation start date of sheet 2. This is because our team needs to identify staffs who end their contract within a month, then prepare contract to renew employment contract. Sheet 1 contains the current staff data, and Sheet 2 and 3 are for preparing renewal contract of staffs.

About the "monthly" and "hourly" employees, I do want to copy "Monthly" employees who are ending their contract soon to sheet2 and copy "Hourly" and "Daily" employees to sheet3.
Employees who receive salary monthly are full-time staffs, whereas hourly and daily salaries are part-time staffs.
To summarize, if contract type == Monthly, then copy to sheet 2, if contract type == Hourly or Daily, then copy to sheet 3.

Thank you so much for your help again.

 

[mumps]

Do you want to copy the Contract End Date (col L) of the monthly employees (col G) to the columns H and K of the Contract(Full-Time) sheet and the Daily and Hourly employees (col G) to the columns H and K of the Contract(Part-Time) sheet?

 

[tmdgus]

Hi, let's put aside this issue first, I have a question about the code you helped me with!
Everything seems to copy and paste well, except the column L of Sheet1, which is Contract End Date.
I tried to identify the reason why it isn't working but still clueless about this issue.
Could you please help me copy column L of Sheet1 and paste the data to column H of Sheet2?
Thank you!

About the issue copying and pasting data onto different sheets based on the contract type, (If contract type is Monthly, paste data to Sheet 2 and if it contract type is either Daily or Hourly, paste data to Sheet3),
I tried to use For and If loops to enable this feature.
However, it doesn't seem like the data is filtered.
I will attach the VBA code I modified. It will be great if you could help me identify why my function does not work!
Here is the current file I have worked on so far. You can only focus on the VBA code under Sheet1(Staff_Info).

Employment_Contract.xlsm | Powered by Box

app.box.com

Private Sub CommandButton1_Click()
Application.ScreenUpdating = False
    Dim desWS As Worksheet, desWS2 As Worksheet, srcWS As Worksheet
    Set desWS = ThisWorkbook.Sheets("Contract(Full-Time)")
    Set desWS2 = ThisWorkbook.Sheets("Contract(Part-Time)")
    Set srcWS = ThisWorkbook.Sheets("Staff_Info")
    Dim lastRow As Long, a As Integer, i As Long, header As Range, x As Long
    lastRow = srcWS.Range("L" & srcWS.Rows.Count).End(xlUp).Row
    srcWS.Range("A2").CurrentRegion.AutoFilter Field:=12, Criteria1:=xlFilterThisMonth, Operator:=xlFilterDynamic
    For a = 3 To lastRow
    If InStr(1, LCase(srcWS.Range("G" & a)), "monthly") <> 0 Then
        With srcWS.Range("A:A,B:B,C:C,D:D,F:F,G:G,H:H,I:I,L:L")
            For i = 1 To .Areas.Count
                x = .Areas(i).Column
                Set header = desWS.Rows(1).Find(.Areas(i).Cells(2), LookIn:=xlValues, lookat:=xlWhole)
                If Not header Is Nothing Then
                    Intersect(srcWS.Range(srcWS.Cells(3, x), srcWS.Cells(lastRow, x)), srcWS.AutoFilter.Range).Copy desWS.Cells(desWS.Rows.Count, header.Column).End(xlUp).Offset(1, 0)
                End If
            Next i
        End With
    End If
    If InStr(1, LCase(srcWS.Range("G" & a)), "hourly") <> 0 Then
        With srcWS.Range("A:A,B:B,C:C,D:D,F:F,G:G,H:H,I:I,L:L")
            For i = 1 To .Areas.Count
                x = .Areas(i).Column
                Set header = desWS2.Rows(1).Find(.Areas(i).Cells(2), LookIn:=xlValues, lookat:=xlWhole)
                If Not header Is Nothing Then
                    Intersect(srcWS.Range(srcWS.Cells(3, x), srcWS.Cells(lastRow, x)), srcWS.AutoFilter.Range).Copy desWS2.Cells(desWS2.Rows.Count, header.Column).End(xlUp).Offset(1, 0)
                End If
            Next i
        End With
    End If
    If InStr(1, LCase(srcWS.Range("G" & a)), "daily") <> 0 Then
        With srcWS.Range("A:A,B:B,C:C,D:D,F:F,G:G,H:H,I:I,L:L")
            For i = 1 To .Areas.Count
                x = .Areas(i).Column
                Set header = desWS2.Rows(1).Find(.Areas(i).Cells(2), LookIn:=xlValues, lookat:=xlWhole)
                If Not header Is Nothing Then
                    Intersect(srcWS.Range(srcWS.Cells(3, x), srcWS.Cells(lastRow, x)), srcWS.AutoFilter.Range).Copy desWS2.Cells(desWS2.Rows.Count, header.Column).End(xlUp).Offset(1, 0)
                End If
            Next i
        End With
    End If
    Next a
    srcWS.Range("A2").AutoFilter
    Application.ScreenUpdating = True
    desWS.Range("a2:t65565").RemoveDuplicates Columns:=1, header:=xlNo
    desWS2.Range("a2:t65565").RemoveDuplicates Columns:=1, header:=xlNo
End Sub

Thank you so much for your help and I'm looking forward to hearing back from you! Have a great day

 

[tmdgus]

Do you want to copy the Contract End Date (col L) of the monthly employees (col G) to the columns H and K of the Contract(Full-Time) sheet and the Daily and Hourly employees (col G) to the columns H and K of the Contract(Part-Time) sheet?

Regarding your question earlier, I want to copy the columns A,B,C,D,F,G,H,I,L of Sheet1 to Sheet 2, only if the contract type(Column G) of Sheet 1 is "Monthly".
I also want to copy the columns A,B,C,D,F,G,H,I,L of Sheet1 to Sheet 3, only if the contract type(Column G) of Sheet 1 is "Daily" or "Hourly".