Consulting

Results 1 to 3 of 3

Thread: Using VBA in Excel macro to extract data from table and reconstruct table

Threaded View

Previous Post Previous Post   Next Post Next Post
  1. #1
    VBAX Newbie
    Joined
    Nov 2017
    Posts
    1
    Location

    Using VBA in Excel macro to extract data from table and reconstruct table

    Here is an interesting 'real-world' school based problem.


    There are about 250 students in a year group who generally take ten subjects so there are 2500 rows in a table for each year group. Each row includes the subject/class name/teacher name and the student’s target in that subject. I need to be able to export a spreadsheet for each teacher that will enable them to change targets (in a separate column). I need a save option that will enable that teachers spreadsheet to paste into a master sheets basically re-compiling the master with all the teacher changes.


    So, I think the process is:
    - Copy / paste the teacher name column on the table and remove duplicates so that you have a list of all the teachers.
    - For each teacher name in this list:
    o Filter the table to display that teachers student/ subject/ class
    o Copy into a new sheet, a few rows down to allow for some summary formulas and heading to be added
    o Add some formula columns to the right of the table (but with the same number of rows)
    o Give the records (not the headings) a range name identify the teacher
    o Save with a file name that identifies the teacher and year group
    - Provide a separate Save button for the user such that when pressed the records which are part of the table are copied over the records which already exist in the master.


    That’s the idea.


    I have some spreadsheet of dummy data you can work with if you want to take a closer look
    1. Dummy Data
    2. Example of the data I want to export for one teacher (called Target Review Year 10 Teacher#20)
    3. Example of a reconstructed table (called 3 Recompiled)


    Good luck and all help appreciated
    Attached Files Attached Files

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •