Alternative:

Probably fastest way is to use ADO to read the workbook's sheets
and dump the data into 1 new workbook or mdb. Mdb is probably
fastest solution (if you're going to use Pivots anyway :)

You could use linked tables in access.. but ...


Just to give you an idea of HOW fast ADO can be..
I copied this little thingy which consolidates 1000 files
into 1 database in no time...

guess how long? .. now run the routine and be amazed


Create new workbook.. put some dummy data in, and save it as
data0000. in a new directory.

edit the path constant.. to point to the directory :)
Then run the routine which creates 1000 copies for testing.
Then run the tstPump to see how fast it consolidates things...

Option Explicit
'Code needs a reference to Microsoft ActiveX Data Objects 2.5 or higher)

Const PATH = "d:\phone"

Sub CreateTestXLS()
Dim i
ChDrive PATH
ChDir PATH
For i = 1 To 1000
FileCopy "data 0000.xls", "customer " & Format(i, "0000") & ".xls"
Next
End Sub

Sub CreateNewMDB(FileName As String)
Dim ocat As Object
Set ocat = CreateObject("ADOX.Catalog")
ocat.Create "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Jet OLEDB:Engine Type=5;Data Source=" & FileName
End Sub

Sub tstPump()
Dim t!, i%
t = Timer
If Dir(PATH & "\data.mdb") = "" Then CreateNewMDB PATH & "\data.mdb"
MDBDropDump
For i = 1 To 1000
XLS2MDB PATH & "\customer " & Format(i, "0000") & ".xls"
Next
MsgBox "done in " & Format(Timer - t, "0") & " seconds."

End Sub

Sub MDBDropDump()
Dim cnn As ADODB.Connection
Const cnnString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" &
PATH
& "\data.mdb" & ";"

Set cnn = New Connection
cnn.Open cnnString
cnn.CursorLocation = adUseClient
On Error Resume Next
cnn.Execute ("DROP TABLE DUMP")
cnn.Close

End Sub


Sub XLS2MDB(sXlFile$)
Dim cnn As ADODB.Connection

Const cnnString = "Provider=Microsoft.Jet.OLEDB.4.0;Extended
Properties=""Excel 8.0;Header:YES"";Data Source=&FFFF;"

Set cnn = New Connection
cnn.Open (Replace(cnnString, "&FFFF", sXlFile))
cnn.CursorLocation = adUseClient
On Error GoTo errH
cnn.Execute "INSERT INTO dump IN 'd:\phone\data.mdb' select '" & _
Left(sXlFile, Len(sXlFile) - 4) & "' as Client , * from `sheet1$`"

endH:
cnn.Close
Exit Sub

errH:
If Err.Number = -2147217865 Then
'This creates the table..
cnn.Execute "select '" & Left(sXlFile, Len(sXlFile) - 4) & _
"' as Client , * INTO dump IN 'd:\phone\data.mdb' from `sheet1$`"
else
msgbox err.number & vbnewline & err.description
End If
GoTo endH

End Sub



--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Steve wrote :

Hi,

I am using Excel 2000. I have a workbook with about 30 worksheets.
Each worksheet has an identical layout containing 14 sections every
45 rows with a number of empty rows and cells. I am using coumns A
through S. The columns I want to use are in column A and B, and G
through S. It is model forecasting volume, revenue, expense (months
in G:S and year in S).

Right now these I have linked these 30 worksheets into a single
worksheet in a separate workbook, the purpose for which is to
organize my data in tabular form so that I can create a pivot table.

Updating the links in 15,000 rows and 19 columns is much too slow in
Excel 2000. I am wondering if there is a macro available I could use
to copy and paste value the data i need.

I could record a macro to do this, but I am wondering if there is a
better way.

For example, if I recorded the macro, I would tell it to in worksheet
1, copy A6:B6 and paste value in new workbook sheet, copy and paste
G6:S6A6:B6, copy paste A12:B18, copy paste E12:S18, copy paste
A20:B33, copy paste G20:S33, and so on, until all my data is repasted
into a tabular format in the new workbook.

Thanks very much.

I've read
http://j-walk.com/ss/excel/tips/tip82.htm

and

http://www.google.com/groups?hl=en&l...1La.4495%24cJ5.
436%40www.newsranger.com&rnum=6

but I am not sure how to make these work for my situation.