General discussion

Locked

Excel linking to worksheets

By Sue T ·
I have a workbook with 5 worksheets. One column has a school code entered. I would like to find a way to have the data in a row automatically copy to another worksheet by using the school code which should tell it which worksheet to copy to depending on the code in that cell. Is there a way to do this?

This conversation is currently closed to new comments.

7 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Comments

Collapse -

by Sue T In reply to Excel linking to workshee ...

Point value changed by question poster.

Collapse -

by mikex In reply to Excel linking to workshee ...

Yes of course :

in the formula cell enter =IF("cell ref"="school code","expected result if the condition macth","what to do if the condition doesn't macth")

Example : IF(a1=TS,a2,ERR)

so in this case if in the cell a1 there is a string "ts", the formula will bring back the content of the A2, and if not you'll get "ERR"


in any one condition - example IF(Y,Z,B)
instead of Y,Z,B you can put another if with another YZB conditions

Collapse -

by Sue T In reply to

Sorry for the delay. I am trying to find a way for it to copy the whole row to another worksheet depending on the value in one column. This is only copying the first column and also when it copies it to the worksheet it is copying it to the same row number which is leaving blank rows between records. Is there a way to copy the whole row and have it copy to the next empty row on in the worksheet?

Collapse -

by Sue T In reply to Excel linking to workshee ...

Point value changed by question poster.

Collapse -

by agency In reply to Excel linking to workshee ...

Here's a macro that can do it, assign it to a key combination and press that when you are on a row containing data you want to add to the corresponding worksheet. You should have the school code in column A and the worksheet should have the same name as the school code. Call your main data sheet "Data".

Sub AddData()
' This Macro adds data to the sheet named in column A
' Give your input sheet the name Data or change the word Data in this macro to the sheet name
' Assign the macro to a key and press it when you are on a row to add that row to the corresponding sheet
Dim x As String
x = Selection.End(xlToLeft).Value
Range(Selection, Selection.End(xlToLeft)).Select
Selection.Copy
Sheets(x).Select
ActiveCell.SpecialCells(xlLastCell).Select
Selection.End(xlToLeft).Select
Selection.End(xlToLeft).Select
ActiveCell.Offset(1, 0).Select
ActiveSheet.Paste
Sheets("Data").Select
ActiveCell.SpecialCells(xlLastCell).Select
ActiveCell.Offset(1, 0).Select
Selection.End(xlToLeft).Select
End Sub

Collapse -

by Sue T In reply to

Poster rated this answer. I gave this to our Business teacher and he was not able to make it work. He assures me he followed your directions exactly. Thank you for responding to the question.

Collapse -

by Sue T In reply to Excel linking to workshee ...

This question was closed by the author

Back to Software Forum
7 total posts (Page 1 of 1)  

Related Discussions

Related Forums