Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
HOw can I use Copy and Paste Function
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
HOw can I use Copy and Paste Function
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 ------------------------------------------------------------------------ whygh's Profile: http://www.excelforum.com/member.php...o&userid=29679 View this thread: http://www.excelforum.com/showthread...hreadid=493977 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
HOw can I use Copy and Paste Function
Thank you very much. I have already use this sub. And I know how to use "OFFSET" function. Very Appreciate it. -- whygh ------------------------------------------------------------------------ whygh's Profile: http://www.excelforum.com/member.php...o&userid=29679 View this thread: http://www.excelforum.com/showthread...hreadid=493977 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
HOw can I use Copy and Paste Function
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy/paste Function | Excel Discussion (Misc queries) | |||
copy and paste function | Excel Worksheet Functions | |||
Copy and Paste function | Excel Worksheet Functions | |||
cut,copy paste function | Excel Discussion (Misc queries) | |||
Copy and Paste Function | Excel Programming |