How to copy data from one sheet to multiple sheets using IF

Closed
SK - Jun 12, 2015 at 06:18 AM
vcoolio
Posts
1359
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
September 25, 2022
- Jun 12, 2015 at 07:58 AM
Hi,

We have one sheet were the master data is entered. There are different users who input data on this master sheet. How can this data be copied on separate sheets(user wise) simultaneously. For example, the users are A,B,C. How can the data of A be copied on separate sheet while user A is inputting the master data sheet. Similarly, this has to follow with other users B & C. Do we need to run any kind of a macro to perform this action? Please help.

Thanks in advance,

Sami Khan

3 replies

venkat1926
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
810
Jun 12, 2015 at 07:37 AM
when the user enters data there should be one column where user name must be also entered. in that case you can filter data according to each user and then copy filtered data to sheet named that user. every time you have unfilter and then again filter. you post a sample sheet ( small sample) and then the macro can be written
0
vcoolio
Posts
1359
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
September 25, 2022
250
Jun 12, 2015 at 07:50 AM
Hello Sami,

If I have understood your post correctly, then you would like the following to happen:-

- If User "A" is inputting data into the Master sheet, then all the data inputted by User "A" is to be placed into an individual sheet named after this particular User (in this case, A).

- If User "B" is inputting data into the Master sheet, then all the data inputted by User "B" is to be placed into an individual sheet named after this particular User (in this case, B).

..............and so on for all Users.

I'm assuming that you want a whole data set (or block of data) to be transferred to the relevant individual sheet. If so, then the following code may do the task for you:-

Sub TransferUserData()

Application.ScreenUpdating = False

     Dim lRow As Long
     Dim MySheet As String
lRow = Range("A" & Rows.Count).End(xlUp).Row

If Range("B1") <> "" Then
    MySheet = Range("B1")
    Range("A3:M" & lRow).Copy
    Sheets(MySheet).Range("A" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
End If

Sheets("Master").Range("A3:M" & Rows.Count).ClearContents
Application.ScreenUpdating = True
Application.CutCopyMode = False
MsgBox "Data transfer completed!", vbExclamation
Sheets(MySheet).Select

End Sub


The code requires that you place the User's name (the sheet needs to be named exactly the same) into cell B1 on the Master sheet. The "used" data block for each User will be cleared from the Master sheet once transferred.

I have attached my test sample work book for you to peruse here:-

https://www.dropbox.com/s/m54tr0x22hefi62/Sami%20Khan.xlsm?dl=0

I hope that this helps.

Cheerio,
vcoolio.
0
vcoolio
Posts
1359
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
September 25, 2022
250
Jun 12, 2015 at 07:58 AM
Hello Venkat,

We must have posted at the same time. My apologies.
I'll leave it to you.

Cheerio,
vcoolio.
0