Hi Exceler,

Here, I tried the first method for 50 sets of data and it worked but you have to change the cell references based on your dataset. For your better understanding, I am attaching the images along with the formula.
=INDEX($D$5:$D$30,MATCH(1,MMULT(--($B$5:$C$30=F5),TRANSPOSE(COLUMN($B$5:$C$30)^0)),0))

The images of datasets

Here, I used the formula for the entire dataset. I changed the references based on my dataset.

Output for 50 values:

Note: If your dataset is very large kindly send us your dataset

Thanks
Shamima Sultana

Hello Mark,

We uploaded the Excel files again, you can check these files also. If you find any difficulty opening the file let us know.

Thanks

Hi Froggy,
Hope you are doing well. Thanks for reaching out to us with your issue.

As you intend to apply the above code to a workbook with multiple sheets so you need to add some extra lines in your code to define all available worksheets or selected worksheets.

Here, I will give you two solution
1. For selected sheets
2. For all the available sheets in a workbook

1. If you want to apply the same code in some selected sheets then use the code given below where I declared sheets names by using an Array.

Option Explicit
Sub AddSpaceBetweenRows()
Dim all_sheets As Worksheet
Dim rng As Range
Dim i As Long
Dim all As Variant

Set all = sheets(Array(“VBA1”, “VBA2”))

For Each all_sheets In all
all_sheets.Select
Set rng = Range(“B5:E9”)
For i = rng.Rows.Count To 2 Step -1
rng.Rows(i).EntireRow.Insert
Next i
Next
End Sub

2. To loop through all the available sheets of your workbook you will need to use the code given below where I declared all_sheets and used an extra For Loop.

For your better understanding, I’m providing the modified code here,

Option Explicit
Sub AddSpaceBetweenRows()
Dim all_sheets As Worksheet
Dim rng As Range
Dim i As Long

For Each all_sheets In Worksheets
all_sheets.Select
Set rng = Range(“B5:E9”)
For i = rng.Rows.Count To 2 Step -1
rng.Rows(i).EntireRow.Insert
Next i
Next
End Sub

Note: Based on your Excel file you will need to change the sheet name and the cell range selection.

For further queries comment down below.

Hi Helen,
I’m glad that our article helped you. For any types of Excel related problems kindly check out our ExcelDemy site.

Thanks
Shamima

Hi A,

Thanks for your appreciation.

Hi Deon Bailey,
Hope you are doing well. Thanks for reaching out to me with your issue.

As you didn’t share your Excel file that’s why it is hard to understand your sheet name and where is the problem occurring.

But you need not to worry, I’m giving you a possible solution so that you can add data to your selected sheet to the last row.

The reason for overwriting the added data is your code wasn’t finding the last row it was showing 1 as the last row number.

Here, I added data according to my dataset I used in this article. I commented out your code and added some required lines.

Sub Insert_Value_from_LastRow()

‘TargetSheet = Cmb_Months.Value
‘If TargetSheet = “” Then
‘Exit Sub
‘End If
Dim targetSheet As Worksheet
Set targetSheet = ThisWorkbook.Worksheets(“Dataset”)
targetSheet.Activate
‘lastrow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
LastRow = ActiveSheet.UsedRange.Rows(ActiveSheet.UsedRange.Rows.Count).Row
MsgBox LastRow

‘ActiveSheet.Cells(LastRow + 6, 2).Value = Cmb_Area.Value
‘ActiveSheet.Cells(LastRow + 6, 3).Value = Txt_Ln_Manager.Value
‘ActiveSheet.Cells(LastRow + 6, 4).Value = Txt_FName.Value
‘ActiveSheet.Cells(LastRow + 6, 5).Value = Txt_Surname.Value
‘ActiveSheet.Cells(LastRow + 6, 6).Value = Txt_S_Number.Value

ActiveSheet.Cells(LastRow + 3, 2).Value = “Rachel Ross”
ActiveSheet.Cells(LastRow + 3, 3).Value = “Germany”
ActiveSheet.Cells(LastRow + 3, 4).Value = “laptop”
ActiveSheet.Cells(LastRow + 3, 5).Value = 4567

End Sub

Note: Whenever you want to insert or add data after a particular row it is better to see the last row number by using MsgBox. It will help you to understand why data is overlapping.

I also added the images.

For further queries, you can send me your Excel file.

Thanks
Shamima Sultana