ragav_in
Board Regular
- Joined
- Feb 13, 2006
- Messages
- 89
- Office Version
- 365
- 2016
- Platform
- Windows
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.
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
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.
VBA Code:
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