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

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" <(E-Mail Removed)> wrote in message
news:36138BF8-BF09-45D7-A6C7-(E-Mail Removed)...
> 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

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" <(E-Mail Removed)> wrote in message
> news:36138BF8-BF09-45D7-A6C7-(E-Mail Removed)...
> > 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
>
>
>

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" <(E-Mail Removed)> wrote in message
news:EFBB9E0A-E506-4103-9AF8-(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
>> news:36138BF8-BF09-45D7-A6C7-(E-Mail Removed)...
>> > 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
>>
>>
>>

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" <(E-Mail Removed)> wrote in message
> news:EFBB9E0A-E506-4103-9AF8-(E-Mail Removed)...
> > 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" <(E-Mail Removed)> wrote in message
> >> news:36138BF8-BF09-45D7-A6C7-(E-Mail Removed)...
> >> > 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
> >>
> >>
> >>
>
>
>

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" <(E-Mail Removed)> wrote in message
news:E1107620-3B78-40C8-8614-(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
>> news:EFBB9E0A-E506-4103-9AF8-(E-Mail Removed)...
>> > 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" <(E-Mail Removed)> wrote in message
>> >> news:36138BF8-BF09-45D7-A6C7-(E-Mail Removed)...
>> >> > 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
>> >>
>> >>
>> >>
>>
>>
>>

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" <(E-Mail Removed)> wrote in message
> news:E1107620-3B78-40C8-8614-(E-Mail Removed)...
> > 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" <(E-Mail Removed)> wrote in message
> >> news:EFBB9E0A-E506-4103-9AF8-(E-Mail Removed)...
> >> > 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" <(E-Mail Removed)> wrote in message
> >> >> news:36138BF8-BF09-45D7-A6C7-(E-Mail Removed)...
> >> >> > 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
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>
>

You probably placed the macro in a regular or standard module. The macro
will not fire if you did that. You need to place it in the sheet module of
each of the "other" sheets. To access a sheet module, right-click on the
sheet tab, select View Code, and paste the macro into that module. "X" out
of that module to return to your sheet. If you wish, send me an email and
I'll send you the small file I used to develop the code. That file has both
macros in it properly placed. My email address is (E-Mail Removed).
Remove the "nop" from this address. HTH Otto
"Otto Moehrbach" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:E1107620-3B78-40C8-8614-(E-Mail Removed)...
>> 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" <(E-Mail Removed)> wrote in message
>>> news:EFBB9E0A-E506-4103-9AF8-(E-Mail Removed)...
>>> > 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" <(E-Mail Removed)> wrote in message
>>> >> news:36138BF8-BF09-45D7-A6C7-(E-Mail Removed)...
>>> >> > 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
>>> >>
>>> >>
>>> >>
>>>
>>>
>>>
>
>

Thanks Otto, I'm all set, adding the code to each sheet works like a charm.
I so appreciate your explanation!!

"Otto Moehrbach" wrote:

> You probably placed the macro in a regular or standard module. The macro
> will not fire if you did that. You need to place it in the sheet module of
> each of the "other" sheets. To access a sheet module, right-click on the
> sheet tab, select View Code, and paste the macro into that module. "X" out
> of that module to return to your sheet. If you wish, send me an email and
> I'll send you the small file I used to develop the code. That file has both
> macros in it properly placed. My email address is (E-Mail Removed).
> Remove the "nop" from this address. HTH Otto
> "Otto Moehrbach" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > 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" <(E-Mail Removed)> wrote in message
> > news:E1107620-3B78-40C8-8614-(E-Mail Removed)...
> >> 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" <(E-Mail Removed)> wrote in message
> >>> news:EFBB9E0A-E506-4103-9AF8-(E-Mail Removed)...
> >>> > 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" <(E-Mail Removed)> wrote in message
> >>> >> news:36138BF8-BF09-45D7-A6C7-(E-Mail Removed)...
> >>> >> > 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
> >>> >>
> >>> >>
> >>> >>
> >>>
> >>>
> >>>
> >
> >
>
>
>

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" <(E-Mail Removed)> wrote in message
> news:E1107620-3B78-40C8-8614-(E-Mail Removed)...
> > 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" <(E-Mail Removed)> wrote in message
> >> news:EFBB9E0A-E506-4103-9AF8-(E-Mail Removed)...
> >> > 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" <(E-Mail Removed)> wrote in message
> >> >> news:36138BF8-BF09-45D7-A6C7-(E-Mail Removed)...
> >> >> > 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
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>