Results 1 to 5 of 5

Thread: Copy and paste from Master File
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Dec 2017
    Posts
    17
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Copy and paste from Master File

    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.

  2. #2
    New Member
    Join Date
    Dec 2017
    Posts
    17
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Copy and paste from Master File

    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:
    Sub test()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 End Sub
    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.


  3. #3
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    4,028
    Post Thanks / Like
    Mentioned
    38 Post(s)
    Tagged
    11 Thread(s)

    Default Re: Copy and paste from Master File

    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, "Sheet1", "Report", "etc"
                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
    Regards Dante Amor

  4. #4
    New Member
    Join Date
    Dec 2017
    Posts
    17
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Copy and paste from Master File

    Worked beautifully, thank you!!

  5. #5
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    4,028
    Post Thanks / Like
    Mentioned
    38 Post(s)
    Tagged
    11 Thread(s)

    Default Re: Copy and paste from Master File

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

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •