Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Copy data to different sheets
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Copy data to different sheets
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 -- "Actually, I *am* a rocket scientist." -- JB (www.MadRocketScientist.com) Your feedback is appreciated, click YES if this post helped you. "James Merrill" wrote: 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Copy data to different sheets
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 "JBeaucaire" wrote: 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 -- "Actually, I *am* a rocket scientist." -- JB (www.MadRocketScientist.com) Your feedback is appreciated, click YES if this post helped you. "James Merrill" wrote: 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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Copy data to different sheets
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 James Merrill wrote: 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 -- Dave Peterson |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Copy data to different sheets
On Wed, 11 Nov 2009 18:55:01 -0800, James Merrill
wrote: Bare with me here No thanks... But try stating "Bear with me here..." next time. :-) As in "Bearing the brunt of the storm." |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Copy data to different sheets
On Wed, 11 Nov 2009 18:55:01 -0800, James Merrill
wrote: 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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Copy data to different sheets
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 --------------- Jacob Skaria "James Merrill" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy data to multiple sheets | Excel Worksheet Functions | |||
How do I copy and move selections of data to other sheets? | Excel Worksheet Functions | |||
copy data from various sheets into one sheet | Excel Worksheet Functions | |||
copy data between sheets, but not the formula | Excel Worksheet Functions | |||
in VBA Sheets("mysheet").Copy Befo=Sheets(1) how do i get a reference to the newly created copy of this sheet? | Excel Worksheet Functions |