If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Macro Help - Jumping Sheets
Hi All
I have a workbook which contains 200 sheets. What I need to do is to insert a "cover" sheet and then create a macro pick up 5 cells from each worksheet (the cells don't alter) and copy and paste the values into the Cover Sheet going progressively down a column, with a different column for each cell. so i need the macro to go to Sheet1 get the cells paste into correct columns and then go to Sheet2 and repeat the process, until there are no more sheets left. Eg. i need b3, b4, c26, e26, h26 from each worksheet in the whole workbook to be summarised so column A on the Cover Sheet has B3 Sheet1 B3 Sheet2 B4 Sheet3 etc... If anyone can help it would be a massive time saver! Cheers Nick |
#2
|
|||
|
|||
Macro Help - Jumping Sheets
Nick,
Not to mince words, but you said you want the data summarized, yet you seem to be asking to have it detailed (each listed). If you want a total of all the b3 cells, you can put something like this in your cover sheet: =SUM('Sheet 1:Sheet n'!B3) =SUM('Sheet 1:Sheet n'!B4) If you want the cells listed in column, I don't understand the pattern. -- Earl Kiosterud mvpearl omitthisword at verizon period net ------------------------------------------- "NICK" wrote in message ... Hi All I have a workbook which contains 200 sheets. What I need to do is to insert a "cover" sheet and then create a macro pick up 5 cells from each worksheet (the cells don't alter) and copy and paste the values into the Cover Sheet going progressively down a column, with a different column for each cell. so i need the macro to go to Sheet1 get the cells paste into correct columns and then go to Sheet2 and repeat the process, until there are no more sheets left. Eg. i need b3, b4, c26, e26, h26 from each worksheet in the whole workbook to be summarised so column A on the Cover Sheet has B3 Sheet1 B3 Sheet2 B4 Sheet3 etc... If anyone can help it would be a massive time saver! Cheers Nick |
#3
|
|||
|
|||
Macro Help - Jumping Sheets
Hi
have a look at: http://www.rondebruin.nl/copy2.htm -- Regards Frank Kabel Frankfurt, Germany NICK wrote: Hi All I have a workbook which contains 200 sheets. What I need to do is to insert a "cover" sheet and then create a macro pick up 5 cells from each worksheet (the cells don't alter) and copy and paste the values into the Cover Sheet going progressively down a column, with a different column for each cell. so i need the macro to go to Sheet1 get the cells paste into correct columns and then go to Sheet2 and repeat the process, until there are no more sheets left. Eg. i need b3, b4, c26, e26, h26 from each worksheet in the whole workbook to be summarised so column A on the Cover Sheet has B3 Sheet1 B3 Sheet2 B4 Sheet3 etc... If anyone can help it would be a massive time saver! Cheers Nick |
Thread Tools | |
Display Modes | |
|
|