How to reference Data from a query in a different sheet within same workbook.

[Chris Slater]

Hi Guys,
I am working on a report that pulls in data from another system via a query. The problem is that the data pulls in in the wrong order and cannot be changed. I will call the sheet with this data "DATA".
So what I need to do is to use another sheet for the report which references each column from the "DATA" sheet but in the order required for the report. Also I will need to add cells with formulas at the base of the report for summing the totals.
All this is simple if the content of the "DATA" sheet doesn't change but, as you can imagine, in the real world it's constantly updating which means the number of rows in the "DATA" sheet is increasing/decreasing on a regular basis.
What I need to do is create a sheet that dynamically references the "DATA" sheet in such a way that the content expands or collapses depending on what is happening to the content of the "DATA" sheet. ie it adds or deletes rows automatically without overwriting the formulas at the foot of the report.
Can it be done? And if so I would really appreciate some help from you guys to make it work.
Thanks.
Chris

 

[mumps]

Copy and paste this macro into the worksheet code module. Do the following: right click the tab name for your "Report" sheet and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. This macro will update the "Report" sheet each time the sheet is activated.

Private Sub Worksheet_Activate()
    Application.ScreenUpdating = False
    Dim desWS As Worksheet, srcWS As Worksheet, LastRow As Long, i As Long, header As Range, x As Long, fnd As Range
    Set desWS = ThisWorkbook.Sheets("Report")
    Set srcWS = ThisWorkbook.Sheets("Data")
    LastRow = srcWS.Range("A" & srcWS.Rows.Count).End(xlUp).Row
    With desWS.UsedRange
        .Offset(1).ClearContents
        .Borders.LineStyle = xlNone
    End With
    For Each header In desWS.Range("A1:X1")
        Set fnd = srcWS.Rows(1).Find(header, LookIn:=xlValues, lookat:=xlWhole)
        If Not fnd Is Nothing Then
            With srcWS
                .Range(.Cells(2, fnd.Column), .Cells(LastRow, fnd.Column)).Copy desWS.Cells(desWS.Rows.Count, header.Column).End(xlUp).Offset(1, 0)
            End With
        End If
    Next header
    With desWS
        LastRow = .Range("A" & .Rows.Count).End(xlUp).Row
        .Range("O2").Resize(LastRow - 1).Formula = "=IFERROR(J2*Rates!$B$1,0)"
        .Range("P2").Resize(LastRow - 1).Formula = "=IFERROR(K2*Rates!$B$2,0)"
        .Range("Q2").Resize(LastRow - 1).Formula = "=IFERROR(L2*Rates!$B$3,0)"
        .Range("R2").Resize(LastRow - 1).Formula = "=IFERROR(M2*Rates!$B$4,0)"
        .Range("S2").Resize(LastRow - 1).Formula = "=IFERROR(N2*Rates!$B$5,0)"
        .Range("O" & LastRow + 1).Resize(, 6).Formula = "=SUM(O$2:O$6)"
        .Range("J" & LastRow + 1).Resize(, 6).Formula = "=SUM(J$2:J$6)"
        .Range("T2").Resize(LastRow - 1).Formula = "=SUM(O2:S2)"
        .Range("T" & LastRow + 1).Formula = "=SUM(T2:T6)"
        .Range("W2").Resize(LastRow - 1).Formula = "=(Data!U2)"
        .Range("W" & LastRow + 1).Formula = ("=SUM(W2:W6)")
        .Range("Y2").Resize(LastRow - 1).Formula = "=T2+W2"
        .Range("Y" & LastRow + 1).Formula = ("=SUM(Y2:Y6)")
        .Range("A" & LastRow + 1).Resize(, 25).Borders(xlEdgeTop).LineStyle = xlContinuous
        .Range("A" & LastRow + 1).Resize(, 25).Borders(xlEdgeBottom).LineStyle = xlContinuous
    End With
    Application.ScreenUpdating = True
End Sub

 

[Chris Slater]

Will the number of columns in the Report always be 25? Will there always be 5 rates?

As far as it's been agreed that's the case. The customer might add at a later date but I'll have to deal with that as and when.

 

[mumps]

Have you tried the macro I suggested?

 

[Chris Slater]

