Automate Report

[vradhak7]

Hi Guys,
I am new to VBA and I am having trouble figuring out the logic behind this. I have attached the excel sheet for reference(Thought it would be pretty hard to explain). It consists of the Input sheet(how I get the raw data) and output sheet(how I should present it using vba). I want it to be dynamic so that in future if I copy paste new set of data in input sheet, it should generate the required output. Your help in this regard would be greatly appreciated. Thank you in advance.


Regards
Vradhak7

[jonh]

Hi!


Sub GenRpt()
Dim wsIn As Worksheet, wsOut As Worksheet, arr
Dim i As Long, j As Long, k As Long, l As Long, r As Long
Set wsIn = Sheets("input")
With wsIn
arr = .Range(.Cells(2, 2), .Cells(.UsedRange.Rows.Count, 12)) '.Value
End With
Set wsIn = Nothing

Set wsOut = Sheets("output")
r = 1
With wsOut
.Cells.Clear
.Range("A1:S1") = Array("Bid No", "Hub", "AM/PM", "Days", _
"Sunday", "", "Monday", "", "Tuesday", "", "Wednesday", _
"", "Thursday", "", "Friday", "", "Saturday", "", "Hours")
.Range("E2:R2") = Array("Base Start", "Base End", "Base Start", _
"Base End", "Base Start", "Base End", "Base Start", "Base End", _
"Base Start", "Base End", "Base Start", "Base End", "Base Start", "Base End")

For i = 1 To UBound(arr, 1)
For j = 1 To arr(i, 4)
.Cells(r + 2, 1) = r
.Cells(r + 2, 2) = arr(i, 1)
.Cells(r + 2, 3) = arr(i, 2)
.Cells(r + 2, 4) = arr(i, 3)
.Cells(r + 2, 19) = 32
l = 0
For k = 5 To 11
If arr(i, k) Then .Cells(r + 2, k + l) = "AM"
l = l + 1
Next
r = r + 1
Next
Next
End With
Set wsOut = Nothing
End Sub

[vradhak7]

That worked perfect jonh! Your effort in this regard is greatly appreciated! Looking forward to learn a lot from you! Thanks!