

March 3, 2020

I have two excel work books
1)work book1 Named as Week1 ->it has two worksheets Alerts & Tasks
2)Work Book2 named as week2 -->it has two worksheets Alerts & Tasks
Example of my Week1 file
1-Jan-2020 Alert-name Description
1-Jan-2020 Alert-name Description
2-Jan-2020 Alert-name Description
2-Jan-2020 Alert-name Description
when i loop through Week1/Week2 work book with Work sheet named Alerts
before every date i need to add the lines as
1-Jan-2020 L1 Monitoring
I was able to loop through each work book and its worksheets.
Currently i was able to loop and consolidate the data to a single sheet ,But not sure how to insert the above line before every date from the Alerts sheet
```Sub getDataFromWbs()
Dim wb As Workbook, ws As Worksheet
Set fso = CreateObject("Scripting.FileSystemObject")
'This is where you put YOUR folder name
Set fldr = fso.GetFolder("C:\Users\Radha\Downloads\Temp\Temp")
'Next available Row on Master Workbook
y = ThisWorkbook.Sheets("sheet1").Cells(Rows.Count, 1).End(xlUp).Row + 1
'Loop through each file in that folder
For Each wbFile In fldr.Files
'Make sure looping only through files ending in .xlsx (Excel files)
If fso.GetExtensionName(wbFile.Name) = "xlsx" Then
'Open current book
Set wb = Workbooks.Open(wbFile.Path)
'Loop through each sheet (ws)
For Each ws In wb.Sheets
'Last row in that sheet (ws)
wsLR = ws.Cells(Rows.Count, 1).End(xlUp).Row
'Loop through each record (row 2 through last row)
For x = 2 To wsLR
'Put column 1,2,3 and 4 of current sheet (ws) into row y of master sheet, then increase row y to next row
ThisWorkbook.Sheets("sheet1").Cells(y, 1) = ws.Cells(x, 1) 'col 1
ThisWorkbook.Sheets("sheet1").Cells(y, 2) = ws.Cells(x, 2) 'col 1
ThisWorkbook.Sheets("sheet1").Cells(y, 3) = CDate(ws.Cells(x, 3)) 'col 1
ThisWorkbook.Sheets("sheet1").Cells(y, 4) = ws.Cells(x, 4) 'col 1
y = y + 1
Next x
Next ws
'Close current book
wb.Close
End If
Next wbFile
End Sub
```


March 3, 2020

Thanks a lot Philip for the help 🙂
Need some help with the enhancement.
Currently my report is coming as below . it works well.
01-Feb-2020 | Alert | L2 | AlertName1 | AlertDescription1 |
01-Feb-2020 | Alert | L2 | AlertName2 | AlertDescription2 |
02-Feb-2020 | Alert | L2 | AlertName5 | AlertDescription5 |
02-Feb-2020 | Alert | L2 | AlertName6 | AlertDescription6 |
01-Feb-2020 | TASKS | L2 | taskName1 | taskDescription1 |
01-Feb-2020 | TASKS | L2 | taskName2 | taskDescription2 |
02-Feb-2020 | TASKS | L2 | taskName3 | taskDescription3 |
I need to check for each date and add a row as below only for the Alert
01-Feb-20 | Monitoring | Nagios | Remedy |
Please find the attached files with code


March 3, 2020

Sorry it was not clear from my side.
Not to add a row for every Alert.its before every new date of alert
Example The requirement is as below
01-Feb-2020 | Monitoring | Nagios | Remedy | |
01-Feb-2020 | Alert | L2 | AlertName1 | AlertDescription1 |
01-Feb-2020 | Alert | L2 | AlertName2 | AlertDescription2 |
02-Feb-2020 | Monitoring | Nagios | Remedy | |
02-Feb-2020 | Alert | L2 | AlertName5 | AlertDescription5 |
02-Feb-2020 | Alert | L2 | AlertName6 | AlertDescription6 |
02-Feb-2020 | Alert | L2 | AlertName6 | AlertDescription6 |
Currently it is adding before all the alerts as below
01-Feb-2020 | Alert | L2 | AlertName1 | AlertDescription1 |
01-Feb-2020 | Monitoring | Nagios | Remedy | |
01-Feb-2020 | Alert | L2 | AlertName2 | AlertDescription2 |
01-Feb-2020 | Monitoring | Nagios | Remedy | |
02-Feb-2020 | Alert | L2 | AlertName5 | AlertDescription5 |
02-Feb-2020 | Monitoring | Nagios | Remedy | |
02-Feb-2020 | Alert | L2 | AlertName6 | AlertDescription6 |
1 Guest(s)