Copy and paste this macro into the worksheet code module. Do the following: right click the tab name for your "Report" sheet and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. This macro will update the "Report" sheet each time the sheet is activated.

Private Sub Worksheet_Activate()
    Application.ScreenUpdating = False
    Dim desWS As Worksheet, srcWS As Worksheet, LastRow As Long, i As Long, header As Range, x As Long, fnd As Range
    Set desWS = ThisWorkbook.Sheets("Report")
    Set srcWS = ThisWorkbook.Sheets("Data")
    LastRow = srcWS.Range("A" & srcWS.Rows.Count).End(xlUp).Row
    With desWS.UsedRange
        .Offset(1).ClearContents
        .Borders.LineStyle = xlNone
    End With
    For Each header In desWS.Range("A1:X1")
        Set fnd = srcWS.Rows(1).Find(header, LookIn:=xlValues, lookat:=xlWhole)
        If Not fnd Is Nothing Then
            With srcWS
                .Range(.Cells(2, fnd.Column), .Cells(LastRow, fnd.Column)).Copy desWS.Cells(desWS.Rows.Count, header.Column).End(xlUp).Offset(1, 0)
            End With
        End If
    Next header
    With desWS
        LastRow = .Range("A" & .Rows.Count).End(xlUp).Row
        .Range("O2").Resize(LastRow - 1).Formula = "=IFERROR(J2*Rates!$B$1,0)"
        .Range("P2").Resize(LastRow - 1).Formula = "=IFERROR(K2*Rates!$B$2,0)"
        .Range("Q2").Resize(LastRow - 1).Formula = "=IFERROR(L2*Rates!$B$3,0)"
        .Range("R2").Resize(LastRow - 1).Formula = "=IFERROR(M2*Rates!$B$4,0)"
        .Range("S2").Resize(LastRow - 1).Formula = "=IFERROR(N2*Rates!$B$5,0)"
        .Range("O" & LastRow + 1).Resize(, 6).Formula = "=SUM(O$2:O$6)"
        .Range("J" & LastRow + 1).Resize(, 6).Formula = "=SUM(J$2:J$6)"
        .Range("T2").Resize(LastRow - 1).Formula = "=SUM(O2:S2)"
        .Range("T" & LastRow + 1).Formula = "=SUM(T2:T6)"
        .Range("W2").Resize(LastRow - 1).Formula = "=(Data!U2)"
        .Range("W" & LastRow + 1).Formula = ("=SUM(W2:W6)")
        .Range("Y2").Resize(LastRow - 1).Formula = "=T2+W2"
        .Range("Y" & LastRow + 1).Formula = ("=SUM(Y2:Y6)")
        .Range("A" & LastRow + 1).Resize(, 25).Borders(xlEdgeTop).LineStyle = xlContinuous
        .Range("A" & LastRow + 1).Resize(, 25).Borders(xlEdgeBottom).LineStyle = xlContinuous
    End With
    Application.ScreenUpdating = True
End Sub

Thanks for that. I was really hoping to achieve this without using Macros if at all possible.
Are you saying it's the only option?

 

[mumps]

I think that it is the most efficient option. After any changes are made in the Data sheet, all you have to do is activate the Report sheet to automatically update the report.

 

[IamSu]

Hi Guys,
I am working on a report that pulls in data from another system via a query. The problem is that the data pulls in in the wrong order and cannot be changed. I will call the sheet with this data "DATA".
So what I need to do is to use another sheet for the report which references each column from the "DATA" sheet but in the order required for the report. Also I will need to add cells with formulas at the base of the report for summing the totals.
All this is simple if the content of the "DATA" sheet doesn't change but, as you can imagine, in the real world it's constantly updating which means the number of rows in the "DATA" sheet is increasing/decreasing on a regular basis.
What I need to do is create a sheet that dynamically references the "DATA" sheet in such a way that the content expands or collapses depending on what is happening to the content of the "DATA" sheet. ie it adds or deletes rows automatically without overwriting the formulas at the foot of the report.
Can it be done? And if so I would really appreciate some help from you guys to make it work.
Thanks.
Chris

Hi Chris,

I recommend just filling down the data on your main worksheet and summing column totals above your data. You can do an =if(Data!D6="","",Data!D6) that way you can get formatting to be specific. or you can use conditional formatting.