combine columns of data from multiple worksheets into one

[Michael]

Hi

Sorry I trawled through the questions and none really applied to my issue.

Here is the scenario - I have 80 seperate worksheets in one spreadsheet
file. Column A is the list of headings (questions in my case) on each row,
and Column B is the response (data).

Each worksheet is set out like a survey so there are some rows that contain
a "heading" but no corresponding data (ie the field in column B is blank for
asthetic purposes).

So that I can run pivot tables on the collected data, I want to combine all
of the information from the 80 odd worksheets into one. They are all based on
a template, so Column A will always be the same. So what I want is to bring
all of the data in Columb B from each worksheet. I want each column to be
identifed by the worksheet name.

Is this possible??

As a footnote, FYI I describe myself as an intermediate user (so if you can,
dumb it down for me), and I may have a need of adding additional worksheets
and therefore data in the future. Finally I really really do not want to have
to cut and paste each set of data...

 

[Sandy Crowley]

I'm not sure if I have the scenario right. Assuming that Sheet1, B1 is where
you want to start.

First, I would select all the sheets (right click on Sheet 1 and choose
Select All Sheets). Click in B1, Sheet1 (answer column for all 80 sheets) and
type in =MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,256) hit
Enter

Right click the sheets and ungroup.

This will put the sheet name in B1 for each sheet.

If you want to put the answers from Sheet2, B1:B256, into Column B of Sheet
1 type into B1, Sheet1 =Sheet2!$B:$B

You can then click on the fill box handle to drag to the right and then down
as far as you need it.

This will place the answers of each sheet in consecutive columns with the
Sheet Name at the top as a head for each column.

Hope I didn't miss the mark to badly.

 

[Sandy Crowley]

Michael,

If you want to concatenate all the answers into one cell, that is another
issue.

 

[Ron de Bruin]

Hi Michael

See this page for a code solution
http://www.rondebruin.nl/copy2.htm

Download the example workbook
There is a column example

 

[Michael]

Hi Sandy,

This is pretty much exactly want I want to do.

However I am having some problems. Your 1st step worked fantasticall, with
each worksheet name being placed in B1 of the corresponding worksheet.

However when I follow the next step "If you want to put the answers from
Sheet2, B1:B256, into Column B of Sheet
1 type into B1, Sheet1 =Sheet2!$B:$B

You can then click on the fill box handle to drag to the right and then down
as far as you need it. ", but when I try to fill to the right it is just
copying the original formula, not updating for each individual worksheet. Is
there a way to do it for all worksheets automatically?

I'm not sure if I have the scenario right. Assuming that Sheet1, B1 is where
you want to start.

First, I would select all the sheets (right click on Sheet 1 and choose
Select All Sheets). Click in B1, Sheet1 (answer column for all 80 sheets) and
type in =MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,256) hit
Enter

Right click the sheets and ungroup.

This will put the sheet name in B1 for each sheet.

If you want to put the answers from Sheet2, B1:B256, into Column B of Sheet
1 type into B1, Sheet1 =Sheet2!$B:$B

You can then click on the fill box handle to drag to the right and then down
as far as you need it.

This will place the answers of each sheet in consecutive columns with the
Sheet Name at the top as a head for each column.

Hope I didn't miss the mark to badly.
--
Thank you,

scrowley(AT)littleonline.com

Hi

Sorry I trawled through the questions and none really applied to my issue.

Here is the scenario - I have 80 seperate worksheets in one spreadsheet
file. Column A is the list of headings (questions in my case) on each row,
and Column B is the response (data).

Each worksheet is set out like a survey so there are some rows that contain
a "heading" but no corresponding data (ie the field in column B is blank for
asthetic purposes).

So that I can run pivot tables on the collected data, I want to combine all
of the information from the 80 odd worksheets into one. They are all based on
a template, so Column A will always be the same. So what I want is to bring
all of the data in Columb B from each worksheet. I want each column to be
identifed by the worksheet name.

Is this possible??

As a footnote, FYI I describe myself as an intermediate user (so if you can,
dumb it down for me), and I may have a need of adding additional worksheets
and therefore data in the future. Finally I really really do not want to have
to cut and paste each set of data...

 

[Michael]

ok I'm sorry but I am really crap when it comes to VB. I tried copying it
over but I really really don't know what I am doing. I copied your example in
its entirety just to test before trying to manipulate and I got an error
message "Compile Error sub or function not defined" and it then highlights
"LastRow" within the following text - 'Find the last row with data on the
DestSh
Last = LastRow(DestSh)

 

[Ron de Bruin]

From my site:

Important:
The macro examples use the LastRow or LastCol function that you can find in the last section of this page.

 

[Michael]

I'm sorry this is sending me insane, I will have to avoid macro as I am not
experienced enough to understand the different references throughout your
site. I was hoping there would be a direct formula so that if I update any
information, the main worksheet will update, which may not appear in the
macro without me running it again.

Will just try and link it all manually. Thanks for your help though.