۵/۵ - (۳ امتیاز)

۱۴ ماکروی آماده و قابل استفاده

حتی اگر هیچی از VBA نمیدونی، این مقاله رو بخون. مطمئن باش خیلی کاربردی خواهد بود. در این مقاله قراره یک سری کدهای آماده و پر استفاده رو در اختیارتون قرار بدیم که حتی اگر کدنویسی هم ندونید، بتونید از این ماکروها استفاده کنید و کارهاتون رو پیش ببرید. همونطور که قبلا در مقاله ماکرونویسی در اکسل دیدیم، استفاده از ماکروها میتونه سرعت و دقت کارها رو به طرز قابل توجهی افزایش بده و مانع انجام کارهای تکراری بشه. (اگر با ماکرو و کاربردهای اون آشنا نیستید حتما مقاله ماکرو در اکسل رو بخونید تا با کاربردهاش آشنا بشید). یکی از راه های کدنویسی (ماکرونویسی) در اکسل ضبط ماکرو هست که پیشنهاد میکنم حتما مقاله ضبط ماکرو رو مطالعه کنید. ضبط ماکرو برای شروع یادگیری و آشنایی با محیط وی بی و … خیلی خوبه اما کافی نیست و کدهایی که از این طریق تولید میشن، عموما خیلی خیلی زیاد و بیش از میزان مورد نیاز است و مثلا کاری که با ۲ خط کد براحتی قابل انجامه، از طریق ضبط ماکرو ممکنه ۱۰-۲۰ خط بشه. علاوه بر اینکه بعضی مسائل از طریق ضبط ماکرو به کد تبدیل نمیشن، مثل زمان (مثلا بخواهیم یک دستوری ،یک دقیقه بعد از دستور قبلی اجرا بشه. این مسائل از طریق ضبط به کد تبدیل نمیشن). در هر صورت خیلی وقت ها نیاز هست که کدها بعد از ضبط، ویرایش بشن که این کار نیاز به دانش کدنویسی و آشنایی با زبان VBA داره. اما گاهی اوقات هم ممکنه کد آماده ای رو متناسب با نیازتون پیدا کنید و فقط لازم باشه که وارد فایل اکسل کنید و استفاده کنید. پس بدون نگرانی از اینکه احتمالا دانش VBA کافی ندارید، ادامه این مقاله رو مطالعه کنید و لذت ببرید.

در این مقاله ابتدا نحوه کپی کردن کد مورد نظر در اکسل و نحوه اجرای اون رو آموزش میدیم و بعد تعدادی ماکروی نوشته شده و آماده استفاده رو در اختیارتون قرار میدیم که هم با کدنویسی آشنا تر بشید و هم اینکه بتونید از این کدها برای انجام کارهاتون استفاده کنید. ما سعی میکنیم که این مقاله رو اپدیت کنیم و ماکروهای بیشتری بهش اضافه کنیم. پس اگر فکر میکنید موضوعی بهتره که اضافه بشه، برای ما در کامنت اعلام کنید، ما سعی میکنیم که کد ماکرو مربوط به اون مسئله در این مقاله قرار بدیم. پس این صفحه رو به عنوان رفرنس ذخیره کنید که بعدا هم بهش سر بزنید.

آنچه در این مقاله میخوانید:

مراحل اضافه کردن کد مورد نظر به یک فایل اکسل

ذخیره کردن فایل

نحوه اجرای یک ماکرو

ماکروهای آماده قابل استفاده

۱. Unhide کردن همه شیت ها با یک حرکت

۲. Hide کردن همه شیت ها به جز شیت فعال

۳. مرتب کردن شیت ها به ترتیب حروف الفبا

۴. Protect کردن همه شیت ها با یک حرکت

۵. Unprotect کردن همه شیت ها با یک حرکت

۶. ذخیره کردن فایل اکسل به همراه زمان و تاریخ سیو کردن در نام

۷. ذخیره هر شیت بصورت یک PDF جدا

۸. ایجاد یک ردیف خالی بین ردیف های محدوده انتخابی

۹. ثبت تاریخ و زمان در یک سل خاص

۱۰. تغییر اندازه حروف داده های موجود در یک ستون

۱۱. ذخیره هر شیت بصورت یک فایل اکسل جدا

۱۲. انتخاب بیش از یک آیتم در لیست دیتاولیدیشن

۱۳. Bold کردن بخشی از یک سلول

۱۴. اضافه کردن ردیف خالی به تعداد مشخص

دانلود فایل اکسل ماکروهای نمونه

مراحل اضافه کردن کد مورد نظر به یک فایل اکسل

قبل از اینکه بریم سراغ کدهای مورد نظر، باید راجع به نحوه استفاده از این کدها صحبت کنیم. اینکه چطور این کدها رو وارد اکسل کنیم. برای اینکه کد مورد نظر رو وارد یک فایل اکسل بکنیم، مراحل زیر رو انجام میدیم:

فایل اکسلی میخواهیم کد رو واردش کنیم باز کنید
کلید ترکیبی Alt+F11 رو بزنید یا روی سربرگ یک شیت کلیک راست کرده و View Code رو بزنید.
روی هر قسمتی از Project Explorer کلیک راست کنید و Insert Module رو بزنید. (شکل ۱)
حالا کافیه کد مورد نظر رو در پنجره سمت راست کپی کنید.

شکل ۱- نحوه ایجاد ماژول برای نگهداری کد

نکته:
گاهی اوقات نیاز هست که کد مورد نظر در شیت خاصی قرار بگیره (برای درک بهتر این موضوع مقاله ساخت آلارم در اکسل رو بخونید در این مقاله در مورد Event در اکسل صحبت شده که اینجا به درد میخوره). برای این کار باید در فضای Project Explore روی شیت مورد نظر دبل کلیک کنیم و کد رو کپی کنیم. در واقع نیازی به ماژول وجود نداره.

ذخیره کردن فایل

بعد از اینکه کد رو کپی کردیم باید فایل رو بصورت Macro enabled با پسوند .xlsm ذخیره کنیم. دقت کنید که اگر این کار رو انجام ندیم و فایل رو بصورت معمولی ذخیره کنیم، کدهای موجود در فایل حذف خواهند شد.

نحوه اجرای یک ماکرو

برای اجرای ماکروهای موجود در یک فایل، راه های مختلفی وجود داره که پیشنهاد میکنم حتما مقاله مربوط به ماکرو رو مطالعه کنید. در اینجا به یک راه اشاره میکنیم:

به تب Developer برید.
روی گزینه Macros کلیک کنید.
از پنجره ای که باز میشه، ماکروی مورد نظر رو انتخاب کرده و Run بزنید.

شکل ۲- تب Developer و فعالسازی ماکرو

نکته:
برای مطالعه دقیق تر و با جزدیات بیشتر از نحوه اجرای ماکروها ،این ۳ مقاله رو حتما مطالعه کنید:

همه چیز در مورد ماکرو در اکسل-قسمت اول

همه چیز در مورد ماکرو در اکسل- بخش دوم

همه چیز در مورد ماکرو در اکسل- بخش سوم

ماکروهای آماده قابل استفاده

حالا بریم سراغ ماکروهای آماده ای که کمک میکنه کارها بصورت خودکار انجام بشن:

Unhide کردن همه شیت ها با یک حرکت
Hide کردن همه شیت ها به جز شیت فعال
مرتب کردن شیت ها به ترتیب حروف الفبا
Protect کردن همه شیت ها با یک حرکت
Unprotect کردن همه شیت ها با یک حرکت
ذخیره کردن فایل اکسل به همراه زمان و تاریخ سیو کردن در نام
ذخیره هر شیت بصورت یک PDF جدا
ایجاد یک ردیف خالی بین ردیف های محدوده انتخابی
ثبت تاریخ و زمان در یک سل خاص
تغییر اندازه حروف داده های موجود در یک ستون
ذخیره هر شیت بصورت یک فایل اکسل جدا

۱. Unhide کردن همه شیت ها با یک حرکت

همونطور که میدونیم Unhide کردن شیت ها بر خلاف Hide کردن، باید تک به تک صورت بگیره. در واقع وقتی مثلا ۱۰ شیت رو hide میکنیم، برای Unhide کردن باید ۱۰ بار این کار رو انجام بدیم و هر بار یکی از شیت های پنهان شده رو Unhide کنیم. البته این موضوع در ورژن ۲۰۲۱ برطرف شده و میتونیم بصورت گروهی، Hide/Unhide انجام بدیم. پس این ماکرو برای ورژن قبل از ۲۰۲۱ کاربرد داره.

‘ این ماکرو همه شیت های موجوددر یک فایل رو Unhide میکنه
Sub UnhideAllWoksheets()
Dim ws As Worksheet
     For Each ws In ActiveWorkbook.Worksheets
          ws.Visible = xlSheetVisible
     Next ws
End Sub

‘ این ماکرو همه شیت های موجوددر یک فایل رو Unhide میکنه

Sub UnhideAllWoksheets()

Dim ws As Worksheet

For Each ws In ActiveWorkbook.Worksheets

ws.Visible = xlSheetVisible

Next ws

End Sub

کد بالا از یک حلقه استفاده میکنه که در یک فایل میره سراغ هر شیت و اونو Unhide یا در واقع Visible میکنه.

نکته:
جمله سبز رنگ که با ‘ شروع میشه، توضیحات مربوط به کد مورد نظر است که بهش Comment گفته میشه و در اجرای کد اثری نداره و به هر زبانی میتونه باشه و صرفا توضیحی راجع به اینه که اون کد در چه موضوعی است. اگر عبارات فارسی بهم ریخته نمایش داده میشن، باید تنظیمات ویندوز رو تغییر بدیم. در واقع باید تنظیمی انجام بدیم که حروف پارسی در محیط وی بی خوانا بشه. برای این کار از مسیر زیر Change System Locale رو روی Persian تنظیم میکنیم و سیستم رو Restart میکنیم.

Control Panel/ Region/ Administrative

۲. Hide کردن همه شیت ها به جز شیت فعال

فرض کنید داشبورد یا گزارشی تهیه کردید، میخواید خیلی سریع همه شیت ها رو به جز شیت گزارش (شیت فعال) پنهان کنید. برای این کار از ماکروز زیر استفاده میکنیم:

'این ماکرو همه شیت ها به جز شیت فعال رو پنهان میکنه
Sub HideAllExceptActiveSheet()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
If ws.Name <> ActiveSheet.Name Then ws.Visible = xlSheetHidden
Next ws
End Sub

'این ماکرو همه شیت ها به جز شیت فعال رو پنهان میکنه

Sub HideAllExceptActiveSheet()

Dim ws As Worksheet

For Each ws In ThisWorkbook.Worksheets

If ws.Name <> ActiveSheet.Name Then ws.Visible = xlSheetHidden

Next ws

End Sub

۳. مرتب کردن شیت ها به ترتیب حروف الفبا

گاهی وقتا پیش میاد که بخوایم شیت های موجود در یک فایل رو به ترتیب حروف الفبا مرتب کنیم. مثلا نام شیت ها، اسم کارکنان، اسم محصول و عدد و … است و میخواهیم مرتب کنیم. برای این کار هیچ امکان و تنظیم مستقیما در اکسل وجود نداره پس باید بریم سراغ کدنویسی و از ماکروی زیر استفاده کنیم.

در حال بارگذاری پخش کننده...

'این ماکرو شیت ها رو به ترتیب حروف الفبا مرتب میکنه
Sub SortSheetsTabName()
Application.ScreenUpdating = False
Dim ShCount As Integer, i As Integer, j As Integer
ShCount = Sheets.Count
For i = 1 To ShCount - 1
For j = i + 1 To ShCount
If Sheets(j).Name < Sheets(i).Name Then
Sheets(j).Move before:=Sheets(i)
End If
Next j
Next i
Application.ScreenUpdating = True
End Sub

۱۰

۱۱

۱۲

۱۳

۱۴

'این ماکرو شیت ها رو به ترتیب حروف الفبا مرتب میکنه

Sub SortSheetsTabName()

Application.ScreenUpdating = False

Dim ShCount As Integer, i As Integer, j As Integer

ShCount = Sheets.Count

For i = ۱ To ShCount - ۱

For j = i + ۱ To ShCount

If Sheets(j).Name < Sheets(i).Name Then

Sheets(j).Move before:=Sheets(i)

End If

Next j

Next i

Application.ScreenUpdating = True

End Sub

نکته:
این کد بصورت صعودی عمل میکنه. اگر خواستیم بصورت نزولی باشه، کافیه علامت < در کد رو به > تبدیل کنیم.

۴. Protect کردن همه شیت ها با یک حرکت

میدونیم که برای قفل کردن هر شیت باید جداگانه protect کنیم. حالا وقتی شیت ها زیاده و میخوایم یکجا همه رو قفل کنیم از کد زیر استفاده میکنیم.

'این ماکرو همه شیت ها رو قفل میکنه
Sub ProtectAllSheets()
Dim ws As Worksheet
Dim password As String
password = "Excelpedia" 
	For Each ws In Worksheets
		ws.Protect password:=password
	Next ws
End Sub

'این ماکرو همه شیت ها رو قفل میکنه

Sub ProtectAllSheets()

Dim ws As Worksheet

Dim password As String

password = "Excelpedia"

For Each ws In Worksheets

ws.Protect password:=password

Next ws

End Sub

نکته:
پسورد مورد نظر که در بالا کلمه Excelpedia است میتونه هر چیزی باشه و تغییر کنه.

۵. Unprotect کردن همه شیت ها با یک حرکت

وقتی همه شیت ها رو قفل کردیم، حالا باید بتونیم خیلی سریع همه رو باز کنیم. برای این کار کافیه کد Protect رو به Unprotect تبدیل کنیم.

'این ماکرو همه شیت ها رو باز میکنه
Sub UnProtectAllSheets()
Dim ws As Worksheet
Dim password As String
password = "Excelpedia"
	For Each ws In Worksheets
		ws.Unprotect password:=password
	Next ws
End Sub

'این ماکرو همه شیت ها رو باز میکنه

Sub UnProtectAllSheets()

Dim ws As Worksheet

Dim password As String

password = "Excelpedia"

For Each ws In Worksheets

ws.Unprotect password:=password

Next ws

End Sub

نکته:
دقت داشته باشید پسوردی که دقیقا موقع قفل کردن استفاده شده نوشته بشه.

برای جذاب تر شدن، میتونیم پسورد رو از طریق یک Inputbox وارد کد بکنیم. مقاله مرتبط: مقاله Inputbox

