Copy data from 1 sheet to 3 others meeting criteria

[Shruti]

Hi,

I have raw data in sheet 1 and want to copy this data to other sheets using VBA. The raw data will change time to time and i want to ensure the rest of the data gets updated as well.

as an example i have attached the raw data. Here is what i want to do.
on sheet "Class 2" i want to add all the students' first & last names, marks, division, sports and extra who are in Class 2 and continue this for sheets "Class 3" and "Class 4".

After this if i update marks and division in the Raw Data sheet then it should not add it as a new row but edit the existing row and if there are new rows in the Raw Data sheet then they should get added in respective sheets.

Can someone please help me?


▬▬▬▬▬▬▬▬▬ Mod edit : thread moved to appropriate forum !

​

 

[Chihiro]

There are many ways to do this.
Here's 2 easy way to do this.

1. PowerQuery

• This is my method of choice, see Class 2 ~ 4-PQ sheets in attached

2. Advanced Filter with VBA

• Set up each sheet (Class 2 to 4 in attached) like image below.

Sub AdvFilterClass(cRange As Range, oRange As Range)

    Sheets("Raw Data").Range("Table1[#All]").AdvancedFilter Action:=xlFilterCopy _
        , CriteriaRange:=cRange, CopyToRange:=oRange, Unique:= _
        False
End Sub
Sub UpdateClassTable()
Dim ws As Worksheet

For Each ws In Worksheets(Array("Class 2", "Class 3", "Class 4"))
  Call AdvFilterClass(ws.Range("B1:B2"), ws.Range("A5:I5"))
Next ws

End Sub

 

[Shruti]

Hi,

This really helps.. i would love to use power query it is a very interesting tool. Had never used it before however there is a challenge with power query.
1. i will be updating my database every week
2. i will be adding more columns in the class sheets and if i do that and also update the data there is a possibility of loosing the data.