Try this code...
Sub GetDataInNewSheet()
Dim sColor As String, rMyRng As Range, wBase As Worksheet, rTemp As Range, rPaste As Range
sColor = "BLACK"
Set wBase = ActiveSheet
Set rMyRng = Range("1:1")
Sheets.Add , wBase
Set rPaste = Range("A1")
Application.ScreenUpdating = False
Do
Set rMyRng = rMyRng.Find(sColor, rMyRng.Cells(1), , xlPart, xlByColumns, xlNext)
If Not rTemp Is Nothing Then If rMyRng.Address = rTemp.Address Then Exit Do
rMyRng.MergeArea.EntireColumn.Copy rPaste
Set rTemp = rMyRng
Set rPaste = Cells(1, Columns.Count).End(xlToLeft).Offset(, 1)
Set rMyRng = wBase.Range(rMyRng, wBase.Cells(1, Columns.Count))
Loop
Rows("1:1").RowHeight = 15
ActiveSheet.UsedRange.Columns.AutoFit
Application.ScreenUpdating = True
End Sub
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks