copying and paste data from each worksheet to a summary work sheet
hello all,
I have a work book that i stored data. There is 30 tabs or sheets(i:e each sheet is used to store a day activities). At the end of the month i would like to summarized each tab's or sheet data on the summary sheet(the summary sheet is the last tab on the wookbook). I would like to have a macro that copies each entries on each sheet and store them on the summary page then sort the information in alph. I have tried record macro but that does not work. i will appriecate and help thanks in advices |
copying and paste data from each worksheet to a summary work sheet
which column do you want the alpha sort done on?
|
copying and paste data from each worksheet to a summary work sheet
Hi Mary,
Ron de Bruin has code to produce a summary sheet at: http://www.rondebruin.nl/copy2.htm Ron 's Test3() macro would appear to meet your summary requirements. To additionally sort the summary data, copy the code snippet: DestSh.UsedRange.Sort Key1:=DestSh.Range("A1"), _ Order1:=xlAscending, _ Header:=xlNo, _ OrderCustom:=1, _ MatchCase:=False, _ Orientation:=xlTopToBottom and insert it before the line: Application.ScreenUpdating = True near the foot of Ron's macro. --- Regards, Norman "mary" wrote in message ... hello all, I have a work book that i stored data. There is 30 tabs or sheets(i:e each sheet is used to store a day activities). At the end of the month i would like to summarized each tab's or sheet data on the summary sheet(the summary sheet is the last tab on the wookbook). I would like to have a macro that copies each entries on each sheet and store them on the summary page then sort the information in alph. I have tried record macro but that does not work. i will appriecate and help thanks in advices |
copying and paste data from each worksheet to a summary work s
Thanks Chip. I want to be able to copy all columns and rows in each sheet.
It wil have information on column A:D and it rows. I check that site by RON DE BRUIN but i am getting errors. Thanks in advance "Chip" wrote: which column do you want the alpha sort done on? |
copying and paste data from each worksheet to a summary work s
Hi Mary,
What errors are you getting? It may be that the code requires minor adaptation to suit your data, but you would need to give details. With the active workbook being the workbook holding the code, Ron's code worked for me. --- Regards, Norman "mary" wrote in message ... Thanks Chip. I want to be able to copy all columns and rows in each sheet. It wil have information on column A:D and it rows. I check that site by RON DE BRUIN but i am getting errors. Thanks in advance "Chip" wrote: which column do you want the alpha sort done on? |
copying and paste data from each worksheet to a summary work s
This worked for me:
Sub copyto() Sheet_count = ActiveWorkbook.Sheets.Count Sheets(1).Select currentpg = ActiveSheet.Index Do Until currentpg = Sheet_count Length = ActiveSheet.UsedRange.Rows.Count Range(Cells(1, 1), Cells(Length, 4)).Copy Sheets("Summary").Select Range("A:D").Select Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromAbove Sheets(currentpg).Select ActiveSheet.Next.Select currentpg = ActiveSheet.Index Loop Length = ActiveSheet.UsedRange.Rows.Count Sheets("Summary").Select Range("A1").Select Application.CutCopyMode = False Range(Cells(1, 1), Cells(Length, 4)).sort Key1:=Range("A1"), Order1:=xlAscending, Header:= _ xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal End Sub Where it says Sheets("Summary").Select you should change the word summary to match the name of the sheet where you have your summary data going to. |
All times are GMT +1. The time now is 04:01 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com