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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message ...
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" wrote in message ...
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

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" wrote in message ...
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" wrote in message ...
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?