Copy and paste from Master File

mhaidar81

New Member
Joined
Dec 9, 2017
Messages
17
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.
 

Some videos you may like

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

mhaidar81

New Member
Joined
Dec 9, 2017
Messages
17
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):

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

Well-known Member
Joined
Dec 3, 2018
Messages
11,262
Office Version
2007
Platform
Windows
Try this
On the red line, put the names of the sheets that you do not need to copy

Code:
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
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
11,262
Office Version
2007
Platform
Windows
I'm glad to help you. Thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,096,310
Messages
5,449,608
Members
405,573
Latest member
Diogo Martins

This Week's Hot Topics

Top