I need help on how can i paste a result on Column “G” Row “2” after searching an entire Column A for the word “Country Code: ” . Below is my Code for the process.
Currently My Code is able to copy the specific word and paste it .
But it only paste on Column A which overlap the existing data.
Sub SearchForString() Dim LSearchRow As Integer Dim LCopyToRow As Integer Dim LCopytoColumn As String 'On Error GoTo Err_Execute 'Start search in row 1 LSearchRow = 1 'Start copying data to column G in Database LCopytoColumn = 7 'Start copying data to row 2 in Database and Loop results LCopyToRow = 2 While Len(Range("A" & CStr(LSearchRow)).Value) > 0 'If value in column E = "Mail Box", copy entire row to Sheet2 If InStr(1, Range("A" & CStr(LSearchRow)).Value, "Country Code:") > 0 Then 'Select row in Sheet1 to copy Rows(CStr(LSearchRow) & ":" & CStr(LSearchRow)).Select Selection.Copy 'Paste row into G2 in next row Range("G2").Select 'Columns(CStr(LCopytoColumn) & ":" & CStr(LCopytoColumn)).Select Rows(CStr(LCopyToRow) & ":" & CStr(LCopyToRow)).Select ActiveSheet.Paste 'Move counter to next row LCopyToRow = LCopyToRow 1 'Go back to Database to continue searching Sheets("Database").Select End If LSearchRow = LSearchRow 1 Wend 'Position on cell G2 Application.CutCopyMode = False Range("G2").Select MsgBox "All matching data has been copied." Exit Sub 'Err_Execute: ' MsgBox "An error occurred." End Sub