Transferring Data from many sheets to a single sheet

**JBeaucaire** · 09-22-2009, 12:13 PM

Mohit,

Attached is your sheet with a macro in it called DatedReport. If you make a change to cell G1, the DatedReport macro is run automatically for you.

I had to change the title of the sheet Kotak 811 because it wasn't an exact match for the text strings in the A/C details section. Make sure those stay in sync.

For the curious, here's the code...
In the Sheet module "Report":

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Count > 1 Then Exit Sub
    If Target.Address = "$G$1" Then Call DatedReport
    Target.Select
End Sub

And in a regular Module1:

Option Explicit

Sub DatedReport()
Dim Rng As Range, cell As Range, LR As Long
Application.ScreenUpdating = False
Application.EnableEvents = False

Set Rng = Range("A4:A21")
Range("A24:G" & Rows.Count).Clear

For Each cell In Rng
    If cell <> "" Then
        With Sheets(cell.Text)
            .Range("A4:I4").AutoFilter
            .Range("A4:I4").AutoFilter Field:=1, Criteria1:=Format(Sheets(1).Range("G1"), "DD-MMM")
            LR = .Range("A" & Rows.Count).End(xlUp).Row
            If LR > 4 Then
                .Range("A1").Copy Range("A" & Rows.Count).End(xlUp).Offset(2, 0)
                With Range(Range("A" & Rows.Count).End(xlUp), Range("A" & Rows.Count).End(xlUp).Offset(0, 6))
                    .MergeCells = True
                    .Font.Bold = True
                    .Interior.ColorIndex = 15
                    .Borders.LineStyle = xlContinuous
                    .Borders.Weight = xlMedium
                End With
                .Range("A4:G" & LR).Copy Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
                .Range("A4:I4").AutoFilter
            Else
                .Range("A4:I4").AutoFilter
            End If
        End With
    End If
Next cell

Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub

**mohitspamz** · 09-23-2009, 01:09 AM

Sir,
That is beautiful! Thanks alot for ur time and effort!
I'll take this with my accountant colleague and let him check if everything is going right for him in this workbook... and get back if there r any changes that we might have....

Hey can you suggest how do i start learning basic vb language?

Thanks again!

Regards

Mohit

**mohitspamz** · 09-25-2009, 02:32 AM

Hey!
So i got my accountant colleague to transfer all the data and check if everything works fine and we encountered 2 problems; 1 macro based and the other formula based... and surprisingly i could sort out the issue with macro which btw if i look back at it now wasnt really a macro issue but a formatting issue, but still cant get hold of the correct approach to sort out the formula based issue.

Actually, on a given day if there are no transactions in a given A/c the "consolidated report generates a N/A error However, i want it to show the balance after the last transaction before the report date.
So if there are no transactions on 5th,6th,7th and i'm generating a report of 7th i require it to show me the final balance of 4th. I tried different formulas to get the desired result but cudnt manage. So here i am once again asking for ur kind help...

the variations of the formulas i tried:

=LOOKUP(2,1/('Kotak 811'!B5:B1300=Report!G1),'Kotak 811'!H5:H1300)

=LOOKUP(2,1/('Kotak 811'!B5:B1300<=Report!G1),'Kotak 811'!H5:H1300)

=LOOKUP(2,1/(('Kotak 811'!B5:B1300=Report!G1)*('Kotak 811'!B5:B1300<Report!G1)),'Kotak 811'!H5:H1300)

=IF(ISNA(LOOKUP(2,1/('Kotak 811'!B5:B1300=Report!G1),'Kotak 811'!H5:H1300),LOOKUP(2,1/('Kotak 811'!B5:B1300<=Report!G1),'Kotak 811'!H5:H1300))

All these formulas to be entered in B4 in Report Sheet. Workbook sample is the same as wot Sir JBeaucaire posted earlier.

@JBeaucaire
Btw, the date format was causing the report to not be generated for first 9 days of the month because of the format of the date.. my data has the date format set as "DD-MMM-YY" so to match yours i converted into "DD-MMM" but still on first 9 days the date wud still come out as "D-MMM" which did not allow the filtering to take place.

Thanks Again

Regards

Mohit