Create Transfer Certificate System in Excel

हम जो School Management System - Excel में बना रहे है उसमे हम Transfer Certificate भी आसानी से बना सकते है जिसके लिए आपको सारी चीजे Provide किया जायेगा (जैसे - Transfer Certificate Template, Full Source Code और Full Guidance) केवल आपको Source Code कॉपी करके अपने Project में Paste करना है जिस तरह से बताया जायेगा, और मै पूरी कोशिस करूँगा कि आपको ज्यादा कुछ ना करना पड़े और आपका Transfer Certificate बन के तैयार हो जाये
मै आप लोगो को दो प्रकार का Transfer Certificate Template Provide करूँगा आपको जो Template अच्छा लगे वो आप Use करिये
I will provide two types of Transfer Certificate Template to you, use the template which you like. View and select the template given below -

View Template - 1

View Template - 2

Now we will working on Template - 1 :-

सबसे पहले निचे दिए गए फाइल को Download कर लीजिये (First download the below .zip file)

Download ZIP

Download की गयी zip फाइल को Extract करें - Extract the downloaded zip file.

zip File को Extract करने के बाद आपको एक "Transfer_Certificate_T1" के नाम से एक Excel File मिलेगी Just उसे Open करें
After extracting the zip file you will get an Excel file named as "Transfer_Certificate_T1" Just open it.

उसके बाद आप अपने Project वाली File को Open करें
After that open your project file.

फिर "Transfer_Certificate_T1" में से ( TC_System और TC_Record और TC_1 ) इन तीनों Sheet को अपने Project वाली File में Copy करे
Then from "Transfer_Certificate_T1" ( TC_System and TC_Record and TC_1 ) copy these three sheets into your Project File.

zip File को Extract करने के बाद आपको दो Userform की File मिलेगी Just उसे अपने Project वाली File में Import करें
After extracting the zip file, you will get two userform files, just import them into your project file.

इसके बाद Coding में क्या-क्या Changes करना है Video में बताया गया हैं - After this, what changes have to be done in coding are explained in the video.

Source Code for fetch Transfer Certificate data from "TC_Record" sheet into "TC_1" sheet (into Transfer Certificate Template)

Source Code

'==========================================================
'start code for Show Transfer Certificate Details
'==========================================================
On Error Resume Next
Dim x As Long
Dim y As Long
x = Sheet17.Range("A" & Rows.Count).End(xlUp).Row
For y = 2 To  x
If Sheet4.Range("O4").Value = Sheet17.Cells(y, "A").Value Then
Sheet4.Range("H4").Value = Sheet17.Cells(y, "H").Value
Sheet4.Range("D5").Value = Sheet17.Cells(y, "I").Value
Sheet4.Range("G5").Value = Sheet17.Cells(y, "J").Value
Sheet4.Range("J5").Value = Sheet17.Cells(y, "K").Value
Sheet4.Range("M5").Value = Sheet17.Cells(y, "L").Value
Sheet4.Range("B7").Value = Sheet17.Cells(y, "B").Value
Sheet4.Range("B8").Value = Sheet17.Cells(y, "O").Value
Sheet4.Range("F7").Value = Sheet17.Cells(y, "C").Value
Sheet4.Range("F8").Value = Sheet17.Cells(y, "P").Value
Sheet4.Range("F9").Value = Sheet17.Cells(y, "M").Value
Sheet4.Range("I7").Value = Sheet17.Cells(y, "F").Value
Sheet4.Range("K7").Value = Sheet17.Cells(y, "N").Value
Sheet4.Range("C10").Value = Sheet17.Cells(y, "E").Value
Sheet4.Range("I10").Value = Sheet17.Cells(y, "D").Value
Sheet4.Range("G10").Value = Sheet17.Cells(y, "G").Value
Sheet4.Range("B9").Value = Sheet17.Cells(y, "Q").Value

End If
Next y

Now we will working on Template - 2 :-

सबसे पहले निचे दिए गए .zip फाइल को Download कर लीजिये (First download the below .zip file)

Download ZIP

Download की गयी zip फाइल को Extract करें - Extract the downloaded zip file.

zip File को Extract करने के बाद आपको एक "Transfer_Certificate_T2" के नाम से एक Excel File मिलेगी Just उसे Open करें
After extracting the zip file you will get an Excel file named as "Transfer_Certificate_T2" Just open it.

