Assuming the card number is in column A and the summary in Column B on
each sheet you might want to try something like:

Sub mvData()
Dim wks As Worksheet
Dim newSht As Worksheet
Dim eRow As Long
Dim c As Long
Dim r As Long
Dim sht As Long
Dim fndCell As Range
Dim card As String
Dim calcMode As Long

On Error GoTo Error_Handler
calcMode = Application.Calculation
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

r = 2
Set newSht = Sheets.Add
With newSht
.Move befo=Sheets(1)
.Range("A1").Value = "Card #"
End With
For Each wks In Worksheets
If wks.Name < newSht.Name Then
With wks
newSht.Cells(1, .Index) = .Name
eRow = .Cells(Rows.Count, 1).End(xlUp).Row
For c = 2 To eRow
card = .Cells(c, 1).Value
Set fndCell = newSht.Columns(1).Find(what:=card _
, LookIn:=xlValues, lookat:=xlWhole)
If Not fndCell Is Nothing Then
fndCell.Offset(0, .Index - 1).Value = _
.Cells(c, 2).Value
Else
newSht.Cells(r, 1).Value = card
newSht.Cells(r, .Index).Value = _
.Cells(c, 2).Value
r = r + 1
End If
Set fndCell = Nothing
Next c
End With
End If
Next wks

Error_Handler:
Application.ScreenUpdating = True
Application.Calculation = calcMode
End Sub


Hope this helps
Rowan

whygh wrote:
Hello,

I have a workbook which has 30 worksheets. the card No# in every sheet
is not completely same. such as ,

Sheet1:

Card NO# Summary

A001 20
A002 30

sheet2:
Card NO# Summary

A001 10
B001 20
...
sheet30:

Card No# Summary
A001 10
A002 20
A003 30
B001 10

....



Now I need add a new sheet in this workbook, convert ervery sheet as
column , c opy and paste all the sheet together.

the structure like the following:

Card NO# Sheet1 Sheet2 sheet3....
A001 20 10 ...
A002 30 .....
B001 20 ....
..
I use the two loops to implement it. it run so slowly.

Do you guys know how to write a formula for this function?

Thanks