How to get a data to be input in to certain rows depending on a number based in a refernce cell

excel

#1

Hi,

New here and hoping some one can help, i am currently using this code

     Sub GetSheetnames_and_IED_addressing()
           'Gets the tab names of the white book then gets the ied addressing and moves to the 'Tab Names 
              from White Book'
                     'It then deletes all text apart from ied's this is then refrenced in the extraction process

                          'Takes off display alerts

Application.DisplayAlerts = False
’Should stop any flicker or at least reduce
Application.ScreenUpdating = False
’Open Workbook to get sheet names
Workbooks.Open Filename:=“D:\Projects\ASE Templates\ASE Template White Book.xlsx”
'This then clears the contents of the whole of column A
Dim ws As Worksheet
Dim i As Integer
With ThisWorkbook.Worksheets(“Tab Names from white book”)
.Range(“A:A”).ClearContents
’This then puts the tab names into column A and the the IED address from H4 if it is there
’and loobs for every worksheet in the workbook
For Each ws In ActiveWorkbook.Worksheets
i = i + 1
.Range(“A” & i) = ws.Name
.Range(“B” & i) = ws.Range(“H4”).Value

'Loop

Next ws
’Closes Ase Templates WB
End With
Workbooks(“ASE Template White Book.xlsx”).Close

'Deletes all rows that have text in column B so only ied addressing numbers will be left
On Error Resume Next
Range(“b1:b100”).SpecialCells(xlCellTypeConstants, xlTextValues).EntireRow.Delete
Range(“b1:b100”).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
On Error GoTo 0

'Reenables display alerts and screen updating

Application.DisplayAlerts = True
Application.ScreenUpdating = True

End Sub

What i would like to do is if the number in column b is 1 or 33 for eg. put that information in to the relevant row number rather than currently it just starts at row 1 so i get 1,33,4,5,6,2 this is based on the order of the worksheets in the closed workbook and the number is an address which i am using as a reference for an indirect function to pull the information from the worksheet which will change depending on order of worksheets

any help aprreciated