Code to extract only number from a cell that contains Text and Number separated by Colon

[ragav_in]

Dear members,

I am currently working on multiple files which are in different format. However the last row of the Column A has a text "Record Count: XXXXX"; the number after the Colon is Dynamic and varies with file to file. I would like to have a macro the get the "digits" after the "colon" in each file and populate it in another sheet along with the workbook name. I have the below code which can do the activity, however the step of getting the number from that particular cell is not known to me. I am providing the code that I have written below.

sub getnumber()
Dim i As Integer, rngData As Range, load As Integer, mybook As String
'Setting the range
Set rngData = Range("A1").CurrentRegion
On Error Resume Next
'Removing Autofilter if applied
If ActiveSheet.AutoFilterMode Then ActiveSheet.AutoFilter.ShowAllData
End If
'Selecting the last cell in Column A which has the text "Record Count: "
Range("A1").Select
Selection.End(xlDown).Select
If ActiveCell.Value Like "*Record Count: *" Then
load = "code here" --> this is the missing piece
'get workbook name
mybook = ActiveWorkbook.Name
'pasting the data in another book "DefectLog.xlsx"
Workbooks("DefectLog.xlsx").Activate
Sheets ("Sheet1").Select
Range("A1").End(xlDown).Select
ActiveCell.Offset(1, 0).Select
ActiveCell.Value = mybook
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = load
End sub

I would request this Forum to help me in identifying the only step that is missing, so that I can start combining data from multiple files. I thank each and everyone in this forum for their time and effort to read this post and provide the solution.

Thanks
ragav_in

 

[bebo021999]

Altenative solution:

Sub getnumber()
Dim lr&, load&
If ActiveSheet.AutoFilterMode Then ActiveSheet.AutoFilter.ShowAllData
lr = Cells(Rows.Count, "A").End(xlUp).Row
If Cells(lr, 1).Value Like "*Record Count: *" Then
load = Trim(Replace(Cells(lr, 1).Value, "Record Count:", ""))
With Workbooks("DefectLog.xlsx").Worksheets("Sheet1")
    lr = .Cells(Rows.Count, "A").End(xlUp).Row
    .Cells(lr + 1, 1).Value = ActiveWorkbook.Name
    .Cells(lr + 1, 2).Value = load
End With
End Sub