C&P row of data from Main Datasheet to the other worksheets if condition is fulfilled

LinLin · May 31st 2018

Hi all,

I have read through multiple forums and posts regarding this problem but has yet to find any that can be applied to the worksheet i have on my hand... I am relatively new to this marco/VBA thing, and i'd really appreciate any form of help. Thank you so much in advance!

I am trying to look for a formula/marco/VBA that would perform the following steps automatically: -

I have attached a sample of the excel workbook in this post. The excel workbook consists of the following worksheets
- Database
- Terminated
- Resigned
- Internal Transfer
- Completed
- Active

The worksheet "Database" consists of all the existing data, and new data will be entered into this worksheet periodically.

I would like excel to be able to copy and paste the existing & new data from the worksheet "Database" to the respective worksheet based on the column - M, status.

For example, the Database worksheet consists of an employee named "ccc" whose status shows "Active", hence the entire row of information relating to this employee "ccc" show be reflected in the worksheet "Active".

If one day, this particular employee named "ccc" were to resign from the company, I will make this change on the worksheet "Database", and then the formula/marco/VBA should be able to track it and move the entire row of data from the worksheet "Active" to the worksheet "resigned".

Thank you so much!

Mumps · May 31st 2018

I don't see any employee named "ccc". I do see "ccc" as a country name in column D. I assume the employee names are in column H. Please clarify. Is there any possibility that there might be more than one employee with the same name? Also I would suggest that you use a data validation drop down list in column M. This is more efficient and eliminates typo errors.

LinLin · Jun 1st 2018

Hi Mumps,

Thank you so much for spending your time to reply, i'd answer your question in the bullet points below: -

Yes the employee names are in column H, apologies for the mistake.
There is no possibility that there might be more than 1 employee with the same name.
The original excel workbook does have a validation drop down list in column M, this is just a mock up workbook because the original excel workbook contains confidential data.

Hope that clarifies! Please let me know if you need any other information.

Once again, thank you so much!

vcoolio · Jun 1st 2018

Hello LinLin,

I've only had a very quick look at your sample workbook but see if the following code works for you:-

Code

Option Explicit
Sub TransferData()


        Dim ar As Variant
        Dim i As Integer
        Dim ws As Worksheet
     
Set ws = Sheets("Database")
ar = ws.Range("M4", ws.Range("M" & ws.Rows.Count).End(xlUp))


Application.ScreenUpdating = False


For i = 1 To UBound(ar)
Sheets(ar(i, 1)).UsedRange.Offset(3).ClearContents


With ws.[B3].CurrentRegion
       .AutoFilter 12, ar(i, 1)
       .Offset(1).Copy Sheets(ar(i, 1)).Range("B" & Rows.Count).End(3)(2)
       .AutoFilter
End With
Next i


Application.CutCopyMode = False
Application.ScreenUpdating = True


MsgBox "Data transfer completed!", vbExclamation, "Status"


End Sub

Display More

This line of code:-

Code

.Offset(1).Copy Sheets(ar(i, 1)).Range("B" & Rows.Count).End(3)(2)

could also be:-

Code

.Offset(1).Resize(, 29).Copy Sheets(ar(i, 1)).Range("B" & Rows.Count).End(3)(2)

if it works better for you.

Test it in a full copy of your workbook first.

I hope that this helps.

Cheerio,
vcoolio.

Mumps · Jun 1st 2018

Try the attached file. The macro is in the worksheet code module. To see it, right click the "Database" tab and click 'View Code'. The macro will be triggered automatically when you change any cell in column M. When you are entering data in the "Database" sheet, fill in all the other columns first and then column M last. When you make a selection in the drop down in column M, if you choose "Active", that row will automatically be copied to the "Active" sheet. If your choose "Resigned", that row will automatically be moved from the "Active" sheet to the "Resigned" sheet. Your file was very large due to the number of rows that were formatted in each sheet. I have removed all the extra rows and now the file size is very much smaller. It will also save and load faster. Please let me know how it works out.

LinLin · Jun 5th 2018

Hi Mumps,

I tried to run the code but the system keeps prompting me the following: -
[ATTACH=JSON]{"data-align":"left","data-size":"full","title":"Capture.PNG","data-attachmentid":1204137}[/ATTACH]

I am really very new to this Macro thing, and i really appreciate your help!
I also took a look at the code, and it seems that you only have 2 scenario which is "Active" and "Resigned", but there is a total of 5 scenarios that i have (Active, Completed, Internal Transfer, Resigned & Terminated), please advise how i can add the other scenarios in as well.

Hope to hear from you soon!

Thank you so much. I really really appreciate your help.

Best regards,
LinLin

Mumps · Jun 5th 2018

[SIZE=13px]The prompt you received is due to the fact that you tried to run the macro manually so it is asking you which macro you want to run. [/SIZE]

This macro is an event macro which means that you do not run it manually. It will run automatically when you

[SIZE=13px]make a selection in the drop down in column M. Please see my instructions in Post #5. To which sheets do you want to copy the data when you select Completed, Internal Transfer and Terminated?[/SIZE]

C&P row of data from Main Datasheet to the other worksheets if condition is fulfilled

Participate now!

Share