Append Spreadsheets

[Mike G.]

Dear Gurus and Experts:

Could you please tell me how to take 3 identical
spreadsheets (FLOR.xls, HOUS.xls, ATLA.xls) that have
different data and combine them together into 1
spreadsheet. In other words, I would like append the
contents of each the populated rows from the spreadsheets
into a master (SOUTH.xls)

Old spreadsheets are:
HOUS.xls
A B C D E F
1 H1 12 43 66 76 87
2 H3 87 12 34 43 54
3 H2 99 21 33 45 65

FLOR.xls
A B C D E F
1 F6 15 55 43 66 76
2 F4 22 47 12 34 43
3 F3 88 11 21 33 45

ATLA.xls
A B C D E F
1 A3 65 15 55 43 66
2 A1 28 22 47 12 34
3 A7 70 88 11 21 33

***********************************************
New spreadsheet to look like this:
SOUTH.xls
A B C D E F
1 H1 12 43 66 76 87
2 H3 87 12 34 43 54
3 H2 99 21 33 45 65
4 F6 15 55 43 66 76
5 F4 22 47 12 34 43
6 F3 88 11 21 33 45
7 A3 65 15 55 43 66
8 A1 28 22 47 12 34
9 A7 70 88 11 21 33

Thanks for your help and guidance.

Best regards, Mike
mailto:[email protected]

 

[michdenis]

Hi Mike

Try this,

'-----------------------------
Sub Sommaire()

Dim Arr(), Elt As Variant
Dim Rg As Range, Sh As Worksheet

Arr = Array("FLOR.xls", "HOUS.xls", "ATLA.xls")

Application.ScreenUpdating = False
On Error Resume Next
Application.DisplayAlerts = False
Application.Worksheets("SOUTH.xls").Delete
Application.DisplayAlerts = True

Set Sh = Worksheets.Add(After:=Sheets(Sheets.Count))
Sh.Name = "SOUTH.xls"

A = 1
For Each Elt In Arr
Set Rg = Worksheets(Elt).Range("A1").CurrentRegion
Rg.Copy Sh.Range("A" & A)
A = A + Rg.Rows.Count
Next
Set Rg = Nothing: Set Sh = Nothing

End Sub
'-----------------------------


Salutations!



"Mike G." <[email protected]> a écrit dans le message de Dear Gurus and Experts:

Could you please tell me how to take 3 identical
spreadsheets (FLOR.xls, HOUS.xls, ATLA.xls) that have
different data and combine them together into 1
spreadsheet. In other words, I would like append the
contents of each the populated rows from the spreadsheets
into a master (SOUTH.xls)

Old spreadsheets are:
HOUS.xls
A B C D E F
1 H1 12 43 66 76 87
2 H3 87 12 34 43 54
3 H2 99 21 33 45 65

FLOR.xls
A B C D E F
1 F6 15 55 43 66 76
2 F4 22 47 12 34 43
3 F3 88 11 21 33 45

ATLA.xls
A B C D E F
1 A3 65 15 55 43 66
2 A1 28 22 47 12 34
3 A7 70 88 11 21 33

***********************************************
New spreadsheet to look like this:
SOUTH.xls
A B C D E F
1 H1 12 43 66 76 87
2 H3 87 12 34 43 54
3 H2 99 21 33 45 65
4 F6 15 55 43 66 76
5 F4 22 47 12 34 43
6 F3 88 11 21 33 45
7 A3 65 15 55 43 66
8 A1 28 22 47 12 34
9 A7 70 88 11 21 33

Thanks for your help and guidance.

Best regards, Mike
mailto:[email protected]