List Management Question- Combining into one

  • Thread starter Thread starter Daphtg
  • Start date Start date
D

Daphtg

I have 9 workbooks each containing 7 worksheets which I need to combine into
one data file to standardize addresses through the Post Office. I am using a
MAc and 2004 Excel. I have no access to "ACCESS". Is there a simple way of
doing this?

Thank you in advance for any help!!
 
Don't know about mac but easy enough to do with a looping macro to
open each worbook
for each worksheet
copy the data to the destination workbook at the next available row
next worksheet
close the source file.
next workbook
 
Since it sounds like a task you will only do once, you may not want to put it
in a macro unless the data is set up in a very consistant way. In the
following code the macros opens two workbooks and then move through all the
sheets starting with the 3rd one and copys specific data into the target
file. You probably don't need anything as fancy as this but it might give
you some ideas:

Sub Rationale()
Dim myRow As Long, sh As Integer, wb As Integer, I As Integer, Path As
String, File(2) As String
Path = ActiveWorkbook.Path
File(1) = "Cap Broadcast10.XLS"
File(2) = "Cap Tech10.xls"
ChDir Path
Application.DisplayAlerts = False
Range("A5", Range("A5").SpecialCells(xlCellTypeLastCell)).Clear
With Current
myRow = 5
For wb = 1 To 2
Workbooks.Open Filename:=File(wb)
sh = 3
Do
Sheets(sh).Activate
If [B4] <> "" Then
.Cells(myRow, 1) = [B3] & "-" & [C3] & "-" & [D3] &
"-" & [E3] & "-" & [F3] & "-" & [G3]
.Cells(myRow, 2) = [B4] & " - " & Format([I60],
"$#,##0")
.Cells(myRow, 2).Font.Bold = True
.Cells(myRow + 1, 1) = .Cells(myRow, 1).Value
.Cells(myRow + 1, 1).NumberFormat = ";;;"
Range("A10:J20").UnMerge
Range("A10").Copy
.Cells(myRow + 1, 2).PasteSpecial xlPasteValues
.Cells(myRow + 1, 2).WrapText = True
myRow = myRow + 2
End If
sh = sh + 1
Loop Until sh > Sheets.Count
Windows(File(wb)).Close Savechanges:=False
Next wb
End With
End Sub
 
Back
Top