Copy data to different sheets

[James Merrill]

Bare with me here as I try to explain this because it is somewhat confusing,
I will try to simplify as much as possible. I have a workbook with multiple
sheets. The first sheet (AllData) contains all my data. I have 3 columns of
data: Subject, Student, and Grade. Looks like this:

SUBJECT STUDENT GRADE
math john doe A
science jane doe B
english james C

There are three subsequent sheets named according to the subject: "math",
"science", "english" I want to move all the "math" data onto the math sheet,
science data onto science sheet, and engnlish data onto english sheet.

This is a simplified version, as my real example has 140 different
"subjects" and about 40,000 rows, so doing it manually is a bit out of the
question. Let me know if any of that didnt make sense or if i can further
clarify.

Thanks,
James

 

[JBeaucaire]

Do you really mean "Bare" with you, or "bear" with you...hehe.

1) When you say "move all the math data to the math sheet" do you mean move
or did you mean copy? Are basically eliminating the master sheet or duping
the data on the subject sheets?

2) What's the name of the master sheet?

3) Do all the subject sheets exist already?

4) Are you OK with a macro that does this for you "all at once"?

5) If macro is OK, when it runs, do you want it to check and make sure the
needed sheets already exist and create them if needed?

6) Do you want the macro to ADD to the data on those existing sheets or
create a whole fresh new report each time it is run?

jerry
AT
devstudios
DOT
com

 

[James Merrill]

ahhh you caught me on the grammar....

1. I meant copy, keep data on master sheet
2. "AllData"
3. Yes they already exist, I used a macro to create them
4. Yes I would love a macro to do this all at once, its already a
macro-enabled workbook as i used the aforementioned macro to create the
sheets.
5. Shouldn't need to but ok
6. No need as this is a one time process

Thanks!
James

 

[Dave Peterson]

First, I would argue against doing this.

If you keep all your data on one worksheet, then you'll be able to do more
things (charts and graphs, sorting, filtering (by student), pivottables, ...

Moving the data to different sheets would make that kind of stuff more difficult
and even worse, you may find people updating the data in the wrong sheets!

But if you have to, I'd still keep all my data in one worksheet and update it
there. Then each time I needed these separate worksheets, I'd run a macro that
would regenerate these sheets. (Keep in mind that those are "report-only"
worksheets. Any changes to them will be lost with the next re-generation.)

If that sounds like an idea you could use, you may want to look at how Ron de
Bruin and Debra Dalgleish approached this kind of thing:

Ron de Bruin's EasyFilter addin:
http://www.rondebruin.nl/easyfilter.htm

Or:

Code from Debra Dalgleish's site:
http://www.contextures.com/excelfiles.html

Create New Sheets from Filtered List -- uses an Advanced Filter to create
separate sheet of orders for each sales rep visible in a filtered list; macro
automates the filter. AdvFilterRepFiltered.xls 35 kb

Update Sheets from Master -- uses an Advanced Filter to send data from
Master sheet to individual worksheets -- replaces old data with current.
AdvFilterCity.xls 55 kb

 

[CellShocked]

Bare with me here

No thanks...

But try stating "Bear with me here..." next time.

As in "Bearing the brunt of the storm."

 

[CellShocked]

Bare with me here as I try to explain this because it is somewhat confusing,
I will try to simplify as much as possible. I have a workbook with multiple
sheets. The first sheet (AllData) contains all my data. I have 3 columns of
data: Subject, Student, and Grade. Looks like this:

SUBJECT STUDENT GRADE
math john doe A
science jane doe B
english james C

There are three subsequent sheets named according to the subject: "math",
"science", "english" I want to move all the "math" data onto the math sheet,
science data onto science sheet, and engnlish data onto english sheet.

This is a simplified version, as my real example has 140 different
"subjects" and about 40,000 rows, so doing it manually is a bit out of the
question. Let me know if any of that didnt make sense or if i can further
clarify.

Thanks,
James

Well, regardless of how tedious you think it is, you may as well simply
sort on the subject, and do 140 separate highlight, copy,
paste-into-new-sheet, and delete operations.

Just think... when you are done, you'll be done. Even with advanced
filters, you would still have to make manual selections of each subject
to sort on.

There are macros that can do it, but you should be in the other group
for that: microsoft.public.excel.programming

 

[Jacob Skaria]

Try the below.

Sub Copyrows()
Dim wb As Workbook, ws1 As Worksheet, ws2 As Worksheet
Dim lngRow As Long, lngLastRow1 As Long, lngLastRow2 As Long
Set wb = ActiveWorkbook
Set ws1 = wb.Sheets("AllData")
lngLastRow1 = ws1.Cells(Rows.Count, "A").End(xlUp).Row
For lngRow = 2 To lngLastRow1
Set ws2 = wb.Sheets(CStr(ws1.Range("A" & lngRow)))
lngLastRow2 = ws2.Cells(Rows.Count, "A").End(xlUp).Row
ws1.Rows(lngRow).Copy ws2.Rows(lngLastRow2 + 1)
Next
End Sub

If this post helps click Yes