I have emailed you. just to let u know i commented here
Thanks
max

"Eduardo" wrote:

Hi Max,
my email is
Please send me the file and I will take a look this afternoon or tomorrow
morning

"Max" wrote:

It still gives me that error after i copied the button from summary to sheet1


"Eduardo" wrote:

Hi Max,
Sorry I just finish reading your post, do something put the button with the
macro in another sheet you can have it in sheet 1. what happens the macro is
calling for the summary sheet by itself, try have the macro in another sheet
and let me know. Thank you

"Max" wrote:

Hi Ed,
I feel i am almost there, now i am not getting errror but while i click
"it gives me a message saying not enough rows in Destsh.

What i did was just to try

In sheet1 just put 3 headers in A1, A2, A3 as NAme , Start , End
and in then next 2 rows entered data.
similary in sheet2 did the same
and in summary sheet have that button and whne i click , i expect data from
sheet1 and sheet 2 to apper but not appearing.
TIA
max
from

"Eduardo" wrote:

Opps,
I forgot something, add this after the End sub in the code given before

Function lastRow(sh As Worksheet)
On Error Resume Next
lastRow = sh.Cells.Find(What:="*", _
after:=sh.Range("A1"), _
LookAt:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
On Error GoTo 0
End Function


Function LastCol(sh As Worksheet)
On Error Resume Next
LastCol = sh.Cells.Find(What:="*", _
after:=sh.Range("A1"), _
LookAt:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Column
On Error GoTo 0
End Function

"Max" wrote:

Thanks for your input
there is an error coming up in this line saying "Sub or function not defined"


"Last = lastRow(Sheets("BackLog_Summary"))"



"Eduardo" wrote:

Hi Max,
In the menu go to developer, then in controls go to insert, choose the first
rectangule, go to spreadsheet and where you want to have this button draw the
area, then click in other area of the spreadsheet, come back to the button
and right click in the mouse, choose asign macro, new, an screen will open,
paste the code there and close the window. when you return to the excel
spreadsheet if you put the mouse on top of the button a little hand will show
up, that means that the button is activate and the macro can be run, press in
the button and will run the macro

"Max" wrote:

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