import multiple workbooks into 1 workbook

I'm setting up a webshop and using Excel to put in the products.
I've got seperate workbooks for my 20 product-groups (20 excel documents),
and each group is devided in brands (a min. of 5 brands per product group),
which are in worksheets.

I would like to make a file that import all the data from all the
worksheets, without me having to save each worksheet into a different text
file.
I've tried to copy the data and past it as a link, but when a row is added,
updating the link doesn't add the added row.
I've tried to import from text files, it does add added rows, but I've got
to save all my worksheets as text files.

Can anyone help me out?

Hi Anita
Could you clarify whether all your 20 worsheets are in a single workbook or
are they in different worbooks?
If they are in a single workbook then there is macro which i can send you
which will combine all worksheets into a single worksheet.

Regards
Vinod




"Anita" <[email protected]> wrote in message
news:[email protected]...
> I'm setting up a webshop and using Excel to put in the products.
> I've got seperate workbooks for my 20 product-groups (20 excel documents),
> and each group is devided in brands (a min. of 5 brands per product
group),
> which are in worksheets.
>
> I would like to make a file that import all the data from all the
> worksheets, without me having to save each worksheet into a different text
> file.
> I've tried to copy the data and past it as a link, but when a row is
added,
> updating the link doesn't add the added row.
> I've tried to import from text files, it does add added rows, but I've got
> to save all my worksheets as text files.
>
> Can anyone help me out?

Hi Jed,

I've got 20 seperate workbooks (eg group1.xls; group2.xls; group3.xls etc.)
Each of those workbook is devided into brands bij using worksheet.
It would be very helpfull to put all worksheets of 1 workbook together, that
would save me a lot of time. So please, tell me more!

"Jed" wrote:

> Hi Anita
> Could you clarify whether all your 20 worsheets are in a single workbook or
> are they in different worbooks?
> If they are in a single workbook then there is macro which i can send you
> which will combine all worksheets into a single worksheet.
>
> Regards
> Vinod
>
>
>
>
> "Anita" <[email protected]> wrote in message
> news:[email protected]...
> > I'm setting up a webshop and using Excel to put in the products.
> > I've got seperate workbooks for my 20 product-groups (20 excel documents),
> > and each group is devided in brands (a min. of 5 brands per product
> group),
> > which are in worksheets.
> >
> > I would like to make a file that import all the data from all the
> > worksheets, without me having to save each worksheet into a different text
> > file.
> > I've tried to copy the data and past it as a link, but when a row is
> added,
> > updating the link doesn't add the added row.
> > I've tried to import from text files, it does add added rows, but I've got
> > to save all my worksheets as text files.
> >
> > Can anyone help me out?
>
>
>

Hi Anita

Here is what you need to do to combine all worksheets in a single worksheet

With your workbook open, Insert a blank worksheet and rename worksheet as
MASTER.

Press <ALT & F11> key together to open Visual Basic.

From the Menu Select 'Insert' & then select ' Module'

In the blank Module , Copy and paste Following Macro.


Public Function LastRow(sh As Worksheet)
On Error Resume Next
LastRow = sh.Cells.Find(What:="*", _
After:=sh.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
On Error GoTo 0
End Function

Sub MergeSheets()
Dim sh As Worksheet
Dim last As Long
Dim rng As Range
Dim shLast As Long
Worksheets("Master").Cells.ClearContents
Worksheets("Master").Range("a1").Value = "All sheets"
For Each sh In ThisWorkbook.Worksheets
If UCase(sh.Name) <> "MASTER" Then
last = LastRow(Worksheets("Master"))
shLast = LastRow(sh)
Set rng = Worksheets("Master").Cells(last + 1, 1)
sh.Range(sh.Rows(1), sh.Rows(shLast)).Copy Destination:=rng
End If
Next
End Sub

Exit Visual Basic Editor.
Back to your Excel WorkSheet
After inserting the above macro, Press <ALT & F8> to list Macros
Select Macro 'MergeSheets' and run the Macro.
All worksheets would now have been copied into MASTER Work sheet.

Hope this Helps

Regards
Jed


"Anita" <[email protected]> wrote in message
news:[email protected]...
> Hi Jed,
>
> I've got 20 seperate workbooks (eg group1.xls; group2.xls; group3.xls
etc.)
> Each of those workbook is devided into brands bij using worksheet.
> It would be very helpfull to put all worksheets of 1 workbook together,
that
> would save me a lot of time. So please, tell me more!
>
> "Jed" wrote:
>
> > Hi Anita
> > Could you clarify whether all your 20 worsheets are in a single workbook
or
> > are they in different worbooks?
> > If they are in a single workbook then there is macro which i can send
you
> > which will combine all worksheets into a single worksheet.
> >
> > Regards
> > Vinod
> >
> >
> >
> >
> > "Anita" <[email protected]> wrote in message
> > news:[email protected]...
> > > I'm setting up a webshop and using Excel to put in the products.
> > > I've got seperate workbooks for my 20 product-groups (20 excel
documents),
> > > and each group is devided in brands (a min. of 5 brands per product
> > group),
> > > which are in worksheets.
> > >
> > > I would like to make a file that import all the data from all the
> > > worksheets, without me having to save each worksheet into a different
text
> > > file.
> > > I've tried to copy the data and past it as a link, but when a row is
> > added,
> > > updating the link doesn't add the added row.
> > > I've tried to import from text files, it does add added rows, but I've
got
> > > to save all my worksheets as text files.
> > >
> > > Can anyone help me out?
> >
> >
> >

Hi Jed,

Thanks, tried it, but it wants to Debug:

sh.Range(sh.Rows(1), sh.Rows(shLast)).Copy Destination:=rng

(got runtime error)

Any solution?

Hi Anita

