auto copy from one sheet to another

[Guest]

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

 

[Otto Moehrbach]

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

 

[Guest]

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]

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

 

[Guest]

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]

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

 

[Guest]

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]

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 address removed).
Remove the "nop" from this address. HTH Otto

 

[Guest]

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

 

[George]

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]

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]

Hi, Otto:

Thanks for your time and effors in trying to help me out! I am so sorry
that my explaination is not clear enough and make you so confused.
Basically, what I need is the user can change 0 to 1 or leave it like
it is on sheet 1 BEFORE the code runs. Once the user FINISH this step,
the user want a summary page (say sheet 1) to be generated
automatically or by click a button. This summary page (sheet 2) will
have ALL ROWS with colum B through column K from sheet 1 with value 1
under column A, IN THE SAME ORDER AND SAME FORMAT. In other words, all
rows with column B through column K with input 1 under column A on
sheet 1 will be copied OR moved to sheet 2. Whatever those rows and
columns look like on sheet 1, they will have the same look on sheet 2
except the row number might be changed. I do not have column headers
for any columns but I can make one if needed. Please note that the
column width on sheet 1 varies.

Please advise!

Thanks,

George
*****************************************************************************************************

 

[Otto Moehrbach]

George
You say you want the Summary sheet "generated". I take it then that the
"Summary sheet" doesn't exist and you want the code to create it.
What I would do is simply have the code copy the sheet, name the new
sheet Summary (or something), then loop through all the occupied cells of
the Summary sheet and delete every row that doesn't have "1" in Column A,
then delete Column A. Done.
Does that sound like what you want? Post back if this is what you want and
you need some code for this. Otto

 

[George]

Hi, Otto:

I think your idea is logical and it is almost what I need. However, it
is not exactly what I need due to my unclear explanation. Basically,
both sheet 1 and sheet 2 (we can rename sheet 2 as Summary Page
manually) are already in my workbook. After users finish to change 0 to
1 or leave it like it is under column A on sheet 1, I want to copy all
information (under column A through K on sheet 1 for each row which has
"1" in column A) to sheet 2 under column A throug K. It is equivalent
to say that sheet 2 is identical to sheet 1 (column A through K) EXCEPT
rows with 0 under column A will be deleted. For example, under sheet 1,
if cell A1 is 1, then information in A1, B1, C1, D1, E1, F1, G1,H1, I1,
J1 and K1 should be copied to A1, B1, C1, D1, E1, F1, G1, H1, I1, J1
and K1 on SHEET 2, respectively. Another example, under sheet 1, if
cell A2 is 0 and A3 is 1, then anything in cell A2, B2, C2, D2, E2,F2,
G2, H2, I2, J2 AND K2 will not be copied to sheet 2 but information in
cell A3, B3, C3, D3, E3, F3, G3, H3, I3, J3 and K3 ON sheet 1 will be
copied to A2, B2, C2, D2, E2,F2, G2, H2, I2, J2 AND K2 on sheet 2,
respectively, etc. Please note that some cells under column B through K
on sheet 1 might be empty even though it is possible to have "1" in
column A in the same row. Please also note that I do not want to modify
anything on sheet 1 except changing 0 to 1 under column A.

Is this clear?

I look forward to hearing from you! Thank you so much for your help!

George
************************************************************************************************

 

[Otto Moehrbach]

George

Here is a macro that will do what you want. This macro is
written with the following assumptions:

The sheet to which you want the data copied is named "Summary". Note that
the sheet name "Summary" is hard-written into the code. You can change that
in the code as you wish, but the file must contain a sheet by the name that
is in the macro.

The data will be pasted to the Summary sheet starting in row 1, therefore
anything that is in the Summary sheet in Columns A:K in rows 1 to however
many rows get pasted, will be overwritten.

The sheet name of the sheet holding the data to be copied (call this the
data sheet) is not important. However, that sheet must be the active sheet
when this macro runs.

All rows that have a "1" in Column A of the data sheet will be copied.

The data in the data sheet will not be altered in any way by this macro.

HTH Otto

Sub Copy1()

Dim RngColA As Range

Dim i As Range

Dim Dest As Range

Set RngColA = Range("A1", Range("A" & Rows.Count).End(xlUp))

Set Dest = Sheets("Summary").Range("A1")

For Each i In RngColA

If i.Value = 1 Then

i.Resize(, 11).Copy Dest

Set Dest = Dest.Offset(1)

End If

Next i

End Sub

 

[George]

Hi, Otto:

I am a kind of new to Excel. Could you please tell me in more details
on how to use this code? Is it possible to make a Macro Button
associated with this code such that after users finish to change 0 to 1
or leave it like it is on the data sheet, they click this button, then
all rows with 1 under column A on this sheet will be copied and pasted
to the Summary sheet?

Thank you so much for your help and patience. Have a great day!

George
******************************************************************************************************

 

[George]

Hi, Otto;

Please never mind my previous post if the following is the right
procedure:

Open my workbook;
By click "Alt + F11" to open up the code window. Make the window of
"Properties" and "Project" visible. Double click any module and paste
your code into the code pane, then close the whole window by "Alt + Q".
Now we can go back to our data sheet. Make a macro button and assign
"Copy1" to it. Then by clicking this button, all rows with 1 under
column A on data sheet will be copied and pasted to Summary Page. It is
very successful and it is all your credit.

However, I do find another problem. That is, I have some formua(s) in
my data sheet. After I finish to change 0 to 1 or leave it like it is,
then I click the button, go to Summary Page, I found the references of
the formula are lost. Is there any way to copy the formula(s) from data
sheet to Summary Page? If this is too difficult for you, we can do an
alternative: Copy from data sheet and paste it to Summary Page as
VALUES.

Please advise! I appreciate your help!

George
*********************************************************************************************************

 

[George]

Hi, Otto:

Addition to my previous post:

If you want to copy and paste VALUES ONLY, is there any way to keep the
FONT FORMAT and the cell border lines?

I am so sorry to give you a lot of troubles but I do trust that you can
figure it out! Thank you so much!

George
***********************************************************************

 

[George]

Hi, Otto:

I think this is the last question. If I want to switch input column
from Column A to Column M, how to modify the code?

Thanks,

George
**************************************************************************************************

 

[George]

Hi, Otto:

Could you please help me out with the first priority of the following
as posted earlier:

1) How to switch my input column on data page from Column A to Column L
on the code ?
2) How to copy from data page (column A:K) and paste it as Values to
Summary page based on the "1" under column A on data page?

Please make the above as TWO seperate programs (subs).

I really really appreciate your help. Thank you so much and have a
great day!

George
**********************************************************************************************