HOw can I use Copy and Paste Function

[whygh]

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

[Rowan Drummond]

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 before:=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
>
>

[whygh]

Thank you very much. I have already use this sub. And I know how to use "OFFSET" function.

Very Appreciate it.

[Rowan Drummond]

You're welcome.

whygh wrote:
> Thank you very much. I have already use this sub. And I know how to use
> "OFFSET" function.
>
> Very Appreciate it.
>
>