उसके बाद आप अपने Project वाली File को Open करें
After that open your project file.

फिर "Transfer_Certificate_T2" में से ( TC_System और TC_Record और TC_1 ) इन तीनों Sheet को अपने Project वाली File में Copy करे
Then from "Transfer_Certificate_T2" ( TC_System and TC_Record and TC_1 ) copy these three sheets into your Project File.

zip File को Extract करने के बाद आपको दो Userform की File मिलेगी Just उसे अपने Project वाली File में Import करें
After extracting the zip file, you will get two userform files, just import them into your project file.

Source Code for fetch Transfer Certificate data from "TC_Records" sheet into "TC_2" sheet (into Transfer Certificate Template)

Source Code

'==========================================================
'start code for Show Transfer Certificate Details
'==========================================================
On Error Resume Next
Dim x As Long
Dim y As Long
x = Sheet11.Range("A" & Rows.Count).End(xlUp).Row
For y = 2 To x
If Sheet12.Range("BC4").Value = Sheet11.Cells(y, "A").Value Then
Sheet12.Range("AS13").Value = Sheet11.Cells(y, "A").Value
Sheet12.Range("AF25").Value = Sheet11.Cells(y, "B").Value
Sheet12.Range("AO27").Value = Sheet11.Cells(y, "C").Value
Sheet12.Range("Q15").Value = Sheet11.Cells(y, "D").Value
Sheet12.Range("X17").Value = Sheet11.Cells(y, "E").Value
Sheet12.Range("R19").Value = Sheet11.Cells(y, "F").Value
Sheet12.Range("M21").Value = Sheet11.Cells(y, "G").Value
Sheet12.Range("AF29").Value = Sheet11.Cells(y, "H").Value
Sheet12.Range("AP47").Value = Sheet11.Cells(y, "I").Value
Sheet12.Range("AQ23").Value = Sheet11.Cells(y, "J").Value
Sheet12.Range("R35").Value = Sheet11.Cells(y, "K").Value
Sheet12.Range("AE35").Value = Sheet11.Cells(y, "L").Value
Sheet12.Range("AQ35").Value = Sheet11.Cells(y, "M").Value
Sheet12.Range("G37").Value = Sheet11.Cells(y, "N").Value
Sheet12.Range("S37").Value = Sheet11.Cells(y, "O").Value
Sheet12.Range("AE37").Value = Sheet11.Cells(y, "P").Value
Sheet12.Range("AQ37").Value = Sheet11.Cells(y, "Q").Value
Sheet12.Range("G39").Value = Sheet11.Cells(y, "R").Value
Sheet12.Range("AI41").Value = Sheet11.Cells(y, "S").Value
Sheet12.Range("R43").Value = Sheet11.Cells(y, "T").Value
Sheet12.Range("AL45").Value = Sheet11.Cells(y, "U").Value
Sheet12.Range("U49").Value = Sheet11.Cells(y, "V").Value
Sheet12.Range("Y51").Value = Sheet11.Cells(y, "W").Value
Sheet12.Range("AA57").Value = Sheet11.Cells(y, "Y").Value
Sheet12.Range("AO57").Value = Sheet11.Cells(y, "Z").Value
Sheet12.Range("P59").Value = Sheet11.Cells(y, "AA").Value
Sheet12.Range("Y61").Value = Sheet11.Cells(y, "AB").Value
Sheet12.Range("U63").Value = Sheet11.Cells(y, "AC").Value
Sheet12.Range("V65").Value = Sheet11.Cells(y, "AD").Value

End If
Next y

Last updated on Saturday, August 07, 2021

Create Transfer Certificate System in Excel

Now we will working on Template - 1 :-

Source Code for fetch Transfer Certificate data from "TC_Record" sheet into "TC_1" sheet (into Transfer Certificate Template)

Source Code

Now we will working on Template - 2 :-

Source Code for fetch Transfer Certificate data from "TC_Records" sheet into "TC_2" sheet (into Transfer Certificate Template)

Source Code

Related Posts

Quick Important Link

आइए इसे भी जानें-

आपका बहुत बहुत धन्यवाद हमें Support करने के लिए...!