VBA - based on info in column X move entire row to sheet 2, or 3 or 4 ect

[Ferrito]

Hello, I've been trying to learn VBA to help automate some things at work, but i'm finding the layering of multiple variables difficult. I know how to move 1 line at a time from one tab to another but my current dilemma is how to look at the entire worksheet and move each row to different sheets/tabs.

I.e, I want to look at each row within sheet1 and move (cut-paste) to either sheet2, sheet3, sheet4 etc. based on the information in a specific column.

For example, say column E listed the species of pet someone had, if it says "cat" I'd want that entire row moved to the sheet named "feline", but if it said "dog" I'd want the entire row moved to the sheet named "canine" instead ect.

(for reference all sheets have 2 header rows, so data starts on row 3, and the columns of data are run from A to X)

any pointers in the right direction would be greatly appreciated, thank you

 

[saboh12617]

Hello,

There are multiples possible approaches, also without VBA. But to stick with the question, basically you need to loop on the rows of the column to consider (E in your example) then look at each value, and you could either use a correspondence table/dictionary or a Select/Case to adapt your code to any possibility. Let's say you use a dictionary With key = "cat" and Value = ThisWorkbook.Worksheets("feline")

Then, for each row, you could do a check and copy the row to the designated one like so

If myDict.Contains(currentRowValue) Then
    currentRow.Copy myDict(currentRowValue).Range("you have to calculate the destination here")
End If

But here we see you also need to make a small code to get where to paste the data (with a Range.End(xlDown) for example, or even simpler if you use Structured Tables/ListObjects), otherwise you will overwrite your previous copied row every time.

This is for the principle, then for optimization purposes you should do this not with full rows/columns but only the needed one, and instead of using Excel's cells and copy/paste, using vba tables and working as much as possible in memory.