Copy rows from one set of sheets to another set of sheets, based on a condition

happyhourandtacos 

Sub CopyTasksToRegionSheets()
    Dim focusAreas As Variant
    Dim regions As Variant
    Dim ws As Worksheet
    Dim regionSheet As Worksheet
    Dim lastRow As Long
    Dim targetRow As Long
    Dim region As String
    Dim i As Long
    Dim focusArea As Variant
    
    ' Define the focus area sheets and region sheets
    focusAreas = Array("IT", "HR", "FINANCE", "COMMS", "SALES")
    regions = Array("SOUTH", "NORTH", "WEST", "EAST", "CENTRAL")
    
    ' Clear the region sheets before copying new data
    For Each region In regions
        Set regionSheet = ThisWorkbook.Sheets(region)
        ' Assuming you want to clear all existing data except the header row
        regionSheet.Rows("2:" & regionSheet.Rows.Count).ClearContents
    Next region
    
    ' Loop through each focus area sheet
    For Each focusArea In focusAreas
        Set ws = ThisWorkbook.Sheets(focusArea)
        lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
        
        ' Loop through each row in the focus area sheet
        For i = 2 To lastRow ' Assuming the first row is the header
            region = ws.Cells(i, "B").Value ' Assuming region is in column B
            
            ' Copy the row to the corresponding region sheet
            Set regionSheet = ThisWorkbook.Sheets(region)
            targetRow = regionSheet.Cells(regionSheet.Rows.Count, "A").End(xlUp).Row + 1
            ws.Rows(i).Copy Destination:=regionSheet.Rows(targetRow)
        Next i
    Next focusArea
    
    MsgBox "Tasks have been successfully copied to their respective region sheets!"
End Sub

The error "For Each control variable must be Variant or Object" indicates that the control variable in your For Each loop must be declared as a Variant or Object. In the provided code, the control variable focusArea needs to be declared correctly.

In this code, I've declared focusArea as a Variant to avoid the compile error.

Hope this helps you.