PDA

View Full Version : Solved: repeating a vba code in excel



royanimesh
09-13-2010, 11:23 AM
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

GTO
09-13-2010, 12:15 PM
i dont know wheather i made u understand or not?

Er... Nope! :dunno

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

Aussiebear
09-13-2010, 04:13 PM
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.

royanimesh
09-14-2010, 11:16 AM
Here I am sending the sample worksheet. It will be self explanatory.

thx

royanimesh
09-16-2010, 01:56 PM
please anyone reply

GTO
09-17-2010, 07:07 PM
Greetings,


...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

royanimesh
09-18-2010, 02:46 AM
thanks mark.
you got that right, in stead of formulas, the values will be copied.

thanks a lot

regards

GTO
09-18-2010, 04:35 AM
thanks mark.
you got that right, in stead of formulas, the values will be copied.

thanks a lot

regards

Glad to be of help:beerchug:

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

Thank you,

Mark