Greetings,
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