برای این کار کافیه در کد بالا بجای Excelpedia بنویسیم Inputbox (“”) یعنی :

Sub UnProtectAllSheets()
Dim ws As Worksheet
Dim password As String
password = InputBox("پسورد مورد نظر را وارد کنید")
    For Each ws In Worksheets
       ws.Unprotect password:=password
    Next ws
End Sub

Sub UnProtectAllSheets()

Dim ws As Worksheet

Dim password As String

password = InputBox("پسورد مورد نظر را وارد کنید")

For Each ws In Worksheets

ws.Unprotect password:=password

Next ws

End Sub

۶. ذخیره کردن فایل اکسل به همراه زمان و تاریخ سیو کردن در نام

خیلی وقت ها ما میخوایم که نسخه های مختلفی از یک فایل رو ذخیره کنیم. یکی از بهترین راه ها اینه که موقع ذخیره کردن، اسم فایل به همراه تاریخ و زمان ذخیره کردن ثبت بشه.

'این کد فایل رو با تاریخ و زمان ذخیره کردن سیو میکنه
Sub SaveWorkbookWithTimeStamp()
Dim timestamp As String
	timestamp = Format(Date, "dd-mm-yyyy") & "_" & Format(Time, "hh-mm-ss")
	ThisWorkbook.SaveAs "C:\Users\Folder\FileName" & timestamp
End Sub

'این کد فایل رو با تاریخ و زمان ذخیره کردن سیو میکنه

Sub SaveWorkbookWithTimeStamp()

Dim timestamp As String

timestamp = Format(Date, "dd-mm-yyyy") & "_" & Format(Time, "hh-mm-ss")

ThisWorkbook.SaveAs "C:\Users\Folder\FileName" & timestamp

End Sub

مقداری که با رنگ قرمز نمایش داده شده، مسیر ذخیره فایل مورد نظر و اسم فایل مورد نظر است. مثلا اگر مسیر ذخیره تغییر پیدا کنه به درایو و فولدر دیگری، باید قسمت قرمز رنگ رو تغییر بدیم. مثلا من میخوام فایل رو در درایو D و فولدر Project و با نام Excelpedia ذخیره کنم. قسمت قرمز رنگ در کد بالا به شرح زیر تغییر میکنه:

“D:\Project\Excelpedia”

۷. ذخیره هر شیت بصورت یک PDF جدا

گاهی اوقات پیش میاد که بخوایم در یک فایل هر شیت رو به یک PDF تبدیل کنیم. مثلا با استفاده از Report filter page در Pivotable گزارشی رو به آیتم های مختلف در هر شیت تفکیک کردیم و حالا میخواهیم هر کدوم رو به یک PDF تبدیل کنیم.

در حال بارگذاری پخش کننده...

'ماکروی ذخیره شیت های یک فایل به پی دی اف
Sub SaveWorkshetAsPDF()
Dim ws As Worksheet
For Each ws In Worksheets
ws.ExportAsFixedFormat xlTypePDF, “C:\Users\ExcelPedia\Desktop\PDF test\” & ws.Name & ".pdf"
Next ws
End Sub

'ماکروی ذخیره شیت های یک فایل به پی دی اف

Sub SaveWorkshetAsPDF()

Dim ws As Worksheet

For Each ws In Worksheets

ws.ExportAsFixedFormat xlTypePDF, “C:\Users\ExcelPedia\Desktop\PDF test\” & ws.Name & ".pdf"

Next ws

End Sub

در ماکروی بالا قسمت “C:\Users\ExcelPedia\Desktop\PDF test\”، مسیر ذخیره رو نشون میده. معنی کد بالا اینه:
همه شیت های فایل فعلی، بصورت جداگانه و با نام خودشون (نام هر شیت) ، با پسوند PDF ، در درایو C و در Desktop و در فولدری به نام PDF Test ذخیره می شوند. (شکل ۳)

شکل ۳- تبدیل هر شیت از یک فایل به PDF

پس قسمت قرمز رنگ رو مطابق با خواسته خودتون تغییر بدید. برای پیدا کردن آدرس مسیر ذخیره کافیه فولدر مورد نظر رو در هر جایی که دوست دارید بسازید و بازش کنید. وقتی باز کردید از نوار نشان داده شده در شکل ۴ میتونید آدرس مسیر ذخیره رو کپی کنید و بیارید داخل کد بالا. توجه کنید که \ آخر رو خودتون اضافه کنید.

شکل ۴- پیدا کردن آدرس مسیر ذخیره فایل های مورد نظر

۸. ایجاد یک ردیف خالی بین ردیف های محدوده انتخابی

گاهی اوقات لازمه که بین داده های وارد شده در یک شیت، ردیف خالی ایجاد بشه. البته این کار رو با استفاده از ابزار Sort و با یک ترفند میشه انجام داد. ما هم در اینجا ماکرو مربوط به این کار رو آماده کردیم:

