Macro to select sheets based on a list of values and copy paste data from one cell to selected sheet

Question

Hi Everyone,

Hoping I can get help on my Macro. I am a beginner and and not even close to perfecting even the basics of VBA.

So I have sheet named General and on column A (A2:A) and column E (E2:2) of this sheet listed are the name of other sheets in the workbook. 

I need a Macro to filter column E based on the value of cell A2 then copy all the visible cells in columns (E:I). After copying (E:I) visible cells I need to find the sheet which name is the same as the value of A2 and paste the copied data there. Lastly I need to create a loop until all names in column A (A2:A) is done. Then unfiltered the General sheet.

So far this is all I've got

Sheets("General").Range("E1").AutoFilter Field:=2, Criteria1:=Cells(2, 1).Value

Range("D:I").SpecialCells(xlCellTypeVisible).Copy

Thanks for your help in advance!

Answer 1

Hi TaneKuma01

With the following code,

1- You will not need the list of sheet names on sheet "General"

2- Does not require to add a button to run the macro

3- The macro will execute upon the activation (selection) of the sheet tab

IMPORTANT

The code below DOES NOT belong to a regular VBA module, it goes on the WorkSheet Event VBA panel

Please, paste the code on each of the relevant sheets for the filtering criteria as shown in the picture below

Here is the code

**************************************************************************

Private Sub Worksheet_Activate()

Dim myName As String

myName = Trim(Me.Name)  ''Activesheet Name

''' Turn off the flickering

Application.ScreenUpdating = False

Me.Cells.Clear

With Sheets("General")

        With .Range("D1").CurrentRegion

            .AutoFilter

            .AutoFilter Field:=2, Criteria1:=myName

            .Copy Me.Range("A1")

         End With

    .ShowAllData  '' Unfilter my Data

 End With

Range("A1").Select

Application.ScreenUpdating = True

End Sub

*************************************************************************************

Do let me know if you need more help

Otherwise

Answer 2

Hi there

I hope you are OK,

We haven't heard from you in the last few days

Would you give us some updates? 

Did you solve your problem?

Is the answer above the solution to your request?

Feel free to give us some feedbacks

Regards