Copying data directly to a specific sheet

[Closed]
Report
Posts
1
Registration date
Thursday April 23, 2015
Status
Member
Last seen
April 23, 2015
-
Posts
1318
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
September 20, 2021
-
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.

1 reply

Posts
1318
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
September 20, 2021
238
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.