Copying data directly to a specific sheetClosed

Question

Hi there! 

I am trying to create a spreadsheet in which I can enter information on one sheet and it automatically carries across that info to the correct sheet in the workbook.

The workbook holds information on the achievements of each employee (each employee has a sheet) But I want to be able to enter new information on the summary sheet (in date order) and have it automatically appear on their individual sheet as well.

Is there a code or something that I can use for this?

Much appreciated.

vcoolio · Answer

Hello Robsdyer,

I'm not sure how your work book is set out, but the following code may point you in the right direction:-

Sub TransferEmployeeData()

Application.ScreenUpdating = False

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

For Each cell In Range("B2:B" & lRow)
    MySheet = cell.Value
    cell.EntireRow.Copy Sheets(MySheet).Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
Next cell

'Sheets("Summary").Range("A2:J" & Rows.Count).ClearContents
MsgBox "Data transfer completed!", vbExclamation
Application.ScreenUpdating = True

End Sub

You can have a look at my test work book here:-

https://www.dropbox.com/s/qbxt3vbdspii2aw/Robsdyer.xlsm?dl=0

to see how the code works. The code assumes that the Employee names are in Column B. I'm not sure if you want to clear the Employee entries from the Summary sheet once the data has been transferred so, for now, the relevant line of code has been commented out (line 14 in the code).

You may need to adjust the ranges to suit yourself.

If its not quite what you need, then please upload a sample of your work book (be careful with any sensitive data) so we can see what you need.

You can upload a sample by using a free file sharing site such as DropBox or ge.tt.

Cheerio,
vcoolio.

Copying data directly to a specific sheet

1 response

Similar discussions

Subscribe To Our Newsletter!