Alok Paul

Dear CC,
Thank you very much for reading our articles.
You have requested an updated VBA code to retain borders as well. Please try the provided VBA code below; it preserves borders.

Sub RemoveConditionalFormattingButKeepFormat()
    ' Loop through each cell in the selected range
    For Each cell In Selection
        ' With block to refer to the current cell
        With cell
            ' Copy font color from the display format to the cell
            .Font.Color = .DisplayFormat.Font.Color

            ' Copy border settings from the display format to the cell
            .Borders.LineStyle = .DisplayFormat.Borders.LineStyle

            ' Copy interior settings from the display format to the cell
            .Interior.Pattern = .DisplayFormat.Interior.Pattern

            ' If the interior pattern is not xlNone (no pattern), copy additional interior settings
            If .Interior.Pattern <> xlNone Then
                .Interior.PatternColorIndex = .DisplayFormat.Interior.PatternColorIndex
                .Interior.Color = .DisplayFormat.Interior.Color
            End If

            ' Copy TintAndShade settings from the display format to the cell
            .Interior.TintAndShade = .DisplayFormat.Interior.TintAndShade

            ' Copy PatternTintAndShade settings from the display format to the cell
            .Interior.PatternTintAndShade = .DisplayFormat.Interior.PatternTintAndShade
        End With
    Next

    ' Delete all format conditions from the selected range
    Selection.FormatConditions.Delete
End Sub

Best Regards,
Alok
Team ExcelDemy

Hi JAD,
I hope you are doing well. Thank you very much for reading our articles.
You want to apply conditional formatting to the minimum value in each row across multiple rows. For that, you need to use a formula in the conditional formatting. Follow the instructions given below:

1. Select the data range.
2. Click on the Conditional Formatting drop-down.
3. Select New Rule to appear Edit Formatting Rule window.
   
4. In the Edit Formatting Rule window:
   • • Go to Select a Rule Type > Use a formula to determine which cells to format.
   • • Insert the following formula into the “Format values where this formula is true” box: =C5=MIN($C5:$E5)
   • • Set a color to highlight using the Format button.
   • • Finally, press OK.

   

You can see minimum values of each row have been highlighted.

Regards,
Alok Paul
ExcelDemy Team

Dear Tom,
Thanks for reading our articles. Here, you queried that you want an Excel sheet based on VBA to input values for 4 users in separate specified range. Those cells are locked with different passwords for each user.
Here, we have set User 1 with range B5:C10 and password: passuser1, User 2 with range E5:F10 and password: passuser2, User 3 with range H5:I10 and password: passuser3, User 4 with range K5:L10 and password: passuser4.

Based on the above information our VBA code will be as follow.

Sub Protect_Multiple_Range_with_Password_for_Specific_User()
Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets("Sheet1") 'change accordingly

Dim i As Integer
For i = ws.Protection.AllowEditRanges.Count To 1 Step -1
    ws.Protection.AllowEditRanges(i).Delete
Next i

' Define the ranges and passwords
    rangeAddresses = Array("B5:C10", "E5:F10", "H5:I10", "K5:L10") ' Add or modify ranges as needed
    passwords = Array("passuser1", "passuser2", "passuser3", "passuser4") ' Add or modify passwords as needed

'Unlocking Sheet if already locked
If ws.ProtectContents = True Then
ws.Unprotect Password:=1234 'change accordingly
End If

    For i = LBound(rangeAddresses) To UBound(rangeAddresses)
        ws.Protection.AllowEditRanges.Add Title:="Range_" & CStr(i + 1), Range:=ws.Range(rangeAddresses(i)), Password:=passwords(i)
        ws.Range(rangeAddresses(i)).Locked = True
    Next i
 MsgBox "Multiple ranges have been set for different users with different passwords using AllowEditRanges feature.", vbInformation
 
 'again Locking the sheet with master password 1234
ws.Protect Password:=1234, DrawingObjects:=True, Contents:=True, Scenarios:=True

End Sub

• When you double-click on any cell specified for any user a window named Unlock Range will pop-up. Insert the password and click on OK to edit the range of the user.

