Try this :
Sub Transformdata()
Dim rngCEll As Range
Dim rngRange As Range
Dim wksOutput As Worksheet
Set wksOutput = ThisWorkbook.Worksheets.Add
Set rngRange = ThisWorkbook.Worksheets("Query").Rows(1)
For Each rngCEll In rngRange.Cells
Application.ScreenUpdating = False
If rngCEll.Value = "" Then Exit For
If rngCEll.Value = "Name" Then
rngCEll.Resize(rngCEll.End(xlDown).Row, 2).Copy
wksOutput.Range("A" & WorksheetFunction.CountA(wksOutput.Range("A:A")) + 1)
End If
Next
With wksOutput
.UsedRange.WrapText = False
.Rows.AutoFit
.Columns.AutoFit
.Range("$A$1:$B$114").AutoFilter Field:=1, Criteria1:="Name"
.Cells.SpecialCells(xlCellTypeVisible).EntireRow.Delete
.Range("A1").EntireRow.Insert
.Range("A1").Value = "Name"
.Range("B1").Value = "No"
End With
Application.ScreenUpdating = False
End Sub
-----Original Message-----
From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com]
On Behalf Of Mukesh Kumar
Sent: Jan/Tue/2012 01:10
To: excel-macros@googlegroups.com
Subject: $$Excel-Macros$$ Query continuous copy paste
Dear Sir,
Please tell me the solution for the query given in the attached file.
In it the data is distributed in the different columns. The solution is
manually copy paste the data which is time consuming process.
Please tell me the solution or formula to automate the process. Also please
note that here two columns are having label i.e. Name & No.
There may be certain time the columns may having more than 2 or more labels.
So please keep this situation too in mind.
Thanks & Regards,
Mukesh Kumar
--
FORUM RULES (986+ members already BANNED for violation)
1) Use concise, accurate thread titles. Poor thread titles, like Please
Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will
not get quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security
measure.
4) Acknowledge the responses you receive, good or bad.
5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited.
NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.
----------------------------------------------------------------------------
--------------------------
To post to this group, send email to excel-macros@googlegroups.com
--
FORUM RULES (986+ members already BANNED for violation)
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited.
NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.
------------------------------------------------------------------------------------------------------
To post to this group, send email to excel-macros@googlegroups.com
