Match a Named Range

Hi Tiffany,

Maybe something like below, from when you posted something like this previously?

Tiffany,

I've modified the SQL in the query so that now it will return all columns. You can have as many columns as you like and any names and in any order - so long as both lists have matching headers and you have headers "Account #" and "Risk Grade".

HTH, Fazza

Code:

Sub Report() 
    
    Dim sConn As String 
    Dim sSQL As String 
    
    Dim wks As Worksheet 
    
    Worksheets("1Q").Range("A1").CurrentRegion.Name = "tbl_1Q" 
    Worksheets("2Q").Range("A1").CurrentRegion.Name = "tbl_2Q" 
    
    sConn = "ODBC;DSN=Excel Files;DBQ=" & _ 
            ActiveWorkbook.FullName & _ 
            ";DefaultDir=" & ActiveWorkbook.Path & _ 
            ";DriverID=790;MaxBufferSize=2048;PageTimeout=5;" 
    
    sSQL = "SELECT 'Removed 2Q' AS [Account Change], tbl_1Q.*" & vbCr & _ 
            "FROM {oj tbl_1Q tbl_1Q LEFT OUTER JOIN tbl_2Q tbl_2Q ON tbl_1Q.`Account #` = tbl_2Q.`Account #`}" & vbCr & _ 
            "WHERE (tbl_2Q.`Account #` Is Null)" & vbCr & _ 
            "UNION" & vbCr & _ 
            "SELECT 'Added 2Q' AS [Account Change], tbl_2Q.*" & vbCr & _ 
            "FROM {oj tbl_2Q tbl_2Q LEFT OUTER JOIN tbl_1Q tbl_1Q ON tbl_2Q.`Account #` = tbl_1Q.`Account #`}" & vbCr & _ 
            "WHERE (tbl_1Q.`Account #` Is Null)" & vbCr & _ 
            "UNION" & vbCr & _ 
            "SELECT 'Risk Change 2Q' AS [Account Change], tbl_2Q.*" & vbCr & _ 
            "FROM tbl_1Q tbl_1Q, tbl_2Q tbl_2Q" & vbCr & _ 
            "WHERE tbl_2Q.`Account #` = tbl_1Q.`Account #` AND tbl_2Q.`Risk Grade` <> tbl_1Q.`Risk Grade`" 
    
    Set wks = Worksheets.Add 
    
    With wks 
        .Name = "Report " & Format$(Now, "h.mm am/pm d mmm yy") 
        With .QueryTables.Add(Connection:=sConn, Destination:=.Range("A1"), Sql:=sSQL) 
            .Refresh BackgroundQuery:=False 
        End With 
        .Move 
    End With 
    Set wks = Nothing 
    
End Sub

Click to expand...

from http://www.mrexcel.com/board2/viewtopic.php?t=248415

Thanks for the reply.

I have been using this code but sometimes it stops at this line of the code.

.Refresh BackgroundQuery:=False

Then, if I close out of the file and try again it works. Any suggestions as to why? When it works it's great, but I have to go in and out of the file to get it to work.

TiffanyG

Good question, Tiffany. I don't know. Maybe you would get an error (or stoppage?) if the file had never been saved. Other than that, I can't think of a reason.

The code is one way to implement a query table - it creates a new query on a new worksheet and then makes it a new workbook. Normally one might simply create the query table once (permanently within the original file) and for all subsequent reports, just refresh the table. So, the implementation in the code is likely not how I would use a query table, but I am familiar with them. I have found it easiest for others to implement (for example when posting to this forum) by code like I posted 'cause then they don't have to deal with other idiosyncracies of query tables - because people are not familiar with query tables. Hope this is making sense! If you learn about query tables you might be able to implement the solution better.

regards, Fazza

Thanks for your help! I'll keep trying and I'll figure it out!

Tiffany

Another thought. Every column must have a header.

Do you receive an error message?

Fazza,
The code has been working. Now I also need to carry over any that were on the previous quarter and also on the new quarter with a line that says 'no change'. This would happen if the risk grade is the same. The code now indicates, added, removed or change. Could you please help me add the additional code.

I love this code but I am at a total loss when I need a change. It is way over my level of VB.
I would be extremely grateful!

TiffanyG

How about this, then? Untested.

Note near the top of the code references to worksheets 1Q and 2Q. Modify to suit. This step could be coded smarter but first lets see if it works. (I'm thinking you could input the sheet names, or one of them anyway, and the VBA could then workout which sheets to refer to.)

Tiffany,

Re-reading your original post, I see that this was to for monthly reporting with worksheets "prior" and "current", and with headers in row 7.

What I've posted is from the earlier thread that I referenced in my first reply, and hence it refers to worksheets "1Q" and "2Q" for quarterly reporting, and, is based on the data starting in row 1.

Can you edit what I've just posted to suit whatever is required? Fazza

Thank you so much!
I will try it at work tomorrow.
You are the best!!

I actually use this for a quarterly report and a monthly report. I have been changing my sheet names to correspond with the code and just changing them back when I am finished. However, the information does start in row 7. To compensate for this I have also been cutting the header and pasting it back when I am done. To change it to start in row 7 do I just change any reference of A1 to A7?


TiffanyG