One Line in Excel to Another Line in Another Worksheet 1

[JJOHNS]

I need to have a line in one Excel Worksheet go into the next available line in another worksheet, so that if I enter

column A = Roy
Column B = $1.00
Column C = 4 Hours

On any line of my main worksheet, it will automatically go to the next available line in my worksheet named Roy. I have a different worksheet for each employee, so I think you understand what I'm asking.

Can someone help with that? I am using Excel 2007

Thanks

 

[xwb]

Line with corresponding row number is easy. For next available line, you probably need to do it in VBA.

Do you want the lines copied or linked?

 

[GlennUK]

... I have a different worksheet for each employee ...

So you have all the data in your main worksheet and you also want bits of the data split out into individual sheets? For what purpose?

Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[lionelhill]

don't do it! Really, you don't want to do this!

The usual reason for wanting a new spreadsheet for each employee (or whatever) is so that you can extract data for individuals. Depending on what you are trying to extract, you probably want things like pivot tables, which will automatically take a huge pile of data in a single worksheet and sort them out by categories. You can change the categories at will, pick individual employees, or group them, or sumarise all employees in all sorts of interesting ways. Keep all your data together, and make a pivot table. In the unlikely event that this doesn't do the job, you probably need a database approach.

 

[JJOHNS]

I'm asking for this to help a friend. He is trying to use Excel as a content management system for dispatching jobs to locksmiths. So, the dispatcher takes the initial call and enters the data onto the main page, and assigns the job to a locksmith. Then she copies and pastes the data into that locksmith's worksheet, as well, so that the locksmith can open his own worksheet and see what jobs have been assigned to him. I don't believe a pasted link will work, because there will be more lines in the main worksheet than the locksmith's worksheet. I was thinking maybe a macro, although my own expertise is more Access than Excel. I know this is not the best way to do this, and I intend to suggest another approach. However, this is what my friend is asking for, so if someone knows how to do it...

 

[SkipVought]

Then she copies and pastes the data into that locksmith's worksheet, as well, so that the locksmith can open his own worksheet and see what jobs have been assigned to him.

There are many ways that do not require copy 'n' paste to other sheets. You could Auto Filter the data sheet to show only one person's data. You could use a PivotTable on another sheet to display only one person's data. You could query to another sheet to show only one person's data. Multiple sheets for multiple people is a bad design strategy!

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[lionelhill]

What you want to do is about 5 clicks with a Pivot table. Skip's right there are other methods too.

I have a report-list spreadsheet doing almost exactly what you ask. Sheet 1 is just a long (and growing) list of reports, all for different clients, in no particular order. Sheet 2 is a pivot table of the data on sheet 1, grouped by client. Each client is represented by a single row in sheet 2. Double-clicking a client in sheet 2 automatically creates a new sheet (sheet 3) containing a list of all lines from sheet 1 that match that client. This is entirely default behaviour of Excel and required no setting-up beyond marking the whole of sheet 1 and following the pivot-table wizard. The only thing you have to remember is to click on Refresh Data in the pivot table occasionally, and I'm sure that could be automated if I could be bothered.

 

[SkipVought]

The only thing you have to remember is to click on Refresh Data in the pivot table occasionally, and I'm sure that could be automated if I could be bothered.

Right-click the sheet containing the PivotTable.

Select View Code

Paste this code...

Private Sub Worksheet_Activate()
    ActiveSheet.PivotTables(1).PivotCache.Refresh
End Sub

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[lionelhill]

Thanks for that! Much appreciated.

 

[JJOHNS]

I think the pivot table idea is going to give my friend exactly what he wants.

Thank you to all who contributed.