Excel VBA macro to copy rows from multiple sheets upon entry

[CharlieBMF]

Hello,

I will be honest, I am completely new at this, I have been trying to write or find a code I can alter to my needs. I need to copy a row based upon a validation in column K on any sheet in a workbook. the validation will just be the word complete. I have multiple sheets with different names, I also need this code to copy rows as long as they are entered in any sheet. Please help, the code below has so many things wrong with it and I know, I was just trying my best. I am definitely taking a class after this.



Sub Copy_MS_2()
Application.ScreenUpdating = False
Dim ws As Worksheet, desWS As Worksheet
Set desWS = Sheets("Mastersheet")
desWS.UsedRange.ClearContents
For Each ws In Sheets
If ws.Name <> "Mastersheet" Then
With ws.Cells(1, 1).CurrentRegion
Set xRg = desWS.UsedRange.AutoFill
On Error Resume Next
Application.ScreenUpdating = False
For C = 1 To xRg.Count
If CStr(xRg(C).Value) Then "" .
xRg(C).EntireRow.Copy
ws.AutoFilter.Range.Offset(1, 0).Copy desWS.Cells(desWS.Rows.Count, "A").End(xlUp).Offset(1, 0)
.AutoFilter 15, ""
End With
End If
Next ws.
Application.ScreenUpdating = True
End Sub





Kindly,



CB

 

[Anbuselvam]

Note that this code assumes that the first row in each sheet contains headers and skips over it during the loop

Sub CopyRows()
    Dim ws As Worksheet
    Dim desWS As Worksheet
    Dim lastRow As Long
    Set desWS = ThisWorkbook.Sheets("Mastersheet")
    desWS.UsedRange.ClearContents
    For Each ws In ThisWorkbook.Worksheets
        If ws.Name <> "Mastersheet" Then
            lastRow = ws.Cells(ws.Rows.Count, "K").End(xlUp).Row
            For i = 2 To lastRow 'assuming first row contains headers
                If ws.Cells(i, "K").Value = "complete" Then
                    ws.Rows(i).Copy
                    desWS.Cells(desWS.Rows.Count, "A").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
                End If
            Next i
        End If
    Next ws
End Sub