The Master worksheet you added must be the last worksheet in the workbook.
Try moving Master worksheet to last worksheet and run the macro again. See
if that helps. I tried with Master worksheet as the first worksheet and gave
the same error as you described, but did not error out when inserted Master
worksheet as last worksheet.

Regards
Jed

"Anita" <[email protected]> wrote in message
news:[email protected]...
> Hi Jed,
>
> Thanks, tried it, but it wants to Debug:
>
> sh.Range(sh.Rows(1), sh.Rows(shLast)).Copy Destination:=rng
>
> (got runtime error)
>
> Any solution?

See
http://www.rondebruin.nl/copy3.htm

--
Regards Ron de Bruin
http://www.rondebruin.nl



"Anita" <[email protected]> wrote in message news:[email protected]...
> I'm setting up a webshop and using Excel to put in the products.
> I've got seperate workbooks for my 20 product-groups (20 excel documents),
> and each group is devided in brands (a min. of 5 brands per product group),
> which are in worksheets.
>
> I would like to make a file that import all the data from all the
> worksheets, without me having to save each worksheet into a different text
> file.
> I've tried to copy the data and past it as a link, but when a row is added,
> updating the link doesn't add the added row.
> I've tried to import from text files, it does add added rows, but I've got
> to save all my worksheets as text files.
>
> Can anyone help me out?

Hi Ron,

Tried to follow it Step by step, but went wrong at the first step. I think I
forgot to mention I'm using a Mac. Changed c:\data into 'Macintosh
HD:Users:myname:Desktop:test' but then he tripped over

ChDrive MyPath

Can you help me out?

"Ron de Bruin" wrote:

> See
> http://www.rondebruin.nl/copy3.htm
>
> --
> Regards Ron de Bruin
> http://www.rondebruin.nl
>
>
>
> "Anita" <[email protected]> wrote in message news:[email protected]...
> > I'm setting up a webshop and using Excel to put in the products.
> > I've got seperate workbooks for my 20 product-groups (20 excel documents),
> > and each group is devided in brands (a min. of 5 brands per product group),
> > which are in worksheets.
> >
> > I would like to make a file that import all the data from all the
> > worksheets, without me having to save each worksheet into a different text
> > file.
> > I've tried to copy the data and past it as a link, but when a row is added,
> > updating the link doesn't add the added row.
> > I've tried to import from text files, it does add added rows, but I've got
> > to save all my worksheets as text files.
> >
> > Can anyone help me out?
>
>
>

Hi Jed,

This didn't do the trick, any sugestions?

"Jed" wrote:

> Hi Anita
>
> The Master worksheet you added must be the last worksheet in the workbook.
> Try moving Master worksheet to last worksheet and run the macro again. See
> if that helps. I tried with Master worksheet as the first worksheet and gave
> the same error as you described, but did not error out when inserted Master
> worksheet as last worksheet.
>
> Regards
> Jed
>
> "Anita" <[email protected]> wrote in message
> news:[email protected]...
> > Hi Jed,
> >
> > Thanks, tried it, but it wants to Debug:
> >
> > sh.Range(sh.Rows(1), sh.Rows(shLast)).Copy Destination:=rng
> >
> > (got runtime error)
> >
> > Any solution?
>
>
>

Hi Anita

Post it in the Mac newsgroup
microsoft.public.excel.macintosh

I have never used a Mac


--
Regards Ron de Bruin
http://www.rondebruin.nl



"Anita" <[email protected]> wrote in message news:[email protected]...
> Hi Ron,
>
> Tried to follow it Step by step, but went wrong at the first step. I think I
> forgot to mention I'm using a Mac. Changed c:\data into 'Macintosh
> HD:Users:myname:Desktop:test' but then he tripped over
>
> ChDrive MyPath
>
> Can you help me out?
>
> "Ron de Bruin" wrote:
>
>> See
>> http://www.rondebruin.nl/copy3.htm
>>
>> --
>> Regards Ron de Bruin
>> http://www.rondebruin.nl
>>
>>
>>
>> "Anita" <[email protected]> wrote in message news:[email protected]...
>> > I'm setting up a webshop and using Excel to put in the products.
>> > I've got seperate workbooks for my 20 product-groups (20 excel documents),
>> > and each group is devided in brands (a min. of 5 brands per product group),
>> > which are in worksheets.
>> >
>> > I would like to make a file that import all the data from all the
>> > worksheets, without me having to save each worksheet into a different text
>> > file.
>> > I've tried to copy the data and past it as a link, but when a row is added,
>> > updating the link doesn't add the added row.
>> > I've tried to import from text files, it does add added rows, but I've got
>> > to save all my worksheets as text files.
>> >
>> > Can anyone help me out?
>>
>>
>>

Hi Jed

tried your code. works great. only thing is that it copies also all the first lines of the worksheet where the collumn labels area. any way not to do that?

thanks in advance

sinistral

Originally Posted by Jed

Hi Anita
Could you clarify whether all your 20 worsheets are in a single workbook or
are they in different worbooks?
If they are in a single workbook then there is macro which i can send you
which will combine all worksheets into a single worksheet.

Regards
Vinod




"Anita" <[email protected]> wrote in message
news:[email protected]...
> I'm setting up a webshop and using Excel to put in the products.
> I've got seperate workbooks for my 20 product-groups (20 excel documents),
> and each group is devided in brands (a min. of 5 brands per product
group),
> which are in worksheets.
>
> I would like to make a file that import all the data from all the
> worksheets, without me having to save each worksheet into a different text
> file.
> I've tried to copy the data and past it as a link, but when a row is
added,
> updating the link doesn't add the added row.
> I've tried to import from text files, it does add added rows, but I've got
> to save all my worksheets as text files.
>
> Can anyone help me out?