Bhubon Costa, B.Sc. in Naval Architecture & Marine Engineering from Bangladesh University of Engineering & Technology, has worked with the ExcelDemy since 2021. Currently, he has been working as a reviewer. Notably, he has written over 90 articles and led several VBA content development teams. He has a great passion for the fields of data analytics and data science. His areas of expertise include Excel VBA, Power Query, Pivot Table, Power BI, MySQL, PostgreSQL, machine learning, and Python automated dashboards.
We'll use the following sample dataset to protect the sheet except for select cells. Method 1 - Use the Protect Ribbon to Protect an Excel Sheet Except ...
Method 1 - Use Excel’s General Options to Auto Backup an Excel File Step 1: Create an Auto Backup File Click the File tab. Select Save As. ...
This is an overview. Method 1 - Using an Excel Formula to Calculate Time Difference in Minutes Step 1: Find the Time Difference in Dates Enter ...
The following database showcases sample fields. Step 1 - Insert Basic Details to Create an Employee Database Enter each employee's name. ...
Download the Practice Workbook Transpose Rows to Columns with Criteria.xlsm 2 Handy Approaches to Transpose Rows to Columns Based on Criteria in ...
In the following image, there is a CSV file. To convert it into an Excel file (.xlsx) Step 1 - Save the CSV File Save your CSV file (source) in a ...
This is an overview: Step 1 - Create a PDF Form Open Adobe Acrobat PDF Reader. Click Tools. In the search box, enter ‘Prepare’ to the Prepare ...
We've included a sample data set with a workbook named Subtotal Pivot Table in the image below. This worksheet's data will be imported into another workbook. ...
Method 1 - Insert FIND Function find_text Argument Find the first space position number; type the find_text argument. =FIND(" ", Method ...
The dataset showcases the sales summary of 2 sequential periods. Step 1- Calculate the Percentage of Win-Loss for Each Entry in Excel To calculate ...
In the following dataset, we will count the Total Sales from 2015 to 2020 for different months. However, we have missed including the Sales for 2016. So, we ...
Dataset Overview We've included a sample dataset in the figure below, from which we'll sift through values according to specific cell criteria. Using Excel ...
This guide will use the sample dataset pictured below. Method 1 – Merging MID Function With FIND Function to Extract Text After the First Space in ...
We will demonstrate 5 ways to create variable rows and columns. We'll use the following sample dataset with product prices, quantities, and sales. ...
Method 1 - Run a VBA Code to Center Text Horizontally and Format Cell with Excel VBA 1.1 Center an Active Cell Horizontally In the image below; there is an ...
Greetings EDUARDO,
Thanks for reaching out again. We are not facing the same issue from our side. But we are providing you with a possible solution to get rid of the issue. There could be problems with versions or with settings. We are using Microsoft 365.
1. First, check that the Automatic option is enabled.
2. Check that the Advanced tab options are the same as yours.
3. Now, if you still face the issue, you can change the Copy Cells command to Fill Series in the AutoFill options.
Please give us feedback if you have any further queries.
Best Regards,
Bhubon Costa, ExcelDemy
Greetings EDUARDO,
Yes, you have followed the procedures perfectly. But the thing is that you don’t need to press Ctrl to increase the number, as the numbers are already in an arithmetic progression. So, when you press Ctrl and drag down, you experience something like this:
So, just drag down the AutoFill tool.
Please give us feedback if you have any further queries.
Best Regards,
Bhubon Costa, ExcelDemy
Greetings Andrija,
There might be two reasons for your query. First, your default Outlook mail compose settings may not be in HTML format. Another one is that the table is pasted as Paste text and merge format (M).
First solution:
Go to the View tab in your Outlook app.
Click on View Settings > Mail > Compose and reply.
Select the HTML format for the Compose messages option.
Second Solution:
Your table might be compressed if you paste it as Paste text and merge format (M).
Change it to Paste as is (P).
We hope your issue will be resolved. Please let us know whether your issue is solved or not. The ExcelDemy team always welcomes your valuable feedback.
Best regards,
Bhubon Costa
Greetings Frank,
From your question, we can assume that your time format is formatted in [h] “hr”, mm “m”, ss “s” format. We need to change it into h:mm:ss format. You need to customize this format with the following steps.
Best regards,
Bhubon Costa, ExcelDemy
Greetings UNSOLVABLE,
We have just rechecked our code and it is working. You might have any compatibility issues for not working this code. You may download our sample Excel file and try to apply the code on that.
Alternatively, you can share your Excel file on our forum https://exceldemy.com/forum/. We will solve your issues within no time.
Best Regards,
Bhubon Costa, Exceldemy
Dear Nouran,
I am delighted to hear that your previous queries have been resolved. It seems like you have invested a lot of patience and effort, considering you had to work with 18 variables to accomplish your task!
Regarding your further queries, you want to send reminder emails multiple times with different time intervals. For instance, you want to send the first mail three days prior to the deadline, the second mail five days prior to the deadline, and the final mail seven days before the deadline. To accomplish this, we have utilized the following dataset, and as of today, it is the 8th of May, 2023.
Now, you have to modify the If section of the codes as in the image below.
Here, is the full updated code.
I hope that this response has resolved your current queries. If you would like to automate the program, please refer to our previous replies for detailed instructions. Additionally, please inform us if you have any further questions.
Regards,
ExcelDemy Team
Dear Nouran,
I am delighted to hear that your previous queries have been resolved. It seems like you have invested a lot of patience and effort, considering you had to work with 18 variables to accomplish your task!
Regarding your further queries, you want to send reminder emails multiple times with different time intervals. For instance, you want to send the first mail three days prior to the deadline, the second mail five days prior to the deadline, and the final mail seven days before the deadline. To accomplish this, we have utilized the following dataset, and as of today, it is the 8th of May, 2023.
Now, you have to modify the If section of the codes as in the image below.
Here, is the full updated code.
I hope that this response has resolved your current queries. If you would like to automate the program, please refer to our previous replies for detailed instructions. Additionally, please inform us if you have any further questions.
Regards,
ExcelDemy Team
Yes, you can still run the program when your Excel App is closed. You do not need to run the program every day manually. By doing so, whenever you open your PC your program will be executed automatically. Follow the steps below to do so.
Step 1:
1. From the Start Menu type Run to open the Windows Run dialog box and paste the following path:
%AppData%\Microsoft\Windows\Start Menu\Programs\Startup
2. Click OK.
Step 2: Place your Excel File in the Startup folder.
Step 3:
1. Open your Excel file Macro Window.
2. In the Project Explorer, double-click the ThisWorkbook object to open the code window for the workbook.
3. In the code window, select Workbook from the drop-down list at the top.
4. Then, select the Workbook_Open event from the drop-down list on the right.
Step 4:
1. Type or paste the VBA code that you want to run when the file is opened between the Private Sub Workbook_Open() and End Sub lines.
2. Save the Excel file.
Now, whenever you open your PC, the VBA code you wrote will be automatically executed. Hope this will resolve your issue. Please let us know your further feedback, our ExcelDemy Team is always relentless to welcome your issues.
Regards,
ExcelDemy Team
Answer 1: Let’s say you want to add one more column for the mail content portion. So, we have assigned VBA codes with one more variable xMailContent2
Copy the codes below and apply them to your Module.
See the output as images below:
Answer 2: Unfortunately, you cannot run the program without opening the Excel App. You may get this done by applying API. However, you can try the below process but it has some troubleshooting issues. Please let us know if you get solved your issue.
On the other hand, you can automatically run when you open the Excel file, you do not need to manually run the code. Simply, apply the VBA codes inside the Private Sub event.
You do not need to run the program every day manually. You can automatically run the program whenever you open your PC. Follow the steps below to do so.
Step 1:
1. From the Start Menu type Run to open the Windows Run dialog box and paste the following path:
%AppData%\Microsoft\Windows\Start Menu\Programs\Startup
2. Click OK.
Step 2: Place your Excel File in the Startup folder.
Step 3:
1. Open your Excel file Macro Window.
2. In the Project Explorer, double-click the ThisWorkbook object to open the code window for the workbook.
3. In the code window, select Workbook from the drop-down list at the top.
4. Then, select the Workbook_Open event from the drop-down list on the right.
Step 4:
1. Type or paste the VBA code that you want to run when the file is opened between the Private Sub Workbook_Open() and End Sub lines.
2. Save the Excel file.
Now, whenever you open your PC, the VBA code you wrote will be automatically executed. Hope this will resolve your issue. Please let us know your further feedback, our ExcelDemy Team is always relentless to welcome your issues.
Regards,
ExcelDemy Team
Yeah, YUKI, you are right. Your workbook need to be opened all the time to continue running this program. But, Problem arises when you turn off your pc the program will not run. So, you need to reopen the Excel file and run the program on daily basis.
However, you can just open the Excel App. from your PC. Then it will run automatically. To do so, please insert your whole code in between Private Sub as below:
So, whenever you open the Excel App. the program will continue to run.
On the other hand, if you want to run the program just after whenever you open PC. We need some time to make it for you. You may share your Excel file with us in [email protected]. Our dedicated ExcelDemy Team is trying to resolve your issue.
Regards,
ExcelDemy Team
Answer 1: To add one or more columns for the mail content section, you must add variables. Let’s say you want to add one more column so we will add VBA codes with one more variable xMailContent2. So, your data set may look like the following image, one column with the task name, and another with the task details.
So, apply the following VBA codes to perform your requirements.
Answer 2: You cannot run a file automatically from a Team Chanel server. However, you can automatically run the program whenever you open your PC. Follow the steps below to do so.
Step 1:
1. From the Start Menu type Run to open the Windows Run dialog box and paste the following path:
%AppData%\Microsoft\Windows\Start Menu\Programs\Startup
2. Click OK.
Step 2: Place your Excel File in the Startup folder.
Step 3:
1. Open your Excel file Macro Window.
2. In the Project Explorer, double-click the ThisWorkbook object to open the code window for the workbook.
3. In the code window, select Workbook from the drop-down list at the top.
4. Then, select the Workbook_Open event from the drop-down list on the right.
Step 4:
1. Type or paste the VBA code that you want to run when the file is opened between the Private Sub Workbook_Open() and End Sub lines.
2. Save the Excel file.
Now, whenever you open your PC, the VBA code you wrote will be automatically executed. Hope this will resolve your issue. Please let us know your further feedback, our ExcelDemy Team is always relentless to welcome your issues.
Regards,
ExcelDemy Team
Greeting Yuki,
Thank you for your queries,
This program will have to run just one time and when it will reach the due date it will send reminder emails automatically.
But you can also customize it to send it manually. Please go through the “Notes” section in Step 3 of the article.
You can stop sending emails automatically just by disabling the .Send command from the full code. It will pops up for a confirmation before sending. Moreover, you can customize the recipients names. That’s the elegance of our objective!
However, if this doesn’t solve your queries, you can mail us your Excel file with detailed instructions to: [email protected], and we’ll try to solve it as soon as possible.
Regards,
ExcelDemy Team
Greetings 3ADDOULA MA5ASSAK, thank you for your question. I hope the following codes will solve your issue.
However, if this doesn’t solve your problem, you can mail us your Excel file with detailed instructions to: [email protected], and we’ll try to solve it as soon as possible.
Greetings INSTASAVETUBE,
Countless thanks for your kind words of praise. The Exceldemy team has always been there to assist and has been working to make knowledge accessible to everyone globally. And without a doubt, you are the kind of great people that empower us.
Best Regards,
Bhubon Costa
(Exceldemy Team)
Greetings Lalit,
Pleased to hear that your previous query has been resolved.
We have created an Excel file for you to add different subjects. Just download the file and run the code of Module 1.
https://www.exceldemy.com/wp-content/uploads/2022/08/Send_Email_Reminder_Lalit.xlsm
1. You will get an input box to select the subject column as shown in the image below.
2. Therefore, you will see the emails on display before sending them, with the selected different subjects.
Please feel free to ask more queries. Our ExcelDemy Team will reach you as soon as possible.
Greetings Lalit,
You just have to add two lines in between the xMailSections portion:
Steps:
1. Add the file path of your attachment with the attachment name.
2. Apply add attachment command to add the attachment.
Please, see the image below for a detailed understanding.
After running the code, you will see results with the attachment available.
Moreover, we have a dedicated article based on your requirement. Please, go through the article to learn in detail.
https://www.exceldemy.com/macro-to-send-email-from-excel-with-attachment/
Hope, this will meet your requirement. Please, give us feedback if any furthur query needed. The ExcelDemy team is always in your assistance.
Greetings Emma,
Let’s us know some info at first.
1. Can you access the other methods to paste values only?
2. Can you replace data in a non-blank cell?
We have gone through every possible solution but couldn’t match the issue exactly. If you kindly share your excel file with us, we will send it back with the solution.
Email: [email protected]
Our dedicated ExcelDemy Team is always there to help.
Greetings DANA,
To update the result after each entry you make, you just need to edit a one-line VBA code. It will update the table range each time you make a new entry in column B.
The VBA Code:
Now, make new entries and run the program to update the result.
Hope this will work for you. Please give us feedback if you have any further queries.
Thanks a lot for your appreciation. You can also put your problems here, the ExcelDemy team is always in your assistance.
Thank you MOE, for your generous appreciation!
Greetings Justin,
#VALUE error occurs when the function doesn’t find any recognized source value. It might have occurred if the degree values were not in degree formats. Follow the steps below to convert the value into degree format first and then apply the function Convert_to_Decimal with our VBA.
Step 1:
Add a degree (°) symbol after a number with a keyboard shortcut.
(Example: 32°).
Step 2:
Add a minute (‘) symbol after the minute’s value with the apostrophe (‘) key from the keyboard (Example: 16’).
Step 3:
Add a second (“) symbol after the second’s value by pressing
(Example: 48″)
Step 4:
Your degree format will show like (32°16’48”).
Finally, apply the Convert_to_Decimal function to the degree value.
Hope this will work with your issue. You can also mail us the Excel file. We will provide the Excel file with the required solution. Please give us your further feedback.
Greetings David,
Thanks for your valuable suggestion.
Greetings Nishant,
To get emails one day prior the deadline date, try the following codes below.
We just edited the code in the If condition by replacing 1 with 0.
Previous code:
Modified Code:
Please, give us feedback, if any further query needed. The ExcelDemy team is always in your assistance.
Greetings,
According to your query, the ExcelDemy team has created an Excel file with the solution. Please provide your email address here, we will send it to you in no time.
Otherwise, you can just follow the procedures as we have proceeded.
Step 1:
a. Our data set range is B4:F8.
b. The names of the fruits range is C4:F4.
c. Names of people range is B5:B8.
Step 2:
a. In cell C11, create a drop-down list with the range C4:F4.
b. Give a named range for the numbers of each fruit with its name (Ex: Named range = Mango for C5:C8).
c. Create another dropdown list dependant to the cell C11. Use the following formula in the Data Validation box from the Data tab to do this.
Step 3:
a. In cell C15, insert the following formula for Mango.
b. AutoFill the formula with dragging right for three cells for more three fruits.
Step 4:
a. Now, you are done, select any name (Grapes) from the first drop-down list.
b. Then, select the number (6).
c. It will result in the person name (Kent).
Please, provide your further feedback if any queries needed.
Greeting AIN,
Let’s say you want to add another column containing the CC email addresses. You can just copy the codes from here. Moreover, if you want to get the Excel file containing the codes, just give your email address.
Hope this will work with your requirements.
Please, keep giving your feedbacks. The ExcelDemy team is always concerned about your queries.
‘Thank you Dan for your comment. Let’s just your first row is 2 and last row is 7.
‘First condition: G = “BID”, H <= 10000.00, I = "Orders, Web" OR ="cXML Orders"
‘Second condition: G =”MO”, H <10000.00
‘Last Condition: G <10000.00, Apply Color
‘Hope this answer your query.
First of all, take cordial greetings from the ExcelDemy team. There could be some possible reasons due to the issue:
The inverted commas are not properly inserted in your code as they should be in VBA script.
The Sheet Names might not be inserted in VBA exactly as they are in the Excel file.
However, we have created an Excel file precisely in accordance with your requirements. We can send the Excel file if you provide your email address.
Otherwise, you can copy the codes below that we have used to make the Excel file with the right syntax. Please continue to provide us with feedback; ExcelDemy always welcomes your concerns.
Sub MoveRowBasedOnCellValue()
‘Declare Variables
Dim MAIN As Worksheet
Dim Project As Worksheet
Dim Claims As Worksheet
Dim TaM As Worksheet
Dim QUOTED As Worksheet
Dim PM As Worksheet
Dim FinalRow As Long
Dim Cell As Range
‘Set Variables
Set MAIN = Sheets(“Dataset2”)
Set Claims = Sheets(“CLAIMS”)
Set Project = Sheets(“Project”)
Set TaM = Sheets(“TaM”)
Set PM = Sheets(“PM”)
Set QUOTED = Sheets(“QUOTED”)
‘Type a Command to select the entire row
Selection.EntireRow.Select
‘Define destination sheets to move row
FinalRow1 = MAIN.Range(“A” & MAIN.Rows.Count).End(xlUp).Row
FinalRow2 = Claims.Range(“A” & Claims.Rows.Count).End(xlUp).Row
FinalRow3 = Project.Range(“A” & Project.Rows.Count).End(xlUp).Row
FinalRow4 = TaM.Range(“A” & TaM.Rows.Count).End(xlUp).Row
FinalRow5 = QUOTED.Range(“A” & QUOTED.Rows.Count).End(xlUp).Row
FinalRow6 = PM.Range(“A” & PM.Rows.Count).End(xlUp).Row
With MAIN
‘Apply loop for column F until last cell with value
For Each Cell In .Range(“F1:F” & .Cells(.Rows.Count, “F”).End(xlUp).Row)
‘Apply condition to match the “CLAIMS” value
If Cell.Value = “CLAIMS” Then
‘Command to Copy and move to a destination Sheet “CLAIMS”
.Rows(Cell.Row).Copy Destination:=Claims.Rows(FinalRow2 + 1)
FinalRow2 = FinalRow2 + 1
‘Apply condition to match the “PROJECT” value
ElseIf Cell.Value = “EQUIPMENT PROJECT” Then
‘Command to Copy and move to a destination Sheet “PROJECT”
.Rows(Cell.Row).Copy Destination:=Project.Rows(FinalRow3 + 1)
FinalRow3 = FinalRow3 + 1
‘Apply condition to match the “PROJECT” value
ElseIf Cell.Value = “CONTRACTING PROJECT” Then
‘Command to Copy and move to a destination Sheet “PROJECT”
.Rows(Cell.Row).Copy Destination:=Project.Rows(FinalRow3 + 1)
FinalRow3 = FinalRow3 + 1
‘Apply condition to match the “TaM” value
ElseIf Cell.Value = “T&M” Then
‘Command to Copy and move to a destination Sheet “TaM”
.Rows(Cell.Row).Copy Destination:=TaM.Rows(FinalRow4 + 1)
FinalRow4 = FinalRow4 + 1
‘Apply condition to match the “Quoted” value
ElseIf Cell.Value = “QUOTED” Then
‘Command to Copy and move to a destination Sheet “QUOTED”
.Rows(Cell.Row).Copy Destination:=QUOTED.Rows(FinalRow5 + 1)
FinalRow5 = FinalRow5 + 1
‘Apply condition to match the “SVC AGR” value
ElseIf Cell.Value = “SVC AGR” Then
‘Command to Copy and move to a destination Sheet “PM”
.Rows(Cell.Row).Copy Destination:=PM.Rows(FinalRow6 + 1)
FinalRow6 = FinalRow6 + 1
End If
Next Cell
End With
End Sub