Loop Thru Columns and copy/paste until blank - please help

[ExcelTrick]

Hello,

I am tying to create a macro/VBA code that would loop thru a column, find value, then copy everything below until the next blank cell and paste to a different sheet and start with the next column to do the same thing until next column is empty.

something like this:

Loop thru Col A
Find "123*"
Copy All below A1 until blank
Create Sheet "Destination"
Paste in Col E next blank Cell col E

Next CoL B and so on Until next Col is blank

Thank you in advance for all your help

 

[ravishankar]

try these codes

Sub trick()
Dim LR As Long, LC As Long, a As Long, d As Long
Dim b As String
LC = Cells(1, Columns.Count).End(xlToLeft).Column
c = InputBox("enter search term")
    For a = 1 To LC
    b = Chr(a + 64)
    LR = Cells(Rows.Count, b).End(xlUp).Row
    d = WorksheetFunction.Match(c, Range(b & "1:" & b & LR), 0)
    Range(b & d & ":" & b & LR).Copy
    Sheets("sheet2").Range(b & "1").PasteSpecial
    Next a
MsgBox "complete"
End Sub

ravishankar

 

[ExcelTrick]

Thank you for your reply Ravishakar. Do you know why would I get an error massage "Unable to get the Match property of the WorksheetFunction class"? Is there anyway to fix this? I've tried different search terms that I no that are in he column but I still get this error