VBA HELP - Big table into a smaller table

[bobshah2010]

Hi Guys,

I would like some help in making a macro that pulls columns from a BIG table (e.g 40 rows x 50 Cols) to a smaller table (40 rows x 5 cols) into a new sheet, and those 5 columns that it converts the table to are based of identically matching headers in a list i've created on another sheet.

So essentially i will have 3 sheets -

• One with the list of headers of the columns i'd like to extract from the master table sheet
• Master table sheet (the big table with 40 rows x 50 cols)
• Macro derived table that when i push a button it will generate the new smaller table into this sheet with the same amount of rows, but different number of columns.

The reason I need this is to create a correlation matrix based on the smaller table as opposed to making a correlation matrix from the larger table. The smaller table will also be constantly updated i.e number columns from the Big table may be added to the end of the smaller table i.e today it might be 5 rows, but as the list from the first sheet increases so does the smaller table.

If it is possible could the step that involves generating the smaller table be improved by also performing a correlation matrix based on the size of the smaller table - and include the headers preferably in the matrix output.

Thanks a lot guys, your help is always much appreciated.

Kind regards

 

[Michael M]

Ah, ok thanks Brian....got it now !!
But my response remains the same....never used it, and certainly don't know how to code a result at this stage for it.
I guess if Bobshah provided the manual steps to get the required result, it wouldn't be a major to code it !!

 

[bobshah2010]

Hi Michael,

The manual steps:

1. Create a table that I would like to observe and create a correlation matrix
2. Open the data analysis tool pack and select "Correlation"
3. A userform opens up and asks me to select the input range and using the example in the previous page I select $A$1:$E$5
4. Keep the grouped by checked in for columns
5. Select and tick Labels in the first row
6. Check in for Output options > Output range: $A$9
7. Hit ok and it automatically generates the table for me.

I recorded the macro to observe what i needed to do to code the macro in. However I think i'm having issues with the macro automatically determining the input range based on the size of the table.

See attached picture if the steps do not make much sense: Imgur: The most awesome images on the Internet

 

[Michael M]

Can you post the macro you recroded ?
AND
The page is the original data ?...not the smaller table on sheet 3 ?

 

[bobshah2010]

Hi Michael

Sub MacroCorre()'
' MacroCorre Macro
'


'
     Application.Run "ATPVBAEN.XLAM!Mcorrel", ActiveSheet.Range("$A$1:$E$5"), _
        ActiveSheet.Range("$A$8"), "C", True
    Range("J13").Select
End Sub

That's the recorded macro^

In regards to your second question - yes you are right the example I showed is the original table but not the smaller table - However If i were to assume that the table generated was smaller the principle should be the same i.e. if I made the table go across 3 columns as opposed to the existing 5.

I hope this makes sense.

 

[Michael M]

Try this
It will run based on whichever sheet is the activesheet

Sub MacroCorre() '
' MacroCorre Macro
Dim lr As Long, lc As Integer
lr = Cells(Rows.Count, "A").End(xlUp).Row
lc = Cells(1, Columns.Count).End(xlToLeft).Column
Application.Run "ATPVBAEN.XLAM!Mcorrel", ActiveSheet.Range(Cells(1, 1), Cells(lr, lc)), _
        ActiveSheet.Range("$A$" & lr + 10), "C", True
End Sub

 

[bobshah2010]

I'm getting a strange error:

"Run-time error '1004'

Sorry, we couldn't find C:\name\Documents\ATPVBAEN.XLAM. Is it possible it was moved, renamed or deleted?"

Not sure why this is happening - When i hit debug it highlights this section

[COLOR=#333333]Application.Run "ATPVBAEN.XLAM!Mcorrel", ActiveSheet.Range(Cells(1, 1), Cells(lr, lc)), _
[/COLOR][COLOR=#333333]        ActiveSheet.Range("$A$" & lr + 10), "C", True[/COLOR]

 

[bobshah2010]

Nevermind - I got it too work.

Not sure how but my analysis toolpack add-in got unchecked..

 

[bobshah2010]

Thanks Michael - Works perfectly!

Is there a way to get rid of the activesheet command?

I tried to do attempt it myself and build the code you made into what I originally had - Is there something wrong with me trying to force the Application to run in "ws3' which is the worksheet with the smaller table generated

Sub GenerateCorrMatrix()Dim ws1 As Worksheet, ws2 As Worksheet, ws3 As Worksheet, s1 As Worksheet, lr As Long, lc As Integer
Dim c As Integer, c1 As Integer, r As Long, lc2 As Integer, rng As Range, lr2 As Long, lc3 As Integer
Set ws1 = Sheets("Data Table")
Set ws2 = Sheets("Unique Name List")
Set ws3 = Sheets("Correlation Matrix")
Set s1 = Sheets("Hypothesis")

' Copies across a unique list from the Hypothesis page into sheet "unique list"


s1.Range("M21:M1000").Copy ws2.Range("A1")
ws2.Range("A:A").RemoveDuplicates Columns:=1, Header:=xlNo

' Removes first blank row that results from removing duplicates
On Error Resume Next
ws2.Columns("A").SpecialCells(xlCellTypeBlanks).EntireRow.Delete


' Clears existing table each time macro is run
Sheets("Correlation Matrix").Cells.Clear

' Generates smaller table


lr = ws2.Cells(Rows.Count, "A").End(xlUp).Row
lc = ws1.Cells(1, Columns.Count).End(xlToLeft).Column
lc2 = ws3.Cells(1, Columns.Count).End(xlToLeft).Column
For c = 1 To lc
    For r = 1 To lr
        If ws2.Cells(r, 1).Value = ws1.Cells(1, c) Then
            ws1.Columns(c).Copy ws3.Cells(1, lc2)
        lc2 = lc2 + 1
        End If
    Next r
Next c

' Runs correlation matrix application on the smaller table - re-sizing as more columns added to smaller table


lr2 = ws3.Cells(Rows.Count, "A").End(xlUp).Row
lc3 = ws3.Cells(1, Columns.Count).End(xlToLeft).Column
Application.Run "ATPVBAEN.XLAM!Mcorrel", ws3.Range(Cells(1, 1), Cells(lr2, lc3)), _
        ws3.Range("$A$" & lr2 + 10), "C", True






End Sub

 

[bobshah2010]

Michael - I worked it out!

lr2 = ws3.Cells(Rows.Count, "A").End(xlUp).Rowlc3 = ws3.Cells(1, Columns.Count).End(xlToLeft).Column
Application.Run "ATPVBAEN.XLAM!Mcorrel", ws3.Range(ws3.Cells(1, 1), ws3.Cells(lr2, lc3)), _
        ws3.Range("$A$" & lr2 + 10), "C", True

Thank you so much for all your help! So grateful!!!