Copy and paste from Master File

[mhaidar81]

I am not sure if this is possible, but I have a master file with 6 columns. The first column has text corresponding to sheet names (a total of 13 sheets).

What I'm looking for is a macro that would move data from the master file to the corresponding sheet based on Column A.

For example, for every cell value in column A that's equal to "Department 1," move the data to sheet "Department 1."

The tricky part is this:

For every cell value in column A that's equal to "Department 1," copy values in columns C, D, E, and F only from the Master to columns A, C, E, and G in Sheet "Department 1."

I would appreciate any help with this.

 

[mhaidar81]

One approach I thought about was a loop that would filter the data by column A, select visible cells, and copy to the appropriate sheet.

Maybe something like this (untested code):

[COLOR=#333333]Sub test()[/COLOR]Dim LR As Long, i As Long
With Sheets("Master")
    LR = .Range("A" & Rows.Count).End(xlUp).Row
    For i = 1 To LR
        If .Range("A" & i).Value = "Department 1" Then .Rows(i).Copy Destination:=Sheets("Department 1").Range("A" & Rows.Count).End(xlUp).Offset(1)
    Next i [COLOR=#333333]End Sub[/COLOR]

I can figure out the loop part, but I still don't know how to copy cells C, D, E, and F to C, E, and G in each sheet.

 

[DanteAmor]

Try this
On the red line, put the names of the sheets that you do not need to copy

Sub Copy_paste()
    Dim sh As Worksheet, sh2 As Worksheet, lr As Long, lr2 As Long
    
    Set sh = Sheets("Master")
    lr = sh.Range("A" & Rows.Count).End(xlUp).Row
    For Each sh2 In Sheets
        Select Case sh2.Name
            Case sh.Name, [COLOR=#ff0000]"Sheet1", "Report", "etc"[/COLOR]
            Case Else
                sh.Range("A1").AutoFilter 1, sh2.Name
                lr2 = sh2.Range("A" & Rows.Count).End(xlUp).Row + 1
                sh.Range("C2:C" & lr).Copy sh2.Cells(lr2, "A")
                sh.Range("D2:D" & lr).Copy sh2.Cells(lr2, "C")
                sh.Range("E2:E" & lr).Copy sh2.Cells(lr2, "E")
                sh.Range("F2:F" & lr).Copy sh2.Cells(lr2, "G")
        End Select
    Next
    sh.ShowAllData
End Sub

 

[mhaidar81]

Worked beautifully, thank you!!

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.