copying and paste data from each worksheet to a summary work sheet

[Guest]

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

 

[Chip]

which column do you want the alpha sort done on?

 

[Norman Jones]

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.

 

[Guest]

Thanks Chip. I want to be able to copy all columns and rows in each sheet.
It wil have information on column A and it rows. I check that site by RON
DE BRUIN but i am getting errors.
Thanks in advance

 

[Norman Jones]

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.

 

[Chip]

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").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.