Appending the rows of several tabs in xls sheets?

[Pat]

Is it possible to append all the rows of several tabs into one sheet?


We are getting inventories in excel files organized in several tabs:

1st Tab: instructions for the users
2nd Tab: information about the country (currency, tax rate, etc)

3rd Tab: inventory of one site within the country
....to... All tabs have the same format (5 rows header, then identical
Nth Tab: rows containing data about one item per row.

The name of each tab 3 to N is the site name (company code).
N is of course different for each country, and the number of lines in each
site inventory is also variable (but they are all at the top with no holes).

What I need to do is to append all those inventories into a single sheet.
I also need to prefix each row with the name of the site.

Is this a known problem with an existing solution (FAQ?)
If not, how can I know how many tabs there are in an XLS file, and their names?

Thanks much for any pointers

Pat.

 

[BrianB]

You should be able to adapt this to do what you want.

'------------------------------------------
'- Transfer data from several tables
'- in the same workbook to a master sheet.
'- Adds to bottom of existing master.
'- (Delete old records first if necessary)
'- Run macro from master sheet.
'- All tables must have same row 1 headings
'- Ignores other sheets
'- by BrianB
'------------------------------------------
Sub CONSOLIDATE()
Dim ToSheet As Worksheet
Dim NumColumns As Integer
Dim Field1 As String
Dim ToRow As Long
Dim FromSheet As Worksheet
Dim FromRow As Long
Dim LastRow As Long
Dim CopyRange As Range
'--------------------------
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
Set ToSheet = ActiveSheet
NumColumns = ToSheet.Range("A1").End(xlToRight).Column
Field1 = ToSheet.Range("A1").Value
ToRow = ToSheet.Range("A65536").End(xlUp).Row + 1
'- main loop
For Each ws In ActiveWorkbook.Worksheets
Application.StatusBar = "Processing : " & ws.Name
'- check for data table
If ws.Range("A1").Value = Field1 And ws.Name <> ToSheet.Nam
Then
'- transfer data
LastRow = ws.Range("A65536").End(xlUp).Row
ws.Activate
Set CopyRange = ws.Range(Cells(2, 1), Cells(LastRow
NumColumns))
CopyRange.Copy Destination:=ToSheet.Range("A" & ToRow)
ToRow = ToSheet.Range("A65536").End(xlUp).Row + 1
End If
Next
ToSheet.Activate
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
MsgBox ("Done.")
Application.StatusBar = False
End Sub
'==== EOP =====================================

 

[Otto Moehrbach]

Pat
If you have just "several" sheets and you have to do this just once,
copy and paste would be the easiest way to go.
However, if you have many sheets or you have to do this repeatedly, then
you need to automate it (write a macro).
Brian gave you a macro that you can modify to your particular situation.
If you need help with this, post back with specific questions about that
macro.
If you feel you need more help than that, send me a small file with a
sample of what you have and what you want to have and I'll set it up for
you. Remove "cobia97" from my email address. HTH Otto

 

[Pat]

Thanks so much, Brian!

After a little discovery of the basics, I was able to make your code work on my
machine. I am now near a solution thanks to your help.

One strange problem was that the reference to "Cells(x,y)" will absolutely not
work at all on my PC. I get "error 400".

Set CopyRange = ws.Range(Cells(2, 1), Cells(LastRow,NumColumns))

I finally replaced by Set CopyRange = ws.Range("A2","AZ1000") which works fine.
This rangeis much bigger than the real area but only the existing liines are
copied, so it does the job.

Some more adaptation and a solution will be within sight.

Thanks a lot again, this was a big help for me.

Pat.