| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Main sheet automatically picks up data from other sheets
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Main sheet automatically picks up data from other sheets
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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Main sheet automatically picks up data from other sheets
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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Main sheet automatically picks up data from other sheets
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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Main sheet automatically picks up data from other sheets
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 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Main sheet automatically picks up data from other sheets
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 |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Main sheet automatically picks up data from other sheets
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 |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Main sheet automatically picks up data from other sheets
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 |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Main sheet automatically picks up data from other sheets
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 "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 |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Main sheet automatically picks up data from other sheets
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 |
|
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Main sheet automatically picks up data from other sheets
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 "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 |
|
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Main sheet automatically picks up data from other sheets
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 |
|
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Main sheet automatically picks up data from other sheets
|
|
#15
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Main sheet automatically picks up data from other sheets
|
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Updating sheets in Workbook from main sheet | Excel Discussion (Misc queries) | |||
| LINKING MAIN SHEET WITH MULTIPLE SUMMARY SHEETS | New Users to Excel | |||
| LINKING MAIN SHEET WITH MULTIPLE SUMMARY SHEETS | Excel Worksheet Functions | |||
| Graphing a dynamic range that automatically picks up the most recent entry. | Excel Worksheet Functions | |||
| How do I combine data from several sheets into one main sheet? | Excel Worksheet Functions | |||
Linear Mode
