Copy rows to other sheets based on value in column

LeedMe Posts 6 Registration date Tuesday March 28, 2017 Status Member Last seen April 12, 2018 - Updated on Mar 28, 2017 at 07:47 AM
vcoolio Posts 1362 Registration date Thursday July 24, 2014 Status Moderator Last seen September 29, 2022 - Mar 30, 2017 at 08:59 AM
I have an Excel file with three sheets: Sheet1 contains all my data. Sheet2 named "MACH" and Sheet3 named "FAB"
I am looking for a way with a macro, to run upon opening the Excel document, and look in column "G" of Sheet1 for the values "MACH" and "FAB. If "MACH" is found in "G" then it copies the row into the sheet named "MACH" and if "FAB" is found in "G" then copy that row to the sheet named "FAB".
When the information is copied I would like it to be inserted into the next available row in the "MACH" and "FAB" sheets so there are no row spaces.
I have attempted the task by looking around forums but, I can only get it half way there.

First macro I have is:
Sub SortDepartments()
For Each Cell In Sheets(1).Range("G:G")
If Cell.Value = "MACH." Then
matchRow = Cell.Row
Rows(matchRow & ":" & matchRow).Select

End If
End Sub

Second macro removes the empty rows:
Private Sub Auto_Open(ByVal Target As Range)
If Intersect(Target, Columns("G:G")) Is Nothing Then Exit Sub
If Target.Value = "MACH." Then
Target.EntireRow.Copy Sheets("MACH").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)

End If
End Sub

6 replies

vcoolio Posts 1362 Registration date Thursday July 24, 2014 Status Moderator Last seen September 29, 2022 250
Mar 29, 2017 at 07:51 PM
Hello LeedMe,

Try the following code in a copy of your work book first:-

Sub TransferData()

        Dim ar As Variant
        Dim i As Integer
        Dim lr As Long
ar = Array("MACH", "FAB")
Application.ScreenUpdating = False
Application.DisplayAlerts = False

  For i = 0 To UBound(ar)
         Sheet1.Range("G1", Sheet1.Range("G" & Sheet1.Rows.Count).End(xlUp)).AutoFilter 1, ar(i), 7, , 0
         lr = Sheet1.Range("G" & Rows.Count).End(xlUp).Row
         If lr > 1 Then
         Sheet1.Range("A2", Sheet1.Range("G" & Sheet1.Rows.Count).End(xlUp)).Copy Sheets(ar(i)).Range("A" & Rows.Count).End(3)(2)
         Sheet1.Range("A2", Sheet1.Range("G" & Sheet1.Rows.Count).End(xlUp)).Delete
         End If
    Next i

Application.DisplayAlerts = True
Application.CutCopyMode = False
Application.ScreenUpdating = True
MsgBox "Data transfer completed!", vbExclamation, "Status"

End Sub

The code filters Column G for the criteria "MACH" and "FAB" and then, when found, transfers the relevant row of data to the relevant sheet. The "used" data in the main sheet is then deleted. You'll note that in the code above, I have used the sheet code rather than the sheet name.

Following is the link to a sample that I have prepared for you. Download the sample and then click on the "RUN" button to see it work.

I hope that this helps.