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