'ایجاد ردیف خالی
Sub InsertAlternateRows()
Dim rng As Range
Dim CountRow As Integer
Dim i As Integer
Set rng = Selection
CountRow = rng.EntireRow.Count
	For i = 1 To CountRow
		ActiveCell.EntireRow.Insert
		ActiveCell.Offset(2, 0).Select
	Next i
End Sub

۱۰

۱۱

۱۲

'ایجاد ردیف خالی

Sub InsertAlternateRows()

Dim rng As Range

Dim CountRow As Integer

Dim i As Integer

Set rng = Selection

CountRow = rng.EntireRow.Count

For i = ۱ To CountRow

ActiveCell.EntireRow.Insert

ActiveCell.Offset(۲, ۰).Select

Next i

End Sub

برای اجرای این کد، کافیه که محدوده مورد نظر رو انتخاب کنیم و کد رو اجرا کنیم. بالای هر ردیف در محدوده انتخابی، یک ردیف خالی اضافه میشه. مطابق شکل ۵، محدوده A1:D5 رو انتخاب میکنیم و بعد از اجرای کد، جدول سمت راست رو مشاهده میکنیم.

شکل ۵- ماکروی ایجاد ردیف خالی بین هر ردیف

۹. ثبت تاریخ و زمان در یک سل خاص

گاهی اوقات پیش میاد که بخوایم زمان ثبت ورود داده در یک سلول، در سلول کناری ثبت بشه. این کار برای ردیابی و ثبت روند انجام یک کار خیلی مورد استافده قرار میگیره. برای این کار از ماکروی زیر استفاده میکنیم.

'ثبت تاریخ موقع ثبت دیتا
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo Handler
	If Target.Column = 1 And Target.Value <> "" Then
		Application.EnableEvents = False
Target.Offset(0, 1) = Format(Now(), "dd-mm-yyyy hh:mm:ss")
		Application.EnableEvents = True
	End If
Handler:
End Sub

۱۰

'ثبت تاریخ موقع ثبت دیتا

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo Handler

If Target.Column = ۱ And Target.Value <> "" Then

Application.EnableEvents = False

Target.Offset(۰, ۱) = Format(Now(), "dd-mm-yyyy hh:mm:ss")

Application.EnableEvents = True

End If

Handler:

End Sub

دقت داشته باشید که این کد، در ماژول نباید ذخیره بشه. باید در قسمت مربوط به شیت وارد بشه. مثلا فرض کنید ما میخواهیم برای داده های موجود در Sheet1 این کار رو بکنیم پس در محیط VBE روی sheet1 دبل کلیک میکنیم و در سمت راست، کد بالا رو کپی میکنیم.
نکته بعد اینکه این کد برای ستون A نوشته شده. یعنی اگه ورود داده در ستون A اتفاق بیفته، تاریخ در سلول کناری در ستون B ثبت بشه. این موضوع رو ما در Target.Column = 1 مشخص کردیم که ۱ یعنی A. پس در صورت نیاز میتونید این عدد رو تغییر بدید.
این کد نیاز به اجرا نداره و همونطور که در مقاله اجرای کد دیدید، این کد به محض ایجاد تغییر در شیت ۱، خودبخود اجرا میشه.
اگر بخوایم زمان ورود داده در یک کامنت ثبت بشه، کافیه Target.Offset(0, 1) رو به Target.AddComment.Text Text: تغییر بدیم:

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo Handler
    If Target.Column = 1 And Target.Value <> "" Then
        Application.EnableEvents = False
Target.AddComment.Text Text:=Format(Now(), "dd-mm-yyyy hh:mm:ss")
        Application.EnableEvents = True
    End If
Handler:
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo Handler

If Target.Column = ۱ And Target.Value <> "" Then

Application.EnableEvents = False

Target.AddComment.Text Text:=Format(Now(), "dd-mm-yyyy hh:mm:ss")

Application.EnableEvents = True

End If

Handler:

End Sub

۱۰. تغییر اندازه حروف داده های موجود در یک ستون

فرض کنید در یک ستون یک سری داده انگلیسی وجود داره که مثلا میخوایم خیلی سریع حرف اول همه کلمات رو بزرگ و مابقی رو کوچک کنیم. البته برای این کار توابعی وجود داره و میتونیم از توابع استفاده کنیم.برای دیدن توابع مورد نظر حتما مقاله زیر رو ببینید:

مقاله مرتبط: تابع Upper, lower, proper

ماکرو مورد نظر بصورت زیر است:

'تبدیل همه حروف به بزرگ
Sub ChangeCase()
Dim Rng As Range
	For Each Rng In Selection.Cells
		If Rng.HasFormula = False Then
			Rng.Value = UCase(Rng.Value)
		End If
	Next Rng
End Sub

'تبدیل همه حروف به بزرگ

Sub ChangeCase()

Dim Rng As Range

For Each Rng In Selection.Cells

If Rng.HasFormula = False Then

Rng.Value = UCase(Rng.Value)

End If

Next Rng

End Sub

