Macro to copy data to applicable sheet

I have branch names in Col A and sheet names for each unique name in Col A


I would like code to copy the data to each sheet based on the name in Col A. for eg if the sheet is KLM-1
, and KLM-1 is in Col A, then all the items for KLM-1 is to be copied from col A to Col F , including the header


I have shown what the data is after manually copying the data


It would be appreciated if someone could provide me with code to do this

It depends if working only with already existing worksheets or for all elements of column A
whatever if some worksheet exists or not ? (if not to be created on the run …)

Thanks for the reply Marc


The sheets already exist. The macro must match the sheet name to the item in Col A for eg match sheet KLM-1 to Col A and copy all the items KLM-1 in Col A as well as the items in the columns to the right of KLM-1 that are applicable to KLM-1

Hi Ben


Thanks for pointing this oiut to me and my apologies for transgressing the rules

Like any beginner can operate manually just using a filter so as a beginner starter :

PHP Code: 

Sub Demo1()
   Dim Rg As Range, V, W&
        Application.ScreenUpdating = False
   Set Rg = Worksheets(1).UsedRange
        V = Rg.Range("A1:A2").Value
    For W = 2 To Worksheets.Count
        With Worksheets(W)
             V(2, 1) = .Name
            .UsedRange.Clear
            .[K1:K2].Value = V
             Rg.AdvancedFilter xlFilterCopy, .[K1:K2], .[A1]
            .[K1:K2].Clear
        End With
    Next
        Set Rg = Nothing
        Application.ScreenUpdating = True
End Sub 


Do you like it ? So thanks to click on bottom left star icon « ★ Add Reputation » !

Thanks for the help

Hi Marc


You kindly helped me recently with code to Copy data from Sheet 1 where the Name in Col A matches the sheet name


I need the code amended , so that sheet2 ("purchases") is also copied to the sheets where the names in Col A matches the sheet names from Sheet3 onwards, but after after the last row containing data after the mfirst macro is run to copy the data from sheet1


Your assistance in this regard is most appreciated

Try this !

PHP Code: 

Sub Demo1b()
    Dim Rg(1 To 2) As Range, V, W&
        Application.ScreenUpdating = False
    Set Rg(1) = Worksheets(1).UsedRange
    Set Rg(2) = Worksheets(2).UsedRange
        V = Rg(1).Range("A1:A2").Value
    For W = 3 To Worksheets.Count
        With Worksheets(W)
             V(2, 1) = .Name
            .UsedRange.Clear
            .[K1:K2].Value = V
             Rg(1).AdvancedFilter xlFilterCopy, .[K1:K2], .[A1]
             Rg(2).AdvancedFilter xlFilterInPlace, .[K1:K2]
             Rg(2).Offset(1).Copy .Cells(.UsedRange.Rows.Count + 1, 1)
            .[K1:K2].Clear
        End With
    Next
        If Rg(2).Parent.FilterMode Then Rg(2).Parent.ShowAllData
        Erase Rg
        Application.ScreenUpdating = True
End Sub 


Do you like it ? So thanks to click on bottom left star icon « ★ Add Reputation » !

Thanks Mark. Code works perfectly

Thanks for the help Marc. Code works 100%

Please explain the following items in your code

Please Login or Register  to view this content.

Previous post edited as a codeline was missing …

Constant F for first cell,
Sheet22 is the CodeName of a source data worksheet as you can easily check on VBE side with the Projects window …

Thanks for the explanation