Copy and paste from Master File

mhaidar81

New Member
Joined
Dec 9, 2017
Messages
34
Office Version
  1. 365
Platform
  1. Windows
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.
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
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.

 
Upvote 0
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
 
Upvote 0
I'm glad to help you. Thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,213,532
Messages
6,114,176
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top