• But if you click on any cell that is not associated with any user then you will get a warning.

We can also unprotect the whole using password: 1234.

Regards
Alok

Hi D V V NARAYANARAJU,
Thank you very much for reading our articles.
In your query, you wanted to know how to return the second highest value which is 12 located at row 2 using the formula.
You can use the following formula:
=LARGE($B$5:$B$13,2)

We have inserted the values in the range B5:B13.
In return, we get 12 which indicates cell B6 which is the 2nd row of the range.
If you have further queries regarding this topic, then inform us.

Regards
Alok
ExcelDemy

Hi NARASIMHAN S,
You can copy data as values from the selected worksheets to another workbook using the VBA code. Here, Workbook1 is our source and Workbook2 is our destination Excel file. We want to copy data from Sheet1 of Workbook1. Use the below code on Workbook1.

VBA Macro to Copy Data of a Sheet into Another Workbook:

Sub Copy_Sheet_Another_Workbook_1()
Sheets(“Sheet1”).Copy Before:=Workbooks(“Workbook2.xlsm”).Sheets(1)
End Sub

Code Breakdown

Sheets(“Sheet1”).Copy Before:=Workbooks(“Workbook2.xlsm”).Sheets(1)
This will copy Sheet1 of the active workbook (Workbook1) into Workbook2 before 1st sheet. If you want to copy after 1st sheet of Workbook2, use After instead of Before in the VBA code.

One thing needs to keep in mind, that is both workbooks must be kept open in this case. The destination workbook name is mentioned in the VBA code. But if the source workbook is closed, you can use the VBA code below.

VBA Macro to Copy Data of a Closed Sheet into Another Workbook:

Sub Copy_Sheet_Another_Workbook_2()
Application.ScreenUpdating = False
Set sourcedBook = Workbooks.Open(“C:\Users\alokp\OneDrive\Desktop\Workbook1.xlsm”)
sourcedBook.Sheets(“Sheet1”).Copy Before:=ThisWorkbook.Sheets(1)
sourcedBook.Close SaveChanges:=False
Application.ScreenUpdating = True
End Sub

We applied this VBA code to Workbook2 and Workbook1 is closed. We inserted the location of Workbook1 and mentioned the sheet name in the VBA code. In both cases, we need to mention the source sheet name in the VBA code.

Dear CHARLES DEL MAR,
Thank you very much for reading our articles. You want to add the page number in the cell following the method Inserting Page Number in Excel Cell. You mentioned that every time you run the code and get “Page 1 of 9”, even in the last cell where it is supposed to show “Page 9 of 9”. Based on your complaint, we checked all the steps and run the VBA code again. We get an accurate result. See the image below.

I think you need to check the Status Bar at the bottom of the sheet, where the page number shows. We get the same result showing in the Status Bar after running the VBA code.

Dear PRATIK,
Thanks very much for reading our articles. You have mentioned that you have different lists in a single sheet. Here, we will show how to declare data validation in different cells with different lists in a single sheet.

Steps:
● Here, we have three different lists of mobile, laptop, and routers. Here, we will introduce different drop-down lists with multiple selections.

● Now, apply data validation as shown in the article already.
● We will choose Range B5:B11 at Cell G4, Range C5:C11 at Cell G5, and Range D5:D11 at Cell G6 for the data-validation source.
● Choose any item from the drop-down list of Cell G4.

● Similarly, choose items for Cell G5 and G6 from the drop-down list.

● Now, put the following VBA code in the VBA module. After that, save the VBA code.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Oldvalue As String
Dim Newvalue As String
On Error GoTo Exitsub
If Target.Column = 7 Then
If Target.SpecialCells(xlCellTypeAllValidation) Is Nothing Then
GoTo Exitsub
Else: If Target.Value = “” Then GoTo Exitsub Else
Application.EnableEvents = False
Newvalue = Target.Value
Application.Undo
Oldvalue = Target.Value
If Oldvalue = “” Then
Target.Value = Newvalue
Else
Target.Value = Oldvalue & “, ” & Newvalue
End If
End If
End If
Application.EnableEvents = True
Exitsub:
Application.EnableEvents = True
End Sub

