Copy data from master worksheeet where column contains the current worksheet name

[shaz0503]

Hi all

I'm looking for a macro that will copy rows from a master work sheet where column "f" contains the same value as the worksheet name..

eg: i have 11 worksheets with dept code names and i want to copy row from a master worksheet where column "f" contains the dept code and then loop through remaining worksheets

TIA

Shaz

 

[vcoolio]

Hello Shaz,

Are you looking to transfer data from the Master sheet to the individual sheets or from the individual sheets to the Master sheet?

Cheerio,
vcoolio.

 

[shaz0503]

Hi vcoolio

I'm looking to copy from master sheet to individual sheets

rgds

shaz

 

[vcoolio]

Hello Shaz,

I think that the following code may help:-

Option Explicit
Sub TransferData()

Application.ScreenUpdating = False

        Dim ar As Variant, sh As Worksheet
        Dim i As Integer
        
Set sh = Sheet1
ar = Array("A1001", "A1002", "A1003")
  
  For i = 0 To UBound(ar)
         sh.Range("F1", sh.Range("F" & sh.Rows.Count).End(xlUp)).AutoFilter 1, ar(i)
         sh.Range("A2", sh.Range("F" & sh.Rows.Count).End(xlUp)).Copy
         Sheets(ar(i)).Range("A" & Rows.Count).End(3)(2).PasteSpecial xlValues
         sh.[F1].AutoFilter
         Sheets(ar(i)).Columns.AutoFit
    Next i

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

End Sub

I've made the following assumptions in relation to the set out of your workbook:-

- Sheet1 is the Master sheet.
- Row 1 of each sheet has headings.
- Your data starts in row 2.
- Your data extends from Column A to Column F (Column F being the department codes).

Following is the link to a little mock-up of what I believe your workbook to look like:-

http://ge.tt/3J9mFmm2

The mock-up has a Master sheet (sheet1) and three other sheets which I've named with mock department codes. Click on the "RUN" button to see it work.

Add all your actual department codes to the array in the code above and then test the code in a copy of your workbook first. If something doesn't quite work as it should, then please upload a sample of your workbook (please use dummy data) to a free file sharing site such as GE.TT or Drop Box and then post the link to your file back here.

I hope that this helps.

Cheerio,
vcoolio.

P.S.: BTW Shaz, do you need the "used" data in the Master sheet cleared once each data transfer is completed?

 

[vcoolio]

Hello again Shaz,

There appears to be some problem with the link to the sample workbook so try the following one instead:-

http://ge.tt/90anHmm2

Cheerio,
vcoolio.