اول محدوده مورد نظر رو انتخاب کنید و بعد ماکرو رو اجرا کنید. همه حروف به بزرگ تبدیل میشن.

نکته: این امکان بدون نیاز به کد و تابع، در گوگل شیت وجود داره.

۱۱. ذخیره هر شیت بصورت یک فایل اکسل جدا

یک فایل اکسل داریم که حاوی شیت های متنوعی است. میخواهیم هر شیت، بصورت یک فایل اکسل جداگانه ذخیره بشه. برای این کار از ماکروی زیر استفاده میکنیم. این ماکرو از هر شیت در یک فایل جدید کپی میگیره و هر فایل جدید رو با اسم شیت، ذخیره میکنه. کجا؟ در آدرسی که مشخص شده در کد. در اینجا داره در درایو D و در فولدر Excelpedia Docs این کار و میکنه که شما بنا به سیستم خودتون باید تغییرش بدید. در انتها سوالی میپرسه مبنی بر ذخیره با ماکرو و … (شکل ۶) گزینه yes رو انتخاب کنید.

'تبدیل هر شیت به یک اکسل جداگانه
Sub SplitWorkbookIntoSheets()
Dim WorkbookName As String
Dim CurrentSheet As Worksheet
Dim NewWorkbook As Workbook
        For Each CurrentSheet In ThisWorkbook.Sheets
            Set NewWorkbook = Workbooks.Add(xlWBATWorksheet)
            CurrentSheet.Copy After:=NewWorkbook.Sheets(1)
            NewWorkbook.SaveAs Filename:="D:\Excelpedia Docs\" & CurrentSheet.Name & ".xlsx"
            NewWorkbook.Close
        Next CurrentSheet
ThisWorkbook.Close SaveChanges:=False
End Sub

۱۰

۱۱

۱۲

۱۳

'تبدیل هر شیت به یک اکسل جداگانه

Sub SplitWorkbookIntoSheets()

Dim WorkbookName As String

Dim CurrentSheet As Worksheet

Dim NewWorkbook As Workbook

For Each CurrentSheet In ThisWorkbook.Sheets

Set NewWorkbook = Workbooks.Add(xlWBATWorksheet)

CurrentSheet.Copy After:=NewWorkbook.Sheets(۱)

NewWorkbook.SaveAs Filename:="D:\Excelpedia Docs\" & CurrentSheet.Name & ".xlsx"

NewWorkbook.Close

Next CurrentSheet

ThisWorkbook.Close SaveChanges:=False

End Sub

شکل ۶- نمایش پیغام در انتهای کد و انتخاب گزینه yes

۱۲. انتخاب بیش از یک آیتم در لیست دیتاولیدیشن

گاهی اوقات پیش میاد که بخوایم از یک لیست دیتاولیدیشن، چند آیتم رو انتخاب کنیم. مثلا میخواهیم کارهایی رو به اعضای یک تیم Assign کنیم. برای هر نفر، کارهایی ر واز لیست انتخاب کنیم که به موارد قبلی اضافه بشه. برا یاین کار باید ماکرویی به فایل اضافه کنیم که بتونه موارد دیتاولیدیشن رو اضافه کنه. ۳ ماکرو ارائه دادیم که یکیش قابلیت اضافه کردن دیتای تکراری رو داره، دومی ،امان اضافه کردن آیتم تکراری رو نمیده، سومی امکان حذف آیتم انتخاب شده رو در اختیارمون قرار میده. چون توضیحات این ماکرو زیاده و نحوه ویرایش هم بصورت مجزا باید توضیح داده بشه، مقاله جداگانه ای برای این موضوع ارائه دادیم که میتونید برید به این لینک هم ماکروها رو ببینید و ویرایش کنید و هم فایل آماده حاوی این ۳ ماکرو رو دانلود و استفاده کنید.

۱۳. Bold کردن بخشی از یک سلول

گاهی اوقات میخوایم که در یک رشته متنی در یک سلول، اگر عبارت مورد نظر ما وجود داشت، اون عبارت مشخص بشه و مثلا Bold بشه.

شکل ۷- بولد کردن بخشی از یک رشته متنی در سلول

برای این کار کافیه ماکرو زیر رو کپی کنیم و طبق دستورات بالا ازش استفاده کنیم. این کد وقتی اجرا میشه، یک باکس ورودی نمایش داده میشه که بتونید کلمه مورد نظر رو داخلش تایپ کنید. بعد از زدن OK هر جا که ان عبارت وجود داشته باشه، Bold میشه. ماکرو زیر محدوده a1:a10 ر پوشش میده که در صورت نیاز میتونید تغییرش بدید.

'بولد کردن یک کلمه خاص در یک رشته متنی
Sub bold_text_in_string()
Dim r As Range
Dim cell As Range
    Set r = Range("a1:a10")
        text_value = InputBox("Please Enter Text You Want to Search and Bold")
            For Each cell In r
                If InStr(cell.Text, text_value) Then
                    cell.Characters(WorksheetFunction.Find(text_value, cell.Value), Len(text_value)).Font.Bold = True
                End If
            Next
