Consulting

Results 1 to 8 of 8

Thread: Solved: repeating a vba code in excel

  1. #1

    Solved: repeating a vba code in excel

    hi all

    i had a worksheet for entering data and as well as calculation. e.g. if i enter the ID of a student, it will fetch the relating datas of the student including his/her marks in different sujects. then it will calculate his/her grade, % etc. then if i run a code it will save the file in a desired location.
    i had another sheet containing the students IDs.

    can LOOP or any other statements be used in such a way that if i rua a macro, it will call the student IDs from that sheet one by one and save each sheet.

    i dont know wheather i made u understand or not?

    waiting for response

    regards

  2. #2
    Knowledge Base Approver VBAX Guru GTO's Avatar
    Joined
    Sep 2008
    Posts
    3,362
    Location
    Quote Originally Posted by royanimesh
    i dont know wheather i made u understand or not?
    Er... Nope!

    I have not met you here before, so please understand that to be a friendly tease.

    I think ot would be much easier if you could create some fake students in a copy of your wb and post it; along w/an explanation as what you would like it to do.

    Mark

  3. #3
    Moderator VBAX Guru Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    3,858
    Location
    Most certainly. You can loop through a list of Student ID's and call various proceedures to process the data, then save the changes to each Student.

    Please post a sample workbook so that we can see how you currently carry out the process. Remove any private data before posting the workbook.
    Remember To Do the Following....
    Use tags when posting code to the thread,
    Mark your thread as Solved if satisfied by using the Thread Tools options.
    If posting the same issue to another forum please show the link

  4. #4
    Here I am sending the sample worksheet. It will be self explanatory.

    thx

  5. #5
    please anyone reply

  6. #6
    Knowledge Base Approver VBAX Guru GTO's Avatar
    Joined
    Sep 2008
    Posts
    3,362
    Location
    Greetings,

    Quote Originally Posted by royanimesh
    ...can LOOP or any other statements be used in such a way that if i rua a macro, it will call the student IDs from that sheet one by one and save each sheet.

    i dont know wheather i made u understand or not?...
    I am not sure, but it seems to me tha you are wanting to copy the first sheet, with probably just the vals (instead of the formulas), and make a new file for each student. If so, here is a simple example to see if we are on the same page...

    In a Standard Module:
    Option Explicit
        
    Sub exa()
    Dim wbDest          As Workbook
    Dim aryEnrolNums    As Variant
    Dim EnrolNum        As Variant
        
        '// Using the woksheet's codename, plunk all the enrol numbers into an array    //
        With Sheet2
            aryEnrolNums = .Range(.Range("B3"), .Cells(.Rows.Count, "B").End(xlUp)).Value
        End With
        
        '// For each enrol number in our array...                                       //
        For Each EnrolNum In aryEnrolNums
            '// Set a reference to a new one sheet workbook.                            //
            Set wbDest = Workbooks.Add(xlWorksheet)
            '// Update the enrol number in our sheet.                                   //
            Sheet1.Range("D3").Value = EnrolNum
            '// Just a safety...                                                        //
            Sheet1.Calculate
            '// Now copy the sheet to the new workbook (wb)                             //
            Sheet1.Copy After:=wbDest.Worksheets(1)
            '// and delete the blank sheet that the new wb was created with.            //
            Application.DisplayAlerts = False
            wbDest.Worksheets(1).Delete
            Application.DisplayAlerts = True
            
            '// Then with our copied sheet in the new wb, overwrite the formulas with   //
            '// values.                                                                 //
            wbDest.Worksheets(1).Range("A1:K17").Value = _
                wbDest.Worksheets(1).Range("A1:K17").Value
            
            '// SaveAs and close.                                                       //
            wbDest.SaveAs wbDest.Worksheets(1).Range("K3").Value & _
                          wbDest.Worksheets(1).Range("K4").Value & _
                          ".xls"
            wbDest.Close False
        Next
    End Sub
    Hope that helps,

    Mark

  7. #7

    Smile

    thanks mark.
    you got that right, in stead of formulas, the values will be copied.

    thanks a lot

    regards

  8. #8
    Knowledge Base Approver VBAX Guru GTO's Avatar
    Joined
    Sep 2008
    Posts
    3,362
    Location
    Quote Originally Posted by royanimesh
    thanks mark.
    you got that right, in stead of formulas, the values will be copied.

    thanks a lot

    regards
    Glad to be of help

    If solved, you can mark as such - it's under Thread Tools atop your first post.

    Thank you,

    Mark

Posting Permissions

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