Main sheet automatically picks up data from other sheets
Excel 2007
"Eduardo" wrote:
Hi Max,
what version of excel are you using
"Max" wrote:
I do appreiciate using this code
but can you tell me how to create macro and execute it, i am new to this
thanks
"Eduardo" wrote:
Hi Max,
Not tested
If your sheets has the same format, insert sheet # 13 call it Summary and
then use a Macro like this to bring the information , change the fields to
copy to your needs
'Delete information in sheet "BackLog_Summary" if it exist. In the code A4
in Summary is where information will start to copy
' Delete previous information in Summary
Sheets("Summary").Select
Range("A4").Select
On Error Resume Next
myLastRow = Cells.Find("*", [A1], , , xlByRows, xlPrevious).Row
myLastColumn = Cells.Find("*", [A1], , , xlByColumns, xlPrevious).Column
myLastCell = Cells(myLastRow, myLastColumn).Address
myrange = "a4:" & myLastCell
Application.ScreenUpdating = True
Range(myrange).Select
Selection.Delete
' Unhide all worksheets
For Each sh In ActiveWorkbook.Worksheets
sh.Visible = xlSheetVisible
Next sh
'loop through all worksheets and copy the data to the DestSh
For Each sh In ActiveWorkbook.Worksheets
'Find the last row with data on the DestSh
Last = lastRow(Sheets("BackLog_Summary"))
'Fill in the range that you want to copy
Set CopyRng = sh.Range("A4:AZ6")
'Test if there enough rows in the DestSh to copy all the data
If Last + CopyRng.Rows.Count
Sheets("BackLog_Summary").Rows.Count Then
MsgBox "There are not enough rows in the Destsh"
GoTo ExitTheSub
End if
'This example copies values/formats, if you only want to copy the
'values or want to copy everything look at the example below
this macro
CopyRng.Copy
With Sheets("BackLog_Summary").Cells(Last + 1, "A")
.PasteSpecial xlPasteValues
.PasteSpecial xlPasteFormats
Application.CutCopyMode = False
End With
'Optional: This will copy the sheet name in the BA column
'Sheets("BackLog_Summary").Cells(Last + 1,
"BA").Resize(CopyRng.Rows.Count).Value = sh.Name
Next
ExitTheSub:
Application.Goto Sheets("BackLog_Summary").Cells(1)
'AutoFit the column width in the DestSh sheet
Sheets("BackLog_Summary").Columns.AutoFit
With Application
.ScreenUpdating = True
.EnableEvents = True
End With
"Max" wrote:
Hi,
I have an excel with 12 sheets for each month. Each worksheet has the same
fields.
I would like to have a 13th sheet called Summary which lists to me all the
projects that have been entered in these 12 worksheets- i mean a form of data
wherein my mgr can look at the status of a project in one sheet.
For ex: the 12 sheets for each month has lot of fields out of which i am
interested only in field names "Activity","start date","finish"
So when i make an entry for activity, start-date and finish on any sheet,
the same should automatically reflect in my main sheet(13th sheet). How can i
achieve this.
Thanks
Max
|