Rubayed Razib Suprov

Thanks for your question in our platform.
You may opt for the first method mentioned here to prevent Excel sheet content to another workbook completely.
Or you can read the article(https://www.exceldemy.com/protect-excel-sheet-from-copy-paste/#1_Use_Info_Option_to_Protect_Excel_Sheet_from_Copy-Paste) to prevent the workbook content from being copied to another workbook. You can ignore the VBA coding method if you want.

The RAND function is supposed to return only a random value, which is used for shuffling the dataset values. In this case, we are trying to swap cell values within two separate cells. This means your statement is not applicable. If you insist that it is possible, please write down the process or send us your Excel file through email. Thank you.

You an find the solution in the below comment of yours. I have provided a reply with a code and explanation image.

Greetings Chalon,
Below I am going to provide a code using which you will be able to move only the rows that have value in the corresponding cells in a specific column. The sheet name here termed as “Destination” and the sheet from where we want to move the cell are named as “Source”. After putting the code in the code editor, press the Run command buton.

Sub MoveRowsBasedOnValue()
    Dim sourceSheet As Worksheet
    Dim destinationSheet As Worksheet
    Dim sourceRange As Range
    Dim cell As Range
    Dim destinationRow As Long
    Set sourceSheet = ThisWorkbook.Worksheets("Move Row") ' Replace "Sheet1" with your actual sheet name
    Set destinationSheet = ThisWorkbook.Worksheets("Destination") ' Replace "Sheet2" with your actual sheet name
    Set sourceRange = sourceSheet.Range("D5:D10") ' Replace "D5:D10" with your actual range
    destinationRow = 1
    For Each cell In sourceRange
        If Not IsEmpty(cell) Then
            sourceSheet.Rows(cell.Row).Copy Destination:=destinationSheet.Rows(destinationRow)
            destinationRow = destinationRow + 1
        End If
    Next cell
    Application.CutCopyMode = False
    destinationSheet.UsedRange.Columns.AutoFit
    MsgBox "Rows moved successfully!"
End Sub

After running the code,we will see that the rows corresponding to the cell values are now mooved to the destination sheet.

Greetings Prasanth,
Thanks a lot for the question you asked. Below I provide a code using which you can add rows in another worksheet. When you run the code, then you will see that the values stored in the C3:D3 insert in another worksheet. The values are now repeated according to the value mentioned in the cell E3

Sub InsertRows()
Dim Qty As Integer
Dim sourceSheet As Worksheet
Dim destinationSheet As Worksheet
Dim lastRow As Long
Dim pasteRange As Range

Set sourceSheet = ThisWorkbook.Worksheets("Sheet1") ' Replace "Sheet1" with the actual name of the source sheet
Set destinationSheet = ThisWorkbook.Worksheets("Sheet2") ' Replace "Sheet2" with the actual name of the destination sheet

Qty = sourceSheet.Range("E3").Value

lastRow = destinationSheet.Cells(destinationSheet.Rows.Count, "B").End(xlUp).Row

Set pasteRange = destinationSheet.Cells(lastRow + 1, "B").Resize(Qty, 2)

pasteRange.Insert Shift:=xlDown

destinationSheet.Cells(lastRow + 1, "B").Resize(Qty, 2).Value = sourceSheet.Range("C3:D3").Value

Application.CutCopyMode = False
destinationSheet.Select ' Optional: Select the destination sheet after inserting rows
End Sub

Greetings Prasanth,
Thanks a lot for your question. You can follow the modified code given below to insert rows to another sheet.Here the input tanges are in the Sheet1 and the Output ranges where the rows are going to be inserted Sheet2.

Sub InsertRows()
    Dim Qty As Integer
    Dim sourceSheet As Worksheet
    Dim destinationSheet As Worksheet
    Dim lastRow As Long
    Dim pasteRange As Range
    
    Set sourceSheet = ThisWorkbook.Worksheets("Sheet1") ' Replace "Sheet1" with the actual name of the source sheet
    Set destinationSheet = ThisWorkbook.Worksheets("Sheet2") ' Replace "Sheet2" with the actual name of the destination sheet
    
    Qty = sourceSheet.Range("E3").Value
    
    lastRow = destinationSheet.Cells(destinationSheet.Rows.Count, "B").End(xlUp).Row
    
    Set pasteRange = destinationSheet.Cells(lastRow + 1, "B").Resize(Qty, 2)
    
    pasteRange.Insert Shift:=xlDown
    
    destinationSheet.Cells(lastRow + 1, "B").Resize(Qty, 2).Value = sourceSheet.Range("C3:D3").Value
    
    Application.CutCopyMode = False
    destinationSheet.Select ' Optional: Select the destination sheet after inserting rows
End Sub

Greetings Beck,
Thanks a lot for your question. I am not entirely sure if your question is pertinent to topic of the this article, or if it is just a standalone question. I am giving you a response treating the question as a standalone question.
To have different values in the cell which is 10 digits long and starts with 430, paste the below code in the code editor, and then press Run.

Sub generate_numbers()
    Dim i As Long
    Dim myrange As Range
    Set myrange = Range("B3:B12")
    For i = 1 To myrange.Cells.Count
        myrange.Cells(i) = "430" & Format(i, "0000000000")
    Next i
End Sub

After pressing Run, you will notice that the code now put 10 distinct 10-digit values in the worksheet starting with 430.
Hope this helps, if you have any other question or suggestions,please do not hesitate to comment on this post.

Thanks a lot for posting your query in Exceldemy
Below I am giving you a VBA code using which you can open the outlook template saved on your pc and then replace them with desired text from the Excel sheet.

Sub EmailClient()
    Dim OutApp As Object
    Dim OutMail As Object
    Dim strbody As String
    Dim clientName As String
    
    ' Get the client's email address from the selected cell
    Dim email As String
    email = Selection.Value
    
    ' Get the client's name from the first column of the selected row
    clientName = Cells(Selection.Row, 1).Value
    
    ' Create the email body using your Outlook template
    ' Replace "TemplateName" with the name of your Outlook template
    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItemFromTemplate("C:\Users\user\Documents\OutlookTemplate.oft")
    
    ' Replace "Client Name" with the name of your client field in the template
    strbody = Replace(OutMail.HTMLBody, "Name", clientName)
    OutMail.HTMLBody = strbody
    
    ' Set the recipient email address
    OutMail.To = email
    
    ' Display the email and let the user edit it before sending
    OutMail.Display
    
    ' Clean up
    Set OutMail = Nothing
    Set OutApp = Nothing
End Sub

the sample template file that we are using is given below.
The Name, in the beginning, is going to be replaced by the desired name and the recipient will be the selected mail address.

In the code, two separate things must be taken seriously, firstly the location of the template file. The location of the template mail has to be specified correctly in the code(Marked as 1), as shown in the image. So please rewrite the location of the file correctly.
secondly, f you need to specify which term to replace with names inside the code(Marked as 2).

the location of the template file in your pc can be found in the properties tab as shown below.

finally, you are ready to execute the code.For this, you have to first select the email address to which you want to send the email. Then Run the Macro by pressing the Macro button placed just right side of the information.

Then you can see that the a new email has been composed with the name placed in the beginning with the selected mail address as the recipient.

You can download the sample macro file from the links below
Sample Macro File: https://www.exceldemy.com/wp-content/uploads/2023/02/Send-Automatic-Email-outlook-template.xlsm\
In case you need to template file, please contact us through email.
Regards,
Rubayed Razib

Thanks a lot for your response John,
Here there might be some other issues underlying your file that is causing issue for you. It would be better if your share your file with us and we can have a look into it.

Greetings TRISH,

In your case, it is difficult to ans this problem without having a look at your worksheet . Please send the worksheet to our problem solving, hence we can assist you on this issue.

Greetings Rob,
Thanks a lot for commenting on this article. Unfortunately, we managed to find only the last three companies and the INVESTEC CORE FUND IWCF among the companies you metioned in the database. Rest of them are not enlisted in the database. I have given the dataset link containing the stock informations of the Satrix RESI ETF SATRIXRESI, Coronation Global Capital Plus [ZAR] Feeder CPLSZ, Ninety One Equity Fund NINEQ, INVESTEC CORE FUND IWCF etc.

Thanks and Regards
Rubayed Razib

Dataset Link:https://www.exceldemy.com/wp-content/uploads/2023/01/Get-Stock-Prices-in-Excel.xlsx

Hello Amghar,
Thanks a lot for submitting Excel related problem at Exceldemy. Here you can use the VBA code mentioned below.

Sub Detect_font_color()
If Worksheets("Sheet1").Range("D6").Font.ColorIndex = 6 Then
Range("D6").RowHeight = 20
End If
End Sub

In that code, you need to mention the cell address inside the VBA code range property(as shown in the image). After this, if the condition satisfied, the cell row height will adjust accordingly.

Thanks a lot for your compliments, Raul. Glad to hear that the article came in handy for you

Thanks for your question. Here Seattle has a rating of -0.50409…And St Louis Rams has -4.439784. if you subtract -0.50409 from the -4.439784, then you will get 4.943874.which is approximately around 4. So the statement that “Seattle has 4 points better than St Louis”

Thanks a lot for your question. In the screenshot, all of the P values are actually below 0.05. One value is 0.002384973 and another one is 1.4769*10^-10. Maybe you got a little confused as the letter E is hard to notice. Hope this helps.

Thanks a lot for your question, Aaron. Here we are going to use a separate formula for highlighting the entire row based on whether any of the values in the row are blank or not. Notice the image below, we got blank cell in C8,C11, and C14

2. First, go to the Conditional Formatting, and set the rules as shown below.

3. Then select only the B4:D4, and copy the formatting style of this cell.

4. Paste the formatting style all over the range of B4:D15.

And now you can see that the rows now highlighted if there is any blank cell in the entire row.

Thanks for your question James, I am not quite sure in which step you get the first error. You can better opt for an alternative approach.
1. The procedure above should be followed until the first extension change. After the extension is changed to .zip, use the 7-zip application to open the archive, not to extract the zip.

2. Then inside the archive, go to xl>worksheet. and double click on the sheet1.xml to edit the text as mentioned in the post.

3. you can use a regular notepad application for opening the XML file. Or you can also opt for an XML notepad(https://microsoft.github.io/XmlNotepad/##_top) instead of a regular notepad. using the XML notepad, you can delete the protection part directly as a folder, as shown in the image.

4. After deleting the protection part, save the XML file.
5. And then change the extension part back to xls.
6. You will see that the Excel file is now unprotected.
Last but not the least, try to use Microsoft Office 365 instead of regular version of Excel.That way you will always be ensured with the latest updates.