Auto Copying Data

Dear all,

I've a workbook with several worksheets, namely "Jan06", "Feb06"......and
"Summary". This Summary worksheet consolidates all data from columns A, B & E
of all other worksheets in the same workbook(they are identical). Every time
when I filled in all records for that month, I've to copy the data from
columns A, B & E to the "Summary" worksheet for further analysis purpose. Is
there a macro which can copy those required data from any of the worksheets
automatically to the first blank row in the "Summary" worksheet? If yes,
please write me that macro.

Thanks in advance.

Best regards.

Perhaps you don't need a macro to do it! Place this formula in Summary!A2 and
copy it to the other columns! Make sure that sheets "Jan06", "Feb06"......
are after each other!

=SUM(Jan06:Feb06!A2)

Regards,
Stefi

„Freshman” ezt �*rta:

> Dear all,
>
> I've a workbook with several worksheets, namely "Jan06", "Feb06"......and
> "Summary". This Summary worksheet consolidates all data from columns A, B & E
> of all other worksheets in the same workbook(they are identical). Every time
> when I filled in all records for that month, I've to copy the data from
> columns A, B & E to the "Summary" worksheet for further analysis purpose. Is
> there a macro which can copy those required data from any of the worksheets
> automatically to the first blank row in the "Summary" worksheet? If yes,
> please write me that macro.
>
> Thanks in advance.
>
> Best regards.

Hi Stefi,

Thanks for your help. May be I haven't raised my question clear. Sorry for
the confusion. My question is not going to total all values in A1 of other
worksheets to cell A1 in Summary worksheet. I want to copy every record in
columns A, B & E of every worksheet to the Summary worksheet then I use them
for analysis in a pivot table. Since the records are filled in month after
month, so I want the completed records of the current month can be
automatically copy to the row under the last record in the Summary worksheet,
i.e. May06's records under Apr06's record. I hope this can make my question
clear. Can you help in this aspect?

Thanks once again.

Regards.


"Stefi" wrote:

> Perhaps you don't need a macro to do it! Place this formula in Summary!A2 and
> copy it to the other columns! Make sure that sheets "Jan06", "Feb06"......
> are after each other!
>
> =SUM(Jan06:Feb06!A2)
>
> Regards,
> Stefi
>
> „Freshman” ezt �*rta:
>
> > Dear all,
> >
> > I've a workbook with several worksheets, namely "Jan06", "Feb06"......and
> > "Summary". This Summary worksheet consolidates all data from columns A, B & E
> > of all other worksheets in the same workbook(they are identical). Every time
> > when I filled in all records for that month, I've to copy the data from
> > columns A, B & E to the "Summary" worksheet for further analysis purpose. Is
> > there a macro which can copy those required data from any of the worksheets
> > automatically to the first blank row in the "Summary" worksheet? If yes,
> > please write me that macro.
> >
> > Thanks in advance.
> >
> > Best regards.

"Freshman" wrote:
> .. I want to copy every record in columns A, B & E
> of every worksheet to the Summary worksheet then I use them
> for analysis in a pivot table. Since the records are filled in month after
> month, so I want the completed records of the current month can be
> automatically copy to the row under the last record in the Summary worksheet,
> i.e. May06's records under Apr06's record...

If the # of sheets to be auto-summarized isn't too many ..
say, data from up to 4 sheets to auto-stack into a summary sheet,
then one possible formulas play to achieve it could be along the lines
in this response to a similar query:

http://tinyurl.com/l7kst

The sample construct mentioned is available at:
http://www.savefile.com/files/4567345
Dynamic record selection from multiple worksheets.xls

--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Hi Max,

Thanks for you help again. It is very nice of you. Cheers.

"Max" wrote:

> "Freshman" wrote:
> > .. I want to copy every record in columns A, B & E
> > of every worksheet to the Summary worksheet then I use them
> > for analysis in a pivot table. Since the records are filled in month after
> > month, so I want the completed records of the current month can be
> > automatically copy to the row under the last record in the Summary worksheet,
> > i.e. May06's records under Apr06's record...
>
> If the # of sheets to be auto-summarized isn't too many ..
> say, data from up to 4 sheets to auto-stack into a summary sheet,
> then one possible formulas play to achieve it could be along the lines
> in this response to a similar query:
>
> http://tinyurl.com/l7kst
>
> The sample construct mentioned is available at:
> http://www.savefile.com/files/4567345
> Dynamic record selection from multiple worksheets.xls
>
> --
> Max
> Singapore
> http://savefile.com/projects/236895
> xdemechanik
> ---

Hi Max,

I check your formula again and I've missed something. Please note that I've
12 primary worksheets + 1 Summary worksheet. Each primary worksheet has
different number of records and records in worksheets are ranging from 80 to
120. Are the number of worksheets and records are not suitable for your
formula? Please advise.

Sorry to bother you again.

Thanks & regards.

"Max" wrote:

> "Freshman" wrote:
> > .. I want to copy every record in columns A, B & E
> > of every worksheet to the Summary worksheet then I use them
> > for analysis in a pivot table. Since the records are filled in month after
> > month, so I want the completed records of the current month can be
> > automatically copy to the row under the last record in the Summary worksheet,
> > i.e. May06's records under Apr06's record...
>
> If the # of sheets to be auto-summarized isn't too many ..
> say, data from up to 4 sheets to auto-stack into a summary sheet,
> then one possible formulas play to achieve it could be along the lines
> in this response to a similar query:
>
> http://tinyurl.com/l7kst
>
> The sample construct mentioned is available at:
> http://www.savefile.com/files/4567345
> Dynamic record selection from multiple worksheets.xls
>
> --
> Max
> Singapore
> http://savefile.com/projects/236895
> xdemechanik
> ---

"Freshman" wrote:
> I check your formula again and I've missed something. Please note that I've
> 12 primary worksheets + 1 Summary worksheet. Each primary worksheet has
> different number of records and records in worksheets are ranging from 80 to
> 120. Are the number of worksheets and records are not suitable for your
> formula? Please advise.

Think the # of records (80 to 120) per "primary" (ie source) sheet is not a
problem. But the # of primary sheets (12) may prove to be a challenge. As it
stands, the sample is structured for 4 source sheets [== 4 layers of nested
IFs <g>]. Let me see whether I can extend the sample to suit your specs.
Give me a couple of days to ponder/work on this. I'll post back here to
update you (promise!).

In the interim, pl monitor your thread for other insights / programming
solutions from others.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---