End Sub

۱۰

۱۱

۱۲

'بولد کردن یک کلمه خاص در یک رشته متنی

Sub bold_text_in_string()

Dim r As Range

Dim cell As Range

Set r = Range("a1:a10")

text_value = InputBox("Please Enter Text You Want to Search and Bold")

For Each cell In r

If InStr(cell.Text, text_value) Then

cell.Characters(WorksheetFunction.Find(text_value, cell.Value), Len(text_value)).Font.Bold = True

End If

End Sub

۱۴. اضافه کردن ردیف خالی به تعداد مشخص

در این ماکرو میخوایم به تعداد مشخصی ردیف در مکان مشخص اضافه کنیم. مثلا در یک سلول بنویسیم ۵ و ۵ ردیف خالی اضافه بشه. در این ماکرو در سلول G1 تعداد ردیف مورد نظر رو مینویسیم و روی یک سلول قرار میگیریم و ماکرو رو اجرا میکنیم.

'اضافه کردن ردیف به تعداد مشخص
Sub Insert_Rows()

m = Range("G1").Value
ActiveCell.Rows("1:" & m).EntireRow.Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
End Sub

'اضافه کردن ردیف به تعداد مشخص

Sub Insert_Rows()

m = Range("G1").Value

ActiveCell.Rows("۱:" & m).EntireRow.Select

Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove

End Sub

خب اینم ۱۴ تا ماکروی آماده و قابل استفاده که توضیح داده شد. دیگه چه مسائل عمومی به نظرتون میرسه که بتونیم براش کد آماده ارائه بدیم؟ حتما در ادامه همین مقاله کامنت بذارید تا در صورت امکان، کدشو اینجا قرار بدیم. پس این آموزش رو به عنوان رفرنس بوکمارک کنید و بهش سر بزنید، قراره ماکروهای بیشتری هم بهش اضافه بشه.

همونطور که مشاهده کردید، برای کارهای مختلفی ،میشه از کدهای VBA استفاده کرد. نکته مهم اینه که حتی اگه هیچی از VBA ندونید، با این آموزش میتونید قدم به قدم مراحل رو اجرا کرده و کارهاتون رو پیش ببرید. اما خب اینها کدهای آماده است و فقط همون کاری رو میکنه که گفته شده. اگر بخواید تغییراتی ایجاد کنید و متناسب با نیاز ازشون استفاده کنید، باید معنی کدهای نوشته شده رو بدونید تا بتونید تغییرات دلخواه رو روش اعمال کنید. پس حتما پیشنهاد میکنم دوره کدنویسی به زبان VBA رو بررسی کنید و در صورت تمایل، با ۲۰ درصد تخفیف دریافت کنید.

دانلود فایل اکسل ماکروهای نمونه

برای دانلود فایل اکسل ماکروهای نمونه از لینک زیر استفاده کنید:

توجه: جهت دانلود فایل حتما در حساب کاربری خود وارد شوید.

دیدگاه کاربران

nazanin ۲۴ مرداد ۱۴۰۳ / ۱:۵۲ ب٫ظ

پاسخ

سلام وقت بخیر کلاس خصوصی ندارید؟؟؟؟ یا اینکه سفارش داشبورد مدیریتی فروش را میپذیرید؟؟؟
- حسنا خاکزاد ۲۶ مرداد ۱۴۰۳ / ۵:۴۰ ب٫ظ
  
  پاسخ
  
  درود بر شما
  کلاس خصوصی خیر
  میتونید از دوره های ویدئویی که پشتیبانی دارن استفاده کنید
  داشبورد هم بله
  به شماره ۰۹۳۶۵۸۸۴۳۳۲ واتس اپ یا تلگرام پیام بدید و توضیحات رو ارسال بفرمایید
سپیده مرادی ۱۷ آذر ۱۴۰۲ / ۴:۳۳ ب٫ظ

پاسخ

سلام
ممنون از آموزش های بسیار خوبتون
من یک فایل اکسل دارم که تعدادی از سلول‌ها در آن لیست کشویی دارند.کدی میخواهم که به محض باز کردن فایل اکسل محتوای نمایش داده شده در سلول دارای لیست کشویی را ذخیره کند و هنگام بستن فایل اکسل ابتدا محتوای سلول دارای لیست کشویی را به حالت اولیه موقع باز کردن فایل برگرداند و بعد فایل بسته شود. یعنی محتوای سلول کشویی همان باشد که موقع باز کردن فایل بود.
- حسنا خاکزاد ۲۱ آذر ۱۴۰۲ / ۹:۵۹ ق٫ظ
  
  پاسخ
  
  درود بر شما
  میتونید دلیل و منطق این موضوع رو توضیح بدید شاید راه های بهتری وجود داشته باشه؟
  با مثال ترجیحا
محمد ذاکری ۳۱ شهریور ۱۴۰۲ / ۱۰:۳۱ ق٫ظ