● Now, choose multiple options from the drop-down list.

Here, we changed the Target.Address to Target.Column and put the column number. Also, need to mention the whole column will save the previous result.

Dear RENEE,
Thanks for following our articles. You mentioned a problem regarding the sheet name. Your sheet name will depend on the value of vcol variable. And one thing the sheets will split depending on the value of the 1st column which is the 1st column of the dataset. And, your provided code is not working due to some mistakes. You have made a mistake by setting the of vcol 7. Set the value of vcol as 1. Furthermore, use the below VBA that will solve your problem correctly.

Sub parse_data()
Dim lr As Long
Dim ws As Worksheet
Dim vcol, i As Integer
Dim icol As Long
Dim myarr As Variant
Dim title As String
Dim titlerow As Integer

‘This macro splits data into multiple worksheets based on the variables on a column found in Excel.
‘An InputBox asks you which columns you’d like to filter by, and it just creates these worksheets.

Application.ScreenUpdating = False
vcol = 1
Set ws = ActiveSheet
lr = ws.Cells(ws.Rows.Count, vcol).End(xlUp).Row
title = “A1”
titlerow = ws.Range(title).Cells(1).Row
icol = ws.Columns.Count
ws.Cells(1, icol) = “Unique”
For i = 2 To lr
On Error Resume Next
If ws.Cells(i, vcol) <> “” And Application.WorksheetFunction.Match(ws.Cells(i, vcol), ws.Columns(icol), 0) = 0 Then
ws.Cells(ws.Rows.Count, icol).End(xlUp).Offset(1) = ws.Cells(i, vcol)
End If
Next

myarr = Application.WorksheetFunction.Transpose(ws.Columns(icol).SpecialCells(xlCellTypeConstants))
ws.Columns(icol).Clear

For i = 2 To UBound(myarr)
ws.Range(title).AutoFilter field:=vcol, Criteria1:=myarr(i) & “”
If Not Evaluate(“=ISREF(‘” & myarr(i) & “‘!1)”) Then
Sheets.Add(after:=Worksheets(Worksheets.Count)).Name = myarr(i) & “”
Else
Sheets(myarr(i) & “”).Move after:=Worksheets(Worksheets.Count)
End If
ws.Range(“A” & titlerow & “:A” & lr).EntireRow.Copy Sheets(myarr(i) & “”).Range(“A1”)
‘Sheets(myarr(i) & “”).Columns.AutoFit
Next

ws.AutoFilterMode = False
ws.Activate
Application.ScreenUpdating = True
End Sub

Hi BHAVNESH,
Thank you very much for reading our article. For different clients, you can create separate sheets using our Excel template. Yes, it is possible to change the interest rate. Also, that will change all the values accordingly. You can see in the below image, we changed the interest rate from 5% to 6.5%. Due to auto-update, all the values changed automatically.

You can also change other input values marked by 1. You can change the yearly insurance and taxes rate if changes. There is also the option to change the extra payment in Excel. You can use the IPMT function to calculate the interest part only. For penalty, please describe the situation or share your wordbook. After that, we try to solve that problem. You can mail us at [email protected].

Dear RAZAN,
Thank you very much for reading our articles. Here, you mentioned that you do not want to get the answer in the form of Yes or No. You want to get the result in numbers. To solve your problem we formed a new formula based on the combination of the SUMPRODUCT, SUBSTITUTE, and LEN functions. Here is the formula:
=SUMPRODUCT((LEN($B$5:$B$19)-LEN(SUBSTITUTE($B$5:$B$19,$D5,"")))/LEN($D5))

This formula will find out the specific word from the Range B5:B19 and return the sum in number.
If you want to get the number of a specific word from each cell you can follow this article.
Excel Formula to Count Specific Words in a Cell (3 Examples)

