Copy rows from 20 sheets into 1 sheet 2

[lunker55]

Is there a way to copy 20 rows in 10 columns on 20 different sheets into 1 sheet in the same workbook without code?
(Did that make sense?)
I don't know VBA yet.

 

[TonyJollans]

Hi lunker55,

No, it doesn't really make sense

Without code you do it manually - go to each sheet in turn, copy the range, go to the point in your destination sheet where you want it and paste. That can't be what you mean, so can you give a bit more detail please? Do you want updates in the various sheets reflected in the one? Are the ranges fixed or variable? And anything else which might help.

Enjoy,
Tony

------------------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading FAQ222-2244 before you ask a question.

 

[lunker55]

Sorry, i'll try again:
As I enter data into a row in any sheet between sheet 1 and sheet 20, I want that row of data copied into sheet 21.
Yes, the ranges are fixed.

A little background:
Each sheet contains a one week schedule. As product is scheduled, it is entered in the correct week(sheet). I need all 20 weeks (20 sheets) compiled into one sheet to do a quick autofilter instead of autofiltering every week(sheet).
I hope that helps.

 

[TonyJollans]

Hi lunker55,

I think you should probably consider redesigning your workbook, but ..

Let's say your data is in columns A to J, and rows 2 to 21 (with headings in row 1) on each sheet. On Sheet21, enter your headings in row 1, then

Select A2:J21, Type =Sheet1!A1, Press <Ctrl><Enter>
Select A22:J41, Type =Sheet2!A1, Press <Ctrl><Enter>
Select A42:J61, Type =Sheet3!A1, Press <Ctrl><Enter>
:
:

:
:
Select A382:J401, Type =Sheet20!A1, Press <Ctrl><Enter>

Now you will have everything you do in sheets 1 to 20 reflected directly in Sheet 21 so you can do your filtering or whatever else you want.

Enjoy,
Tony

------------------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading FAQ222-2244 before you ask a question.

 

[SkipVought]

I agree with Tony, you need to redesign your workbook.

Shooting from the hip...

1. All your data should be in ONE TABLE -- easier to maintain and easier to data mine, analyze & report.

2. Add a column for date and a column for week...

=INT(TheDate/7)

will result in a number representing a week. You can adjust the week-break point by adding/subtracting a value from 1 to 3 for instance...

=INT((TheDate+1)/7)

Now ther is one table to maintain and data is at your fingertips. You have a week value to analyze/report by that period.



Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[lunker55]

Thank you TonyJollans.
That is exactly what I want.
I know it is a crude way and the workbook is not very efficient, but this will work for now.
Thanks again,
joe

 

[lunker55]

Skip:
Nice suggestion! I never thought of it that way.
I have been compiling many workbooks into one with many sheets. Maybe I can do it in one sheet or two.
I will try it.
Thanks,
joe

 

[SkipVought]

Joe,

If you choose to invest the time and effort (and believe me, it will pay off now and in future efforts) check out this Microsoft link and download the SpreadSheetDesignData.xls workbook.

http://office.microsoft.com/assista...ID=HA010563211033&CTT=6&Origin=EC010553071033

Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884