lost macro to move rows to another sheet based on text string

[larrissag]

Hello! This forum has been sooo helpful, I was able to find and tweak code to move rows from Sheet1 to Sheet2, A7, based on certain text contained in Column A...but I lost the macro somehow, I didn't save it properly or it was imbedded in a file I didn't save, something. Nonetheless, I'm back to square 1! I have SCOURED the forums for 2 whole days looking for the original code but I can't find it. There are similar posts but nothing that quite works like the original code from member Norie. I've seen many many posts on this topic while searching, but everyone has a slightly different request, I've tried to modify to no avail. If Column A says

apple 1234
apple 1234
pear 5678
grape 9123
apple 4567

If Column A contains "1234" (part of the text) then move whole row to Sheet2, starting at A7. I plan to repeat the code to move row containing text "5678" to sheet3, starting at A7, move text "9123" to sheet4 starting at A7, etc, etc. (the sheets are already formatted with headers and such, that's why the A7).

Can anyone bail me out, I'm dying here....

 

[johnnyL]

@larrissag The following should get you started:

Sub MoveRowsFromOneSheetToAnotherDependentUponCellData()
    Dim SourceSheet         As Worksheet
    Dim DestinationSheet1   As Worksheet
'
    Set SourceSheet = Sheets("Sheet1")          ' <-- Set Sheet Name
    Set DestinationSheet1 = Sheets("Sheet2")    ' <-- Set Sheet Name
'
    SourceSheet.UsedRange.AutoFilter 1, "*1234*"     ' the '1,' indicates the 'A' Column
    SourceSheet.UsedRange.Offset(0).Copy
    DestinationSheet1.Cells(Rows.Count, 1).End(xlUp)(7).PasteSpecial xlPasteValues      '  The ', 1' indicates the 'A' column, the '(7)' indicates the row
'
    Application.CutCopyMode = False
    SourceSheet.AutoFilterMode = False
End Sub

 

[larrissag]

Awesome, thank you JohnnyL. I've been cherry picking from other code, but can't get it right, I'll try this!

 

[vcoolio]

Hello Larrissa,

How many destination worksheets do you have and how are they named?

Cheerio,
vcoolio.

 

[larrissag]

Hello Larrissa,

How many destination worksheets do you have and how are they named?

Cheerio,
vcoolio.

Voolio, I have 37 destination sheets, but their names are unique (bank account name & number). I'll have no choice but to copy/paste the cose for each, correct?

 

[johnnyL]

@larrissag With 37 sheets, the following code is probably better for you:

'
Public SearchString         As String
'
Public DestinationSheet     As Worksheet
Public SourceSheet          As Worksheet
'
'

Sub MoveRowsFromOneSheetToAnotherDependentUponCellDataV2()
'
    Set SourceSheet = Sheets(1)                                     ' <-- Set Sheet Index Number OR Sheet Name inside the parentheses
'
'
    Set DestinationSheet = Sheets(2)                                ' <-- Set Sheet Index Number OR Sheet Name inside the parentheses
    SearchString = "*1234*"                                         ' <-- Edit the values here to what you are looking for
    Call SearchingSub
'
    Set DestinationSheet = Sheets(3)                                ' <-- Set Sheet Index Number OR Sheet Name inside the parentheses
    SearchString = "*5678*"                                         ' <-- Edit the values here to what you are looking for
    Call SearchingSub
'
    Set DestinationSheet = Sheets(4)                                ' <-- Set Sheet Index Number OR Sheet Name inside the parentheses
    SearchString = "*9123*"                                         ' <-- Edit the values here to what you are looking for
    Call SearchingSub
'
    Set DestinationSheet = Sheets(5)                                ' <-- Set Sheet Index Number OR Sheet Name inside the parentheses
    SearchString = "*4567*"                                         ' <-- Edit the values here to what you are looking for
    Call SearchingSub
'
'   ETC ...
'
End Sub

Sub SearchingSub()
'
    Dim LastRowInColumn             As Long
    Dim NextRowInDestinationSheet   As Long
    Dim RowCount                    As Long
'
    NextRowInDestinationSheet = 7                                   ' Starting row to copy matching rows to in the Destination sheet
'
    With SourceSheet
        LastRowInColumn = .Range("A" & Rows.Count).End(xlUp).Row    ' Find last used row in the search column
'
        For RowCount = 1 To LastRowInColumn                                                     ' Loop to check each cell in the search column
            If .Range("A" & RowCount) Like SearchString Then                                    '   check cell to see if it contains text we are looking for
                .Rows(RowCount).Copy DestinationSheet.Range("A" & NextRowInDestinationSheet)   '       If it does, copy the entire row to the Destination sheet
                NextRowInDestinationSheet = NextRowInDestinationSheet + 1                       '       Also Increment the next row to be copied to
            End If                                                                              '   End check of this cell
        Next RowCount                                                                           ' Loop back until all used cells in search column are checked
    End With
End Sub

That will get you started and you can just copy/paste more to handle all of your sheets that you may need. Just copy/paste and edit the sheet name/index number and the search string.

 

[larrissag]

@larrissag With 37 sheets, the following code is probably better for you:

'
Public SearchString         As String
'
Public DestinationSheet     As Worksheet
Public SourceSheet          As Worksheet
'
'

Sub MoveRowsFromOneSheetToAnotherDependentUponCellDataV2()
'
    Set SourceSheet = Sheets(1)                                     ' <-- Set Sheet Index Number OR Sheet Name inside the parentheses
'
'
    Set DestinationSheet = Sheets(2)                                ' <-- Set Sheet Index Number OR Sheet Name inside the parentheses
    SearchString = "*1234*"                                         ' <-- Edit the values here to what you are looking for
    Call SearchingSub
'
    Set DestinationSheet = Sheets(3)                                ' <-- Set Sheet Index Number OR Sheet Name inside the parentheses
    SearchString = "*5678*"                                         ' <-- Edit the values here to what you are looking for
    Call SearchingSub
'
    Set DestinationSheet = Sheets(4)                                ' <-- Set Sheet Index Number OR Sheet Name inside the parentheses
    SearchString = "*9123*"                                         ' <-- Edit the values here to what you are looking for
    Call SearchingSub
'
    Set DestinationSheet = Sheets(5)                                ' <-- Set Sheet Index Number OR Sheet Name inside the parentheses
    SearchString = "*4567*"                                         ' <-- Edit the values here to what you are looking for
    Call SearchingSub
'
'   ETC ...
End Sub

Sub SearchingSub()
'
    Dim LastRowInColumn             As Long
    Dim NextRowInDestinationSheet   As Long
    Dim RowCount                    As Long
'
    NextRowInDestinationSheet = 7                                   ' Starting row to copy matching rows to in the Destination sheet
'
    With SourceSheet
        LastRowInColumn = .Range("A" & Rows.Count).End(xlUp).Row    ' Find last used row in the search column
'
        For RowCount = 1 To LastRowInColumn                                                     ' Loop to check each cell in the search column
            If .Range("A" & RowCount) Like SearchString Then                                    '   check cell to see if it contains text we are looking for
                .Rows(RowCount).Copy DestinationSheet.Range("A" & NextRowInDestinationSheet)   '       If it does, copy the entire row to the Destination sheet
                NextRowInDestinationSheet = NextRowInDestinationSheet + 1                       '       Also Increment the next row to be copied to
            End If                                                                              '   End check of this cell
        Next RowCount                                                                           ' Loop back until all used cells in search column are checked
    End With
End Sub

That will get you started and you can just copy/paste more to handle all of your sheets that you may need. Just copy/paste and edit the sheet name/index number and the search string.

 

[johnnyL]

@larrissag I don't see the purpose of your last post here. ??? Cat got your tongue? LOL

 

[larrissag]

@johnnyLI will try this in the morning as well, thank you! How does the xlup work? You guys don't know how much I appreciate it.

@larrissag I don't see the purpose of your last post here. ??? Cat got your tongue? LOL

Sorry lol close, the dogs jumped on me and got me distracted. What I was going to say was how does finding the last cell in a column work? I think it is called xlup or something? I was researching code to find last cell in column and repeat it in the same row two columns over. For example,find last row in column G (f it happens to be 50 lets say) and copy to column I, row 50.

After I move out the data to its own sheet, i have to repeat some text in two additional columns to match formatting requirements. Issue is, the last row varies from sheet to sheet. I probably shouldve asked this question in a separate thread, i apologize.

 

[johnnyL]

@larrissag

Members, please correct or add to anything I post here.

End(xlup).Row is an excel snippet of code to find the last used row in an Excel range. It is basically a loop search. It looks for constants/formulas in a cell.

It looks from the very bottom of the range upwards until it finds the first non 'blank' cell and records the row value.

This is the 'opposite' of xldown which looks from the top of the range downwards until it finds a 'blank' cell and records the row value of the previous cell.

The difference is there may be other cells in the range that contain 'blank' or 'non-blank' cells, the direction chosen just records the first instance that satisfies the direction chosen.


In other words:
xlup looks upwards for first 'non-blank' cell
xldown looks downwards for first 'blank' cell


As far as your other question, I am not a moderator here, but I would say that if it doesn't relate to the title of this thread, I would suggest a new thread with a different title. JMOP