Hi JORGE,
It’s a bit difficult to answer this, not seeing your spreadsheet. Can you please send a sample to [email protected]?
Prior to that, check the following Excel file. We have added a 12-month example for you. Let us know if this helps.
Download Link:
Solution.xlsx
Note:
You must have data of 2021 and 2022 for forecasting 2023.

Hi JUAN MARTIN OPACAK,
Thanks for reading our articles. We have found a solution to fulfill your requirement. You need to insert the following macros with the existing VBA code.

Worksheets(ActiveSheet.Index + 1).Select
ActiveSheet.Name = (Worksheets(2).Range("A1").Value) & " " & ActiveSheet.Name

Here, Worksheets(2).Range(“A1”).Value defines we want to insert the text of Cell A1 of Worksheet 2 with the current sheet name.
Have a look at the image below, where to insert the mentioned VBA code.

Hello, DANIEL.
Thanks for reading our articles.
Look at the below link. Hopefully, you will get your solution.
https://www.exceldemy.com/extract-data-from-one-sheet-to-another-in-excel-using-vba/

For example, you can use the following code:

Sub Extract_Data()
Selection.Copy
Sheets("Dataset2").Activate
Range("F4").Select
ActiveSheet.Paste
Application.CutCopyMode = False
End Sub
Enter your sheet name instead of Dataset2 in the 3rd line. Change the cell range in the 4th line. Hope you will get desired output. If your problem is yet solved, then let us know.
Regards.
-Alok Paul
Author at ExcelDemy

Hi DAVID,
Thank you very much for your appreciation. Follow our website ExcelDemy for other problems, and hope you will always get the best solutions.

Hi BARNEY! Hope you are doing well. Thanks for your nice compliments. We are happy to know that the readers find our articles useful.
However, the problem you are facing is not quite clear from what you have told us. Are you trying to sum up entries that meet specific criteria? In that case, you have to use the SUMIF function if you have to meet just a single criterion. If you have multiple criteria, then you have to use the SUMIFS function. There are more articles in our blog related to these functions. To explore them, scroll down and click on the function tag names.

If this is not what you are looking for, please let us know more details. You can send me the problem with a sample file at [email protected] or at [email protected].

Best wishes. Keep staying with us.
-Alok Paul
ExcelDemy Team

Hi JEFF! Thanks for your nice compliment. To remove the InputBox and make the code always select Column 1, just remove the InputBox command and variable VCL. After that, replace the VCL with 1.
You can directly use the following code:

Sub Split_Data()
Dim L As Long
Dim DS As Worksheet
Dim XCL As Long
Dim MARY As Variant
Dim title As String
Dim titlerow As Integer
Application.ScreenUpdating = False
Set DS = ActiveSheet
L = DS.Cells(DS.Rows.Count, 1).End(xlUp).Row
title = "A1"
titlerow = DS.Range(title).Cells(1).Row
XCL = DS.Columns.Count
DS.Cells(3, XCL) = "Unique"
For X = 2 To L
On Error Resume Next
If DS.Cells(X, 1) <> "" And Application.WorksheetFunction.Match(DS.Cells(X, 1), DS.Columns(XCL), 0) = 0 Then
DS.Cells(DS.Rows.Count, XCL).End(xlUp).Offset(1) = DS.Cells(X, 1)
End If
Next
MARY = Application.WorksheetFunction.Transpose(DS.Columns(XCL).SpecialCells(xlCellTypeConstants))
DS.Columns(XCL).Clear
For X = 2 To UBound(MARY)
DS.Range(title).AutoFilter field:=1, Criteria1:=MARY(X) & ""
If Not Evaluate("=ISREF('" & MARY(X) & "'!A1)") Then
Sheets.Add(after:=Worksheets(Worksheets.Count)).Name = MARY(X) & ""
Else
Sheets(MARY(X) & "").Move after:=Worksheets(Worksheets.Count)
End If
DS.Range("A" & titlerow & ":A" & L).EntireRow.Copy Sheets(MARY(X) & "").Range("A4")
Next
DS.AutoFilterMode = False
DS.Activate
Application.ScreenUpdating = True
End Sub

If the last two rows contain the same data, then it fails to delete both rows. Otherwise, it works.