Auto transfer data from one work sheet to another

[charltonLee]

Hello,

I am fairly new to using Excel so please be patient with me!

I am putting together a recruitment log for my company and it requires a lot of information to be included about a lot of young people. I want to collate all this data on a "Master Sheet", but then have individuals information automatically copied to another sheet within the same workbook based upon the "Interest" which they have.

For example: Joe Blogg's information is all completed on the "Master" sheet. In the "Interest" column "Sport" is selected. I then want all of Joe's information to be copied to the worksheet named "Sport". I would like this to be done for the other options of interest to. Eventually I hope to have a "Master" sheet with all information required, and then several other sheets containing information of those that have the same interests.

I have attached an example (it is all fabricated for data protection purposes but should highlight what i mean)

I know it can be done through Macros or VB but as i said, limited knowledge! I appreciate any assistance with this!

[HaHoBe]

Hi, charltonLee,

did you have a look at the Autofilter or Advanced Filter or have converted the data into a Table (as the list is called from 2007 on)? These three actions offer tho separate data on the original sheet without putting it to other sheets.

Ciao,
Holger

[charltonLee]

HaHoBe,

I have attempted various ways of using the Advance Filter option, however because my understanding is limited i don't really know what I am doing!

Using a YouTube video i managed to get the first row of data to copy but then it would not apply to the rest of the worksheet!

I cannot stress enough how much of a newbie I am at this so apologies if i am being really dumb!

[HaHoBe]

Hi, charltonLee,

okay, I still believe that you should get the best results without using macros but here we go.

Copy the Excel VBA code
Select the workbook in which you want to store the Excel VBA code
Hold the Alt key, and press the F11 key, to open the Visual Basic Editor
Choose Insert | Module
Where the cursor is flashing, choose Edit | Paste

To run the Excel VBA code:
Choose Tools | Macro | Macros
Select a macro in the list, and click the Run button

Sub SortSplit()
   Dim wks As Worksheet
   Dim iRow As Integer, iRowT As Integer
   Application.ScreenUpdating = False
   Set wks = ActiveSheet
   Range("A1").Sort key1:=Range("A2"), order1:=xlAscending, header:=xlYes
   iRow = 2
   Do Until IsEmpty(wks.Cells(iRow, 1))
      If Left(wks.Cells(iRow, 1), 1) <> Left(wks.Cells(iRow - 1, 1), 1) Then
         Worksheets.Add after:=Worksheets(Worksheets.Count)
         Rows(1).Value = wks.Rows(1).Value
         Rows(1).Font.Bold = True
         Columns(1).Font.Bold = True
         ActiveSheet.Name = Left(wks.Cells(iRow, 1), 1)
         iRowT = 1
      End If
      iRowT = iRowT + 1
      Rows(iRowT).Value = wks.Rows(iRow).Value
      iRow = iRow + 1
   Loop
   Worksheets(1).Select
   Application.ScreenUpdating = True
End Sub

This code will sort the active sheet on Column A assuming that row 1 are headers. You would need to adjust the column for sorting as well as for the data to be compared (itīs Column A here). If you have rows that go beyond 32.767 you would need to replace the data type Integer with Long to avoid a run-time error.

If you have questions please donīt be afraid to ask.

Ciao,
Holger

[charltonLee]

Thank you so much! It works!