How do i consolidate 13 spreadsheets onto one
 

I have 13 spreadsheets on one workbook and i would like to have all of them
on one spreadsheet. How do i do that w/o copying and pasting?

How do i consolidate 13 spreadsheets onto one
 

Martha,

With a macro. Copy the code below into a module in a blank workbook, then
run it and when prompted select the workbook with the 13 sheets (Start with
that workbook closed) that you want to combine onto one sheet.

Assumes: table starts in A1, and is contiguous, headers are in row 1 of each
sheet.

HTH,
Bernie
MS Excel MVP

Sub Consolidate()
With Application
..DisplayAlerts = False
..EnableEvents = False
..ScreenUpdating = False
End With

Set Basebook = ThisWorkbook

Set myBook = Workbooks.Open(Application.GetOpenFilename)
For Each mySheet In myBook.Worksheets
mySheet.Activate
If Basebook.Worksheets(1).Range("A" & Rows.Count).End(xlUp).Row = 1 Then
Range("A1").CurrentRegion.Copy _
Basebook.Worksheets(1).Range("A65536").End(xlUp).O ffset(1, 0)
Else
Intersect(Range("2:" & Rows.Count),Range("A1").CurrentRegion).Copy _
Basebook.Worksheets(1).Range("A65536").End(xlUp).O ffset(1, 0)
End If
Next mySheet
myBook.Close

With Application
..DisplayAlerts = True
..EnableEvents = True
..ScreenUpdating = True
End With

Basebook.SaveAs Application.GetSaveAsFilename

End Sub

"Martha" wrote in message
...
I have 13 spreadsheets on one workbook and i would like to have all of them
on one spreadsheet. How do i do that w/o copying and pasting?

How do i consolidate 13 spreadsheets onto one
 

Data Consolidation? Its an Excel feature

Tyro

"Martha" wrote in message
...
I have 13 spreadsheets on one workbook and i would like to have all of them
on one spreadsheet. How do i do that w/o copying and pasting?

How do i consolidate 13 spreadsheets onto one
 

Yes, but what function do you choose? I have 19 worksheets, 16 of them
contain text (well some have numbers, but I don't want the counted or added
or averaged, ect). I just want the data that is entered in columns A - D of
these 16 tables to be consolidated to a single worksheet in the same workbook.

Also, I would like it when I edit one of the 16 worksheets the "master"
sheet will also be updated. on each of the 16 worksheets I have highlighted
the columns and rows (as these are constant) where the data is and have named
the range. Therefore worksheet labled "ROW01" has a range named "ROW01" that
includes all the data I want from that sheet.

I have tried all the macro codes that I have found on this site, but do not
know enough to be able to edit the code to fit my specific situation. I
would love to just use the Tools / Data Consolidation feature but have tried
each of the different function types and none return the results I am seeking.

thanks!

"Tyro" wrote:

Data Consolidation? Its an Excel feature

Tyro

"Martha" wrote in message
...
I have 13 spreadsheets on one workbook and i would like to have all of them
on one spreadsheet. How do i do that w/o copying and pasting?

How do i consolidate 13 spreadsheets onto one
 

Hi Ember

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

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Ember" wrote in message ...
Yes, but what function do you choose? I have 19 worksheets, 16 of them
contain text (well some have numbers, but I don't want the counted or added
or averaged, ect). I just want the data that is entered in columns A - D of
these 16 tables to be consolidated to a single worksheet in the same workbook.

Also, I would like it when I edit one of the 16 worksheets the "master"
sheet will also be updated. on each of the 16 worksheets I have highlighted
the columns and rows (as these are constant) where the data is and have named
the range. Therefore worksheet labled "ROW01" has a range named "ROW01" that
includes all the data I want from that sheet.

I have tried all the macro codes that I have found on this site, but do not
know enough to be able to edit the code to fit my specific situation. I
would love to just use the Tools / Data Consolidation feature but have tried
each of the different function types and none return the results I am seeking.

thanks!

"Tyro" wrote:

Data Consolidation? Its an Excel feature

Tyro

"Martha" wrote in message
...
I have 13 spreadsheets on one workbook and i would like to have all of them
on one spreadsheet. How do i do that w/o copying and pasting?

How do i consolidate 13 spreadsheets onto one
 

Ron - thank you for your reply. I spent several hours reviewing the
different links on your website that were referenced in several posts about
this issue. However, I was not able to make any of your codes work for my
needs.

I will just stick to the old fashoned copy and paste method.

Thanks

"Ron de Bruin" wrote:

Hi Ember

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

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Ember" wrote in message ...
Yes, but what function do you choose? I have 19 worksheets, 16 of them
contain text (well some have numbers, but I don't want the counted or added
or averaged, ect). I just want the data that is entered in columns A - D of
these 16 tables to be consolidated to a single worksheet in the same workbook.

Also, I would like it when I edit one of the 16 worksheets the "master"
sheet will also be updated. on each of the 16 worksheets I have highlighted
the columns and rows (as these are constant) where the data is and have named
the range. Therefore worksheet labled "ROW01" has a range named "ROW01" that
includes all the data I want from that sheet.

I have tried all the macro codes that I have found on this site, but do not
know enough to be able to edit the code to fit my specific situation. I
would love to just use the Tools / Data Consolidation feature but have tried
each of the different function types and none return the results I am seeking.

thanks!

"Tyro" wrote:

Data Consolidation? Its an Excel feature

Tyro

"Martha" wrote in message
...
I have 13 spreadsheets on one workbook and i would like to have all of them
on one spreadsheet. How do i do that w/o copying and pasting?