پاسخ

سلام و سپاس از زحمات شما، بسیار عالی و کارآمد بود.
سعید بروزی نیت ۲۶ مرداد ۱۴۰۲ / ۹:۴۲ ق٫ظ

پاسخ

عالی بود. خیلی لذت بردم.
خاکباز ۲۳ مرداد ۱۴۰۲ / ۷:۵۵ ب٫ظ

پاسخ

باسلام و ارادت. ضمن عرض خدا قوت، از ارائه آموزشهای خوبتان تشکر میکنم
برقرار باشید
خاکباز ۲۳ مرداد ۱۴۰۲ / ۷:۵۵ ب٫ظ

پاسخ

باسلام و ارادت. ضمن عرض خدا قوت، از ارائه آموزشهای خوبتان تشکر میکنم
محسن نظری ۲۱ مرداد ۱۴۰۲ / ۱۲:۴۰ ب٫ظ

پاسخ

باسلام
تشکر از آموزش های عالی و کاربردی شما
در ذخیره هر شیت بصورت یک PDF جدا، امکانش هست که فایل با تغییرات در ماژول نویسی به جای pdf اکسل ذخیره بشه ؟ ممنون ازشما
- حسنا خاکزاد ۲۱ مرداد ۱۴۰۲ / ۱:۱۴ ب٫ظ
  
  پاسخ
  
  درود بر شما
  بله چرا که نه فرمت رو داخل کد تغییر بدید
  اینو هم ببینید:
  لینک کد
  - محسن نظری ۲۱ مرداد ۱۴۰۲ / ۱:۵۸ ب٫ظ
    
    پاسخ
    
    سپاس ، خیلی عالی
شهراد ۱۱ خرداد ۱۴۰۲ / ۹:۱۷ ق٫ظ

پاسخ

خیلی خوب بود. سپاس
javad ashoori ۳ خرداد ۱۴۰۲ / ۱:۵۹ ب٫ظ

پاسخ

خیلی عالی
امید کشاورز ۲۸ اردیبهشت ۱۴۰۲ / ۰:۵۴ ق٫ظ

پاسخ

سلام و خسته نباشید و ممنون از مطالب مفیدتون.
من برای replace چند عبارت با یک عبارت راهی پیدا نکردم، نیاز به ماکرونویسی داره؟ مثلا هرجا عبارتهای a,b,c بود بجاش d بذاره، نمیخام سه بار از replace استفاده کنم‌.
- حسنا خاکزاد ۲۸ اردیبهشت ۱۴۰۲ / ۱۱:۲۱ ق٫ظ
  
  پاسخ
  
  درود
  فیلتر کنید
  برای فیلتر هم فیلتر معمولی نهایتا تا ۲ شرط میپذیره
  برید سراغ advance filter و a,b,c رو فیلت رکنید و بعد انتخاب کرده و تایپ کنید d و ctrl+Enter
  advance filter هم مقاله داریم براش داخل سایت https://excelpedia.net/advanced-filter/
محمد نادری زرگاه ۲۷ اردیبهشت ۱۴۰۲ / ۲:۴۳ ب٫ظ

پاسخ

با سلام
ممنون از مقالات مفید و کاربری که در سایت بارگذاری میکنید.
در صورت امکان ماکرو ذخیره هر شیت به صورت اکسل جدا گانه
- حسنا خاکزاد ۲۷ اردیبهشت ۱۴۰۲ / ۵:۴۳ ب٫ظ
  
  پاسخ
  
  درود بر شما، ممنون از همراهیتون
  
  چشم این هم اضافه خواهد شد

۱۴ ماکروی آماده و قابل استفاده

مراحل اضافه کردن کد مورد نظر به یک فایل اکسل

ذخیره کردن فایل

نحوه اجرای یک ماکرو

ماکروهای آماده قابل استفاده

۱. Unhide کردن همه شیت ها با یک حرکت

۲. Hide کردن همه شیت ها به جز شیت فعال

۳. مرتب کردن شیت ها به ترتیب حروف الفبا

۴. Protect کردن همه شیت ها با یک حرکت

۵. Unprotect کردن همه شیت ها با یک حرکت

۶. ذخیره کردن فایل اکسل به همراه زمان و تاریخ سیو کردن در نام

۷. ذخیره هر شیت بصورت یک PDF جدا

۸. ایجاد یک ردیف خالی بین ردیف های محدوده انتخابی

۹. ثبت تاریخ و زمان در یک سل خاص

۱۰. تغییر اندازه حروف داده های موجود در یک ستون

۱۱. ذخیره هر شیت بصورت یک فایل اکسل جدا

۱۲. انتخاب بیش از یک آیتم در لیست دیتاولیدیشن

۱۳. Bold کردن بخشی از یک سلول

۱۴. اضافه کردن ردیف خالی به تعداد مشخص

دانلود فایل اکسل ماکروهای نمونه

حسنا خاکزاد 144