| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
auto copy from one sheet to another
I'll be creating a workbook with 5 sheets that folks will use each day to
submit information on. The first 4 sheets will have data entered in columns A, B, C, D, E, F and G. The number of rows each day with data entered is variable and on some days a sheet may even be empty. My goal is to have the 5th Sheet be a summary page and is titled, "Summary". On this summary sheet I only want to copy the informaiton from Columns A, B and E from the sheets titled "First", "Second", "Third" and "Fourth" only when data is entered beginning on row 2 since there are column headings on each sheet. I'm just not sure how to designate to copy the text from one sheet to another whe when the number of rows pupulated on each sheet is unknown. Any ideas are appreciated, thank you |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
auto copy from one sheet to another
This macro should do what you want. I assumed that the file has 5 sheets as
you said and that one is named Summary. This macro copies what you say you want from all the other sheets to the Summary sheet. HTH Otto Sub CopyToSummary() Dim ws As Worksheet Application.ScreenUpdating = False For Each ws In ActiveWorkbook.Worksheets If ws.Name = "Summary" Then GoTo NextSheet With ws If IsEmpty(.Range("A2").Value) Then GoTo NextSheet .Range("A2", .Range("A" & Rows.Count).End(xlUp)).Resize(, 2).Copy Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial .Range("E2", .Range("E" & Rows.Count).End(xlUp)).Copy Range("C" & Rows.Count).End(xlUp).Offset(1).PasteSpecial End With NextSheet: Next ws Application.ScreenUpdating = True End Sub "JOUIOUI" wrote in message ... I'll be creating a workbook with 5 sheets that folks will use each day to submit information on. The first 4 sheets will have data entered in columns A, B, C, D, E, F and G. The number of rows each day with data entered is variable and on some days a sheet may even be empty. My goal is to have the 5th Sheet be a summary page and is titled, "Summary". On this summary sheet I only want to copy the informaiton from Columns A, B and E from the sheets titled "First", "Second", "Third" and "Fourth" only when data is entered beginning on row 2 since there are column headings on each sheet. I'm just not sure how to designate to copy the text from one sheet to another whe when the number of rows pupulated on each sheet is unknown. Any ideas are appreciated, thank you |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
auto copy from one sheet to another
The line:
..Range("A2", .Range("A" & Rows.Count).End(xlUp)).Resize(,2).Copy is the line that sets up to copy Columns A & B. The first part: ..Range("A2", .Range("A" & Rows.Count).End(xlUp)) is all of Column A. The last part, Resize(,2), increases the range to copy to encompass both Columns A & B. The macro, as written, copies Column E from the other sheets and pastes it into Column C of the Summary sheet. You say you want the name of the sheet to appear in Column C. What do you want to do with the data from Column E? Copying a row from the other sheets while data is being entered presents a problem. Excel needs to have a trigger for when to do the copying. Entering data in any cell can be the trigger but that brings up the problem of where to paste the data in the Summary sheet, i.e, in what row? One solution would be to use any entry in Column G (the last column) as the trigger. Then Excel would copy Columns A, B, and E, of that row at that time. Of course, this may not work for you if data entry is not made by columns in order. Post back and answer the questions and your decision on how to trigger Excel to copy. Otto "JOUIOUI" wrote in message ... This worked great for me Otto, thanks, I just have two more questions for you. Is there a way I can have the data prefill the summary as it is entered on the other sheets rather than run a macro to have the summary populate? My other question is I would like the sheet name to appear in column C on the summary page for each item copied to the summary page. Oh one more question, I was able to understand your code, but where are calling the Col B information, I only see A and E? Thanks again for your help. "Otto Moehrbach" wrote: This macro should do what you want. I assumed that the file has 5 sheets as you said and that one is named Summary. This macro copies what you say you want from all the other sheets to the Summary sheet. HTH Otto Sub CopyToSummary() Dim ws As Worksheet Application.ScreenUpdating = False For Each ws In ActiveWorkbook.Worksheets If ws.Name = "Summary" Then GoTo NextSheet With ws If IsEmpty(.Range("A2").Value) Then GoTo NextSheet .Range("A2", .Range("A" & Rows.Count).End(xlUp)).Resize(, 2).Copy Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial .Range("E2", .Range("E" & Rows.Count).End(xlUp)).Copy Range("C" & Rows.Count).End(xlUp).Offset(1).PasteSpecial End With NextSheet: Next ws Application.ScreenUpdating = True End Sub "JOUIOUI" wrote in message ... I'll be creating a workbook with 5 sheets that folks will use each day to submit information on. The first 4 sheets will have data entered in columns A, B, C, D, E, F and G. The number of rows each day with data entered is variable and on some days a sheet may even be empty. My goal is to have the 5th Sheet be a summary page and is titled, "Summary". On this summary sheet I only want to copy the informaiton from Columns A, B and E from the sheets titled "First", "Second", "Third" and "Fourth" only when data is entered beginning on row 2 since there are column headings on each sheet. I'm just not sure how to designate to copy the text from one sheet to another whe when the number of rows pupulated on each sheet is unknown. Any ideas are appreciated, thank you |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
auto copy from one sheet to another
Hi Otto,
As you can see, I'm sort of a beginner at this so I sometimes do things the long way rather than the most efficient. I was going to prefill the sheets name into column E of the worksheets, then copy Col A, B and E into the Summary worksheet. Then I thought it would be more efficient to just copy Col A and B and the sheet name with code into the Summary sheet thus saving some steps. On the first 4 sheets, the only required columns for data entry are Col A and B so with that said is it possible when the person enters their info in Col A and B, after they move off B the data populates the Summary Page. One other question, I read in one of the other postings that if a sheet is protected, a macro has to be used rather than a command button to evoke code, is that true. I would rather use a command button for another function separate from this , however I do need to protect my sheet so that my not be an option for me. I'm trying to think the most efficient and logical way, does this sound correct to you Otto. I also appreciate your explanation of the code...I'm learning as I go and I appreciate your extra effort. Thank you. "Otto Moehrbach" wrote: The line: ..Range("A2", .Range("A" & Rows.Count).End(xlUp)).Resize(,2).Copy is the line that sets up to copy Columns A & B. The first part: ..Range("A2", .Range("A" & Rows.Count).End(xlUp)) is all of Column A. The last part, Resize(,2), increases the range to copy to encompass both Columns A & B. The macro, as written, copies Column E from the other sheets and pastes it into Column C of the Summary sheet. You say you want the name of the sheet to appear in Column C. What do you want to do with the data from Column E? Copying a row from the other sheets while data is being entered presents a problem. Excel needs to have a trigger for when to do the copying. Entering data in any cell can be the trigger but that brings up the problem of where to paste the data in the Summary sheet, i.e, in what row? One solution would be to use any entry in Column G (the last column) as the trigger. Then Excel would copy Columns A, B, and E, of that row at that time. Of course, this may not work for you if data entry is not made by columns in order. Post back and answer the questions and your decision on how to trigger Excel to copy. Otto "JOUIOUI" wrote in message ... This worked great for me Otto, thanks, I just have two more questions for you. Is there a way I can have the data prefill the summary as it is entered on the other sheets rather than run a macro to have the summary populate? My other question is I would like the sheet name to appear in column C on the summary page for each item copied to the summary page. Oh one more question, I was able to understand your code, but where are calling the Col B information, I only see A and E? Thanks again for your help. "Otto Moehrbach" wrote: This macro should do what you want. I assumed that the file has 5 sheets as you said and that one is named Summary. This macro copies what you say you want from all the other sheets to the Summary sheet. HTH Otto Sub CopyToSummary() Dim ws As Worksheet Application.ScreenUpdating = False For Each ws In ActiveWorkbook.Worksheets If ws.Name = "Summary" Then GoTo NextSheet With ws If IsEmpty(.Range("A2").Value) Then GoTo NextSheet .Range("A2", .Range("A" & Rows.Count).End(xlUp)).Resize(, 2).Copy Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial .Range("E2", .Range("E" & Rows.Count).End(xlUp)).Copy Range("C" & Rows.Count).End(xlUp).Offset(1).PasteSpecial End With NextSheet: Next ws Application.ScreenUpdating = True End Sub "JOUIOUI" wrote in message ... I'll be creating a workbook with 5 sheets that folks will use each day to submit information on. The first 4 sheets will have data entered in columns A, B, C, D, E, F and G. The number of rows each day with data entered is variable and on some days a sheet may even be empty. My goal is to have the 5th Sheet be a summary page and is titled, "Summary". On this summary sheet I only want to copy the informaiton from Columns A, B and E from the sheets titled "First", "Second", "Third" and "Fourth" only when data is entered beginning on row 2 since there are column headings on each sheet. I'm just not sure how to designate to copy the text from one sheet to another whe when the number of rows pupulated on each sheet is unknown. Any ideas are appreciated, thank you |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
auto copy from one sheet to another
About the macro or command button to run a macro when the sheet is
protected. A command button is simply a means of running a macro. Also, the command button WILL run the macro even if the sheet is protected. Here is the same macro modified so that it copies only Columns A & B and puts the sheet name in Column C. You can run this macro from a command button. This macro will NOT run automatically when you enter something in Column B. Sub CopyToSummary() Dim ws As Worksheet Application.ScreenUpdating = False For Each ws In ActiveWorkbook.Worksheets If ws.Name = "Summary" Then GoTo NextSheet With ws If IsEmpty(.Range("A2").Value) Then GoTo NextSheet .Range("A2", .Range("A" & Rows.Count).End(xlUp)).Resize(, 2).Copy Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial Range("C" & Rows.Count).End(xlUp).Offset(1).Value = ws.Name End With NextSheet: Next ws Application.ScreenUpdating = True End Sub The following will execute upon any entry in any cell in Column B of any of the other sheets. Note that this macro is a sheet macro and MUST be placed in the sheet module of EACH of the other sheets (not the Summary sheet). HTH Otto Private Sub Worksheet_Change(ByVal Target As Range) If Target.Count 1 Then Exit Sub If IsEmpty(Target.Value) Then Exit Sub If Target.Column = 2 Then Application.ScreenUpdating = False With Sheets("Summary") Target.Offset(, -1).Resize(, 2).Copy .Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial .Range("C" & Rows.Count).End(xlUp).Offset(1).Value = Me.Name End With Application.ScreenUpdating = True End If End Sub "SITCFanTN" wrote in message ... Hi Otto, As you can see, I'm sort of a beginner at this so I sometimes do things the long way rather than the most efficient. I was going to prefill the sheets name into column E of the worksheets, then copy Col A, B and E into the Summary worksheet. Then I thought it would be more efficient to just copy Col A and B and the sheet name with code into the Summary sheet thus saving some steps. On the first 4 sheets, the only required columns for data entry are Col A and B so with that said is it possible when the person enters their info in Col A and B, after they move off B the data populates the Summary Page. One other question, I read in one of the other postings that if a sheet is protected, a macro has to be used rather than a command button to evoke code, is that true. I would rather use a command button for another function separate from this , however I do need to protect my sheet so that my not be an option for me. I'm trying to think the most efficient and logical way, does this sound correct to you Otto. I also appreciate your explanation of the code...I'm learning as I go and I appreciate your extra effort. Thank you. "Otto Moehrbach" wrote: The line: ..Range("A2", .Range("A" & Rows.Count).End(xlUp)).Resize(,2).Copy is the line that sets up to copy Columns A & B. The first part: ..Range("A2", .Range("A" & Rows.Count).End(xlUp)) is all of Column A. The last part, Resize(,2), increases the range to copy to encompass both Columns A & B. The macro, as written, copies Column E from the other sheets and pastes it into Column C of the Summary sheet. You say you want the name of the sheet to appear in Column C. What do you want to do with the data from Column E? Copying a row from the other sheets while data is being entered presents a problem. Excel needs to have a trigger for when to do the copying. Entering data in any cell can be the trigger but that brings up the problem of where to paste the data in the Summary sheet, i.e, in what row? One solution would be to use any entry in Column G (the last column) as the trigger. Then Excel would copy Columns A, B, and E, of that row at that time. Of course, this may not work for you if data entry is not made by columns in order. Post back and answer the questions and your decision on how to trigger Excel to copy. Otto "JOUIOUI" wrote in message ... This worked great for me Otto, thanks, I just have two more questions for you. Is there a way I can have the data prefill the summary as it is entered on the other sheets rather than run a macro to have the summary populate? My other question is I would like the sheet name to appear in column C on the summary page for each item copied to the summary page. Oh one more question, I was able to understand your code, but where are calling the Col B information, I only see A and E? Thanks again for your help. "Otto Moehrbach" wrote: This macro should do what you want. I assumed that the file has 5 sheets as you said and that one is named Summary. This macro copies what you say you want from all the other sheets to the Summary sheet. HTH Otto Sub CopyToSummary() Dim ws As Worksheet Application.ScreenUpdating = False For Each ws In ActiveWorkbook.Worksheets If ws.Name = "Summary" Then GoTo NextSheet With ws If IsEmpty(.Range("A2").Value) Then GoTo NextSheet .Range("A2", .Range("A" & Rows.Count).End(xlUp)).Resize(, 2).Copy Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial .Range("E2", .Range("E" & Rows.Count).End(xlUp)).Copy Range("C" & Rows.Count).End(xlUp).Offset(1).PasteSpecial End With NextSheet: Next ws Application.ScreenUpdating = True End Sub "JOUIOUI" wrote in message ... I'll be creating a workbook with 5 sheets that folks will use each day to submit information on. The first 4 sheets will have data entered in columns A, B, C, D, E, F and G. The number of rows each day with data entered is variable and on some days a sheet may even be empty. My goal is to have the 5th Sheet be a summary page and is titled, "Summary". On this summary sheet I only want to copy the informaiton from Columns A, B and E from the sheets titled "First", "Second", "Third" and "Fourth" only when data is entered beginning on row 2 since there are column headings on each sheet. I'm just not sure how to designate to copy the text from one sheet to another whe when the number of rows pupulated on each sheet is unknown. Any ideas are appreciated, thank you |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
auto copy from one sheet to another
Hi Again Otto,
I thought I would have to select the sheet by name, so this is what I did, oh by the way, I chose your second option. It is not prefilling the Summary Page. I tried copying your code directly into a module and it still didn't work. What am I doing wrong. Thanks again for your help. Sub MisappliedPrefillCode() Sheets("Misapplied").Select If Target.Count 1 Then Exit Sub If IsEmpty(Target.Value) Then Exit Sub If Target.Column = 2 Then Application.ScreenUpdating = False With Sheets("Summary") Target.Offset(, -1).Resize(, 2).Copy ..Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial ..Range("C" & Rows.Count).End(xlUp).Offset(1).Value = Me.Name End With Application.ScreenUpdating = True End If End Sub "Otto Moehrbach" wrote: About the macro or command button to run a macro when the sheet is protected. A command button is simply a means of running a macro. Also, the command button WILL run the macro even if the sheet is protected. Here is the same macro modified so that it copies only Columns A & B and puts the sheet name in Column C. You can run this macro from a command button. This macro will NOT run automatically when you enter something in Column B. Sub CopyToSummary() Dim ws As Worksheet Application.ScreenUpdating = False For Each ws In ActiveWorkbook.Worksheets If ws.Name = "Summary" Then GoTo NextSheet With ws If IsEmpty(.Range("A2").Value) Then GoTo NextSheet .Range("A2", .Range("A" & Rows.Count).End(xlUp)).Resize(, 2).Copy Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial Range("C" & Rows.Count).End(xlUp).Offset(1).Value = ws.Name End With NextSheet: Next ws Application.ScreenUpdating = True End Sub The following will execute upon any entry in any cell in Column B of any of the other sheets. Note that this macro is a sheet macro and MUST be placed in the sheet module of EACH of the other sheets (not the Summary sheet). HTH Otto Private Sub Worksheet_Change(ByVal Target As Range) If Target.Count 1 Then Exit Sub If IsEmpty(Target.Value) Then Exit Sub If Target.Column = 2 Then Application.ScreenUpdating = False With Sheets("Summary") Target.Offset(, -1).Resize(, 2).Copy .Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial .Range("C" & Rows.Count).End(xlUp).Offset(1).Value = Me.Name End With Application.ScreenUpdating = True End If End Sub "SITCFanTN" wrote in message ... Hi Otto, As you can see, I'm sort of a beginner at this so I sometimes do things the long way rather than the most efficient. I was going to prefill the sheets name into column E of the worksheets, then copy Col A, B and E into the Summary worksheet. Then I thought it would be more efficient to just copy Col A and B and the sheet name with code into the Summary sheet thus saving some steps. On the first 4 sheets, the only required columns for data entry are Col A and B so with that said is it possible when the person enters their info in Col A and B, after they move off B the data populates the Summary Page. One other question, I read in one of the other postings that if a sheet is protected, a macro has to be used rather than a command button to evoke code, is that true. I would rather use a command button for another function separate from this , however I do need to protect my sheet so that my not be an option for me. I'm trying to think the most efficient and logical way, does this sound correct to you Otto. I also appreciate your explanation of the code...I'm learning as I go and I appreciate your extra effort. Thank you. "Otto Moehrbach" wrote: The line: ..Range("A2", .Range("A" & Rows.Count).End(xlUp)).Resize(,2).Copy is the line that sets up to copy Columns A & B. The first part: ..Range("A2", .Range("A" & Rows.Count).End(xlUp)) is all of Column A. The last part, Resize(,2), increases the range to copy to encompass both Columns A & B. The macro, as written, copies Column E from the other sheets and pastes it into Column C of the Summary sheet. You say you want the name of the sheet to appear in Column C. What do you want to do with the data from Column E? Copying a row from the other sheets while data is being entered presents a problem. Excel needs to have a trigger for when to do the copying. Entering data in any cell can be the trigger but that brings up the problem of where to paste the data in the Summary sheet, i.e, in what row? One solution would be to use any entry in Column G (the last column) as the trigger. Then Excel would copy Columns A, B, and E, of that row at that time. Of course, this may not work for you if data entry is not made by columns in order. Post back and answer the questions and your decision on how to trigger Excel to copy. Otto "JOUIOUI" wrote in message ... This worked great for me Otto, thanks, I just have two more questions for you. Is there a way I can have the data prefill the summary as it is entered on the other sheets rather than run a macro to have the summary populate? My other question is I would like the sheet name to appear in column C on the summary page for each item copied to the summary page. Oh one more question, I was able to understand your code, but where are calling the Col B information, I only see A and E? Thanks again for your help. "Otto Moehrbach" wrote: This macro should do what you want. I assumed that the file has 5 sheets as you said and that one is named Summary. This macro copies what you say you want from all the other sheets to the Summary sheet. HTH Otto Sub CopyToSummary() Dim ws As Worksheet Application.ScreenUpdating = False For Each ws In ActiveWorkbook.Worksheets If ws.Name = "Summary" Then GoTo NextSheet With ws If IsEmpty(.Range("A2").Value) Then GoTo NextSheet .Range("A2", .Range("A" & Rows.Count).End(xlUp)).Resize(, 2).Copy Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial .Range("E2", .Range("E" & Rows.Count).End(xlUp)).Copy Range("C" & Rows.Count).End(xlUp).Offset(1).PasteSpecial End With NextSheet: Next ws Application.ScreenUpdating = True End Sub "JOUIOUI" wrote in message ... I'll be creating a workbook with 5 sheets that folks will use each day to submit information on. The first 4 sheets will have data entered in columns A, B, C, D, E, F and G. The number of rows each day with data entered is variable and on some days a sheet may even be empty. My goal is to have the 5th Sheet be a summary page and is titled, "Summary". On this summary sheet I only want to copy the informaiton from Columns A, B and E from the sheets titled "First", "Second", "Third" and "Fourth" only when data is entered beginning on row 2 since there are column headings on each sheet. I'm just not sure how to designate to copy the text from one sheet to another whe when the number of rows pupulated on each sheet is unknown. Any ideas are appreciated, thank you |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
auto copy from one sheet to another
|
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
auto copy from one sheet to another
|
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
auto copy from one sheet to another
Hi, Otto:
After reading your post and I am sure you can solve my problem. I have the similiar situation. I have a workbook which has multiple worksheets. For simplicity, we name two of the worksheets as sheet 1 and sheet 2. Sheet 1 is my input page. From the following, you can see that column A under sheet 1 has a binary input 0-1 as the default. If the default value for a row under column A on sheet 1 is 1, you are NOT allowed to change it to 0. However, if the default value is 0, you can modify it to 1 or leave it as 0. What I need is to have a summary page on sheet 2 which will have all the rows from sheet 1 with a vaule of 1 under column A IN THE SAME ORDER AND SAME FORMAT. Also, I would like this to be an automatic procedure. Could you please help me on this? The following is an illustration: On sheet 1 (this is after your modification of 0 to 1) A B C D E F G m 1 1 B1 C1 F1 2 0 D2 G2 3 0 C3 E3 m3 4 1 B4 F4 n 1 bn en mn ******On sheet 2******* A B C D E F G m 1 1 B1 C1 F1 2 1 B4 F4 n 1 bn en mn Thanks again, George Otto Moehrbach wrote: About the macro or command button to run a macro when the sheet is protected. A command button is simply a means of running a macro. Also, the command button WILL run the macro even if the sheet is protected. Here is the same macro modified so that it copies only Columns A & B and puts the sheet name in Column C. You can run this macro from a command button. This macro will NOT run automatically when you enter something in Column B. Sub CopyToSummary() Dim ws As Worksheet Application.ScreenUpdating = False For Each ws In ActiveWorkbook.Worksheets If ws.Name = "Summary" Then GoTo NextSheet With ws If IsEmpty(.Range("A2").Value) Then GoTo NextSheet .Range("A2", .Range("A" & Rows.Count).End(xlUp)).Resize(, 2).Copy Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial Range("C" & Rows.Count).End(xlUp).Offset(1).Value = ws.Name End With NextSheet: Next ws Application.ScreenUpdating = True End Sub The following will execute upon any entry in any cell in Column B of any of the other sheets. Note that this macro is a sheet macro and MUST be placed in the sheet module of EACH of the other sheets (not the Summary sheet). HTH Otto Private Sub Worksheet_Change(ByVal Target As Range) If Target.Count 1 Then Exit Sub If IsEmpty(Target.Value) Then Exit Sub If Target.Column = 2 Then Application.ScreenUpdating = False With Sheets("Summary") Target.Offset(, -1).Resize(, 2).Copy .Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial .Range("C" & Rows.Count).End(xlUp).Offset(1).Value = Me.Name End With Application.ScreenUpdating = True End If End Sub "SITCFanTN" wrote in message ... Hi Otto, As you can see, I'm sort of a beginner at this so I sometimes do things the long way rather than the most efficient. I was going to prefill the sheets name into column E of the worksheets, then copy Col A, B and E into the Summary worksheet. Then I thought it would be more efficient to just copy Col A and B and the sheet name with code into the Summary sheet thus saving some steps. On the first 4 sheets, the only required columns for data entry are Col A and B so with that said is it possible when the person enters their info in Col A and B, after they move off B the data populates the Summary Page. One other question, I read in one of the other postings that if a sheet is protected, a macro has to be used rather than a command button to evoke code, is that true. I would rather use a command button for another function separate from this , however I do need to protect my sheet so that my not be an option for me. I'm trying to think the most efficient and logical way, does this sound correct to you Otto. I also appreciate your explanation of the code...I'm learning as I go and I appreciate your extra effort. Thank you. "Otto Moehrbach" wrote: The line: ..Range("A2", .Range("A" & Rows.Count).End(xlUp)).Resize(,2).Copy is the line that sets up to copy Columns A & B. The first part: ..Range("A2", .Range("A" & Rows.Count).End(xlUp)) is all of Column A. The last part, Resize(,2), increases the range to copy to encompass both Columns A & B. The macro, as written, copies Column E from the other sheets and pastes it into Column C of the Summary sheet. You say you want the name of the sheet to appear in Column C. What do you want to do with the data from Column E? Copying a row from the other sheets while data is being entered presents a problem. Excel needs to have a trigger for when to do the copying. Entering data in any cell can be the trigger but that brings up the problem of where to paste the data in the Summary sheet, i.e, in what row? One solution would be to use any entry in Column G (the last column) as the trigger. Then Excel would copy Columns A, B, and E, of that row at that time. Of course, this may not work for you if data entry is not made by columns in order. Post back and answer the questions and your decision on how to trigger Excel to copy. Otto "JOUIOUI" wrote in message ... This worked great for me Otto, thanks, I just have two more questions for you. Is there a way I can have the data prefill the summary as it is entered on the other sheets rather than run a macro to have the summary populate? My other question is I would like the sheet name to appear in column C on the summary page for each item copied to the summary page. Oh one more question, I was able to understand your code, but where are calling the Col B information, I only see A and E? Thanks again for your help. "Otto Moehrbach" wrote: This macro should do what you want. I assumed that the file has 5 sheets as you said and that one is named Summary. This macro copies what you say you want from all the other sheets to the Summary sheet. HTH Otto Sub CopyToSummary() Dim ws As Worksheet Application.ScreenUpdating = False For Each ws In ActiveWorkbook.Worksheets If ws.Name = "Summary" Then GoTo NextSheet With ws If IsEmpty(.Range("A2").Value) Then GoTo NextSheet .Range("A2", .Range("A" & Rows.Count).End(xlUp)).Resize(, 2).Copy Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial .Range("E2", .Range("E" & Rows.Count).End(xlUp)).Copy Range("C" & Rows.Count).End(xlUp).Offset(1).PasteSpecial End With NextSheet: Next ws Application.ScreenUpdating = True End Sub "JOUIOUI" wrote in message ... I'll be creating a workbook with 5 sheets that folks will use each day to submit information on. The first 4 sheets will have data entered in columns A, B, C, D, E, F and G. The number of rows each day with data entered is variable and on some days a sheet may even be empty. My goal is to have the 5th Sheet be a summary page and is titled, "Summary". On this summary sheet I only want to copy the informaiton from Columns A, B and E from the sheets titled "First", "Second", "Third" and "Fourth" only when data is entered beginning on row 2 since there are column headings on each sheet. I'm just not sure how to designate to copy the text from one sheet to another whe when the number of rows pupulated on each sheet is unknown. Any ideas are appreciated, thank you |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
auto copy from one sheet to another
George
Do you want the data that is copied from sheet 2 to sheet 1 to be placed in some particular place in sheet 1? Maybe below what's there already? You say that if the value in Column A is 0, "you" can change it to 1. Who is "you"? Do you mean the code (me?) can do that or do you mean the user can do that before the code runs? You say you want this copying to be automatic. Automatic on what cue? In other words, when do you want this copying to happen? You don't say "copy". Instead you say you want sheet 2 to "have" .......... from sheet 1. Do you want the data copied or moved? The code will need to have some way to determine the number of columns to copy. Does your data have column headers for all columns? In what row? Otto "George" wrote in message oups.com... Hi, Otto: After reading your post and I am sure you can solve my problem. I have the similiar situation. I have a workbook which has multiple worksheets. For simplicity, we name two of the worksheets as sheet 1 and sheet 2. Sheet 1 is my input page. From the following, you can see that column A under sheet 1 has a binary input 0-1 as the default. If the default value for a row under column A on sheet 1 is 1, you are NOT allowed to change it to 0. However, if the default value is 0, you can modify it to 1 or leave it as 0. What I need is to have a summary page on sheet 2 which will have all the rows from sheet 1 with a vaule of 1 under column A IN THE SAME ORDER AND SAME FORMAT. Also, I would like this to be an automatic procedure. Could you please help me on this? The following is an illustration: On sheet 1 (this is after your modification of 0 to 1) A B C D E F G m 1 1 B1 C1 F1 2 0 D2 G2 3 0 C3 E3 m3 4 1 B4 F4 n 1 bn en mn ******On sheet 2******* A B C D E F G m 1 1 B1 C1 F1 2 1 B4 F4 n 1 bn en mn Thanks again, George Otto Moehrbach wrote: About the macro or command button to run a macro when the sheet is protected. A command button is simply a means of running a macro. Also, the command button WILL run the macro even if the sheet is protected. Here is the same macro modified so that it copies only Columns A & B and puts the sheet name in Column C. You can run this macro from a command button. This macro will NOT run automatically when you enter something in Column B. Sub CopyToSummary() Dim ws As Worksheet Application.ScreenUpdating = False For Each ws In ActiveWorkbook.Worksheets If ws.Name = "Summary" Then GoTo NextSheet With ws If IsEmpty(.Range("A2").Value) Then GoTo NextSheet .Range("A2", .Range("A" & Rows.Count).End(xlUp)).Resize(, 2).Copy Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial Range("C" & Rows.Count).End(xlUp).Offset(1).Value = ws.Name End With NextSheet: Next ws Application.ScreenUpdating = True End Sub The following will execute upon any entry in any cell in Column B of any of the other sheets. Note that this macro is a sheet macro and MUST be placed in the sheet module of EACH of the other sheets (not the Summary sheet). HTH Otto Private Sub Worksheet_Change(ByVal Target As Range) If Target.Count 1 Then Exit Sub If IsEmpty(Target.Value) Then Exit Sub If Target.Column = 2 Then Application.ScreenUpdating = False With Sheets("Summary") Target.Offset(, -1).Resize(, 2).Copy .Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial .Range("C" & Rows.Count).End(xlUp).Offset(1).Value = Me.Name End With Application.ScreenUpdating = True End If End Sub "SITCFanTN" wrote in message ... Hi Otto, As you can see, I'm sort of a beginner at this so I sometimes do things the long way rather than the most efficient. I was going to prefill the sheets name into column E of the worksheets, then copy Col A, B and E into the Summary worksheet. Then I thought it would be more efficient to just copy Col A and B and the sheet name with code into the Summary sheet thus saving some steps. On the first 4 sheets, the only required columns for data entry are Col A and B so with that said is it possible when the person enters their info in Col A and B, after they move off B the data populates the Summary Page. One other question, I read in one of the other postings that if a sheet is protected, a macro has to be used rather than a command button to evoke code, is that true. I would rather use a command button for another function separate from this , however I do need to protect my sheet so that my not be an option for me. I'm trying to think the most efficient and logical way, does this sound correct to you Otto. I also appreciate your explanation of the code...I'm learning as I go and I appreciate your extra effort. Thank you. "Otto Moehrbach" wrote: The line: ..Range("A2", .Range("A" & Rows.Count).End(xlUp)).Resize(,2).Copy is the line that sets up to copy Columns A & B. The first part: ..Range("A2", .Range("A" & Rows.Count).End(xlUp)) is all of Column A. The last part, Resize(,2), increases the range to copy to encompass both Columns A & B. The macro, as written, copies Column E from the other sheets and pastes it into Column C of the Summary sheet. You say you want the name of the sheet to appear in Column C. What do you want to do with the data from Column E? Copying a row from the other sheets while data is being entered presents a problem. Excel needs to have a trigger for when to do the copying. Entering data in any cell can be the trigger but that brings up the problem of where to paste the data in the Summary sheet, i.e, in what row? One solution would be to use any entry in Column G (the last column) as the trigger. Then Excel would copy Columns A, B, and E, of that row at that time. Of course, this may not work for you if data entry is not made by columns in order. Post back and answer the questions and your decision on how to trigger Excel to copy. Otto "JOUIOUI" wrote in message ... This worked great for me Otto, thanks, I just have two more questions for you. Is there a way I can have the data prefill the summary as it is entered on the other sheets rather than run a macro to have the summary populate? My other question is I would like the sheet name to appear in column C on the summary page for each item copied to the summary page. Oh one more question, I was able to understand your code, but where are calling the Col B information, I only see A and E? Thanks again for your help. "Otto Moehrbach" wrote: This macro should do what you want. I assumed that the file has 5 sheets as you said and that one is named Summary. This macro copies what you say you want from all the other sheets to the Summary sheet. HTH Otto Sub CopyToSummary() Dim ws As Worksheet Application.ScreenUpdating = False For Each ws In ActiveWorkbook.Worksheets If ws.Name = "Summary" Then GoTo NextSheet With ws If IsEmpty(.Range("A2").Value) Then GoTo NextSheet .Range("A2", .Range("A" & Rows.Count).End(xlUp)).Resize(, 2).Copy Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial .Range("E2", .Range("E" & Rows.Count).End(xlUp)).Copy Range("C" & Rows.Count).End(xlUp).Offset(1).PasteSpecial End With NextSheet: Next ws Application.ScreenUpdating = True End Sub "JOUIOUI" wrote in message ... I'll be creating a workbook with 5 sheets that folks will use each day to submit information on. The first 4 sheets will have data entered in columns A, B, C, D, E, F and G. The number of rows each day with data entered is variable and on some days a sheet may even be empty. My goal is to have the 5th Sheet be a summary page and is titled, "Summary". On this summary sheet I only want to copy the informaiton from Columns A, B and E from the sheets titled "First", "Second", "Third" and "Fourth" only when data is entered beginning on row 2 since there are column headings on each sheet. I'm just not sure how to designate to copy the text from one sheet to another whe when the number of rows pupulated on each sheet is unknown. Any ideas are appreciated, thank you |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Auto Copy/autofill Text from sheet to sheet if meets criteria | Excel Discussion (Misc queries) | |||
| auto copy from one sheet to another | Excel Discussion (Misc queries) | |||
| Auto Copy Text from one work sheet to another | Excel Worksheet Functions | |||
| Auto copy data from 1 sheet into another | Excel Discussion (Misc queries) | |||
| How do I auto copy text in a cell from one sheet to another | Excel Worksheet Functions | |||
Linear Mode
