Main sheet automatically picks up data from other sheets

[Max]

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

 

[Eduardo]

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]

I do appreiciate using this code
but can you tell me how to create macro and execute it, i am new to this
thanks

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

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

 

[Eduardo]

Hi Max,
what version of excel are you using

I do appreiciate using this code
but can you tell me how to create macro and execute it, i am new to this
thanks

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

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

 

[Max]

Excel 2007

Hi Max,
what version of excel are you using

I do appreiciate using this code
but can you tell me how to create macro and execute it, i am new to this
thanks

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

:

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

 

[Eduardo]

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

Excel 2007

Hi Max,
what version of excel are you using

I do appreiciate using this code
but can you tell me how to create macro and execute it, i am new to this
thanks

:

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

:

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

 

[Max]

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


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

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

Excel 2007

Hi Max,
what version of excel are you using

:

I do appreiciate using this code
but can you tell me how to create macro and execute it, i am new to this
thanks

:

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

:

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

 

[Eduardo]

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

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


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

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

Excel 2007

:

Hi Max,
what version of excel are you using

:

I do appreiciate using this code
but can you tell me how to create macro and execute it, i am new to this
thanks

:

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

:

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

 

[Max]

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

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

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


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

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

:

Excel 2007

:

Hi Max,
what version of excel are you using

:

I do appreiciate using this code
but can you tell me how to create macro and execute it, i am new to this
thanks

:

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

:

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

 

[Eduardo]

Hi Max,
That is part of the code, what it does is to check if you have enough rows
empty to paste the information. that means that you don't have enough rows in
the destination sheet, so the code stops there. Maybe you can delete
information you don't need or you can create another sheet to save the
information

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

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

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


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



:

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

:

Excel 2007

:

Hi Max,
what version of excel are you using

:

I do appreiciate using this code
but can you tell me how to create macro and execute it, i am new to this
thanks

:

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

:

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

 

[Eduardo]

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

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

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

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


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



:

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

:

Excel 2007

:

Hi Max,
what version of excel are you using

:

I do appreiciate using this code
but can you tell me how to create macro and execute it, i am new to this
thanks

:

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

:

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

 

[Max]

No Luck Ed,
I tried copying the button to sheet1 and tried , after the press of button
the Summary sheet is shwn and A5 cell gets highlighted and get the same error
mesg
Pass me your email i can send you the sheet
Thanks
max

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

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

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

:

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


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



:

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

:

Excel 2007

:

Hi Max,
what version of excel are you using

:

I do appreiciate using this code
but can you tell me how to create macro and execute it, i am new to this
thanks

:

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

:

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

 

[Max]

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

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

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

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

:

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


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



:

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

:

Excel 2007

:

Hi Max,
what version of excel are you using

:

I do appreiciate using this code
but can you tell me how to create macro and execute it, i am new to this
thanks

:

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

:

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

 

[Eduardo]

Hi Max,
my email is (e-mail address removed)
Please send me the file and I will take a look this afternoon or tomorrow
morning

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

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

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

:

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

:

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


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



:

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

:

Excel 2007

:

Hi Max,
what version of excel are you using

:

I do appreiciate using this code
but can you tell me how to create macro and execute it, i am new to this
thanks

:

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

:

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

 

[Max]

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

Hi Max,
my email is (e-mail address removed)
Please send me the file and I will take a look this afternoon or tomorrow
morning

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

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

:

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

:

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

:

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


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



:

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

:

Excel 2007

:

Hi Max,
what version of excel are you using

:

I do appreiciate using this code
but can you tell me how to create macro and execute it, i am new to this
thanks

:

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

:

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