Group and split into seperate excel workbooks 3

[Bilberry]

Hi Friends,
I have a huge spreadsheet (~20.000 rows each time). It looks like as follows:

Column A        B       C   D   E               F       G       H               I               J
Test records	595	595 A	11-XXX-12	3522	A	4-11-2013	05:56:17	3,14
Test records	595	595 A	11-XXX-12	3522	A	4-11-2013	08:29:27	3,14
Test records	595	595 A	11-XXX-12	3522	A	4-11-2013	10:33:55	3,14
Test records	595	595 A	11-XXX-12	3522	A	4-11-2013	11:51:54	3,14
Test records	595	595 A	11-XXX-12	3522	A	4-11-2013	12:49:45	3,14
Test records	595	595 A	11-XXX-12	3522	A	4-11-2013	13:53:35	3,14
Test records	595	595 A	11-XXX-12	3522	A	4-11-2013	14:57:59	3,14
Test records	595	595 A	11-XXX-12	3522	A	5-11-2013	05:24:56	3,14
Test records	595	595 A	11-XXX-12	3522	A	5-11-2013	07:44:06	3,14
Test records	595	595 A	11-XXX-12	3522	A	5-11-2013	08:37:08	3,14
Test records	595	595 A	11-XXX-12	3522	A	5-11-2013	10:00:48	3,14
Test records	595	595 A	11-XXX-12	3522	A	5-11-2013	11:59:54	3,14
Test records	595	595 A	11-XXX-12	3522	A	6-11-2013	05:40:48	3,14
Test recordss	595	595 A	11-XXX-13	3523	A	6-11-2013	08:39:37	3,14
Test recordss	595	595 A	11-XXX-13	3523	A	6-11-2013	10:06:36	3,14
Test recordss	595	595 A	11-XXX-13	3523	A	7-11-2013	05:25:56	3,14
Test recordss	595	595 A	11-XXX-13	3523	A	7-11-2013	07:47:20	3,14

I need to group by column E and split these records into seperate workbooks. In this example i want to get two workbooks with the names:
11-XXX-12.xls
11-XXX-13.xls

and containing only the records which belongs to column E.

Anybody idea how to setup this?
A star for the golden solution!

 

[SkipVought]

In the renamed source workbook, does Qr reference ALL the data (the 10,000 rows)?

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Bilberry]

Hi Skip,
I have deleted the file and copy it again, very strange but now it looks like its working. I will keep testing. Thanks again...Excuse me for that

 

[SkipVought]

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Bilberry]

Skip,
Keep getting errors

Do i need to start with a empty sheet? I have create a button which executes the Main sub. When its empty then it gives a subscript out of range error at line:

With ActiveSheet.ListObjects(1).QueryTable

 

[Bilberry]

hmmmm, i have tried to read this whole post again. I have import it manually. But after that if i import another file, the system keeps giving the same error as described above. What im doing wrong?

 

[Bilberry]

Update: I have deleted the columns A:H. If i import the second sheet (as external object) with lets say 100 records the range is still showing =$A$1:$H$20000, is that the problem?

 

[SkipVought]

How is the Qr names range DEFINED?

BTW, you must not delete the QueryTable, which it appears that you have done.

Use your originally recorded macro where you ADDED the QT.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Bilberry]

Hi Skip,
How can I see the names range? Is that in the upper left corner. When I click on that the whole table is selected. From the drop down list, I see the name: Table_Query_from_Excel_Files

Is this what you mentioned?

 

[SkipVought]

Select in the QT

right-click and select Table Range Parameters

In that window SOMEWHERE you can CHANGE the name to Qr.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Bilberry]

Hi Skip,
Im using excel 2013. When rightclicking i couldnt find table range parameters. There is a submenu called table and from there the parameters is grayed out. There is also a external data properties menu. When i click on that i can give a connection name, should i change this part?

 

[SkipVought]

Sorry, I was on my iPad and did not have Excel to reference

It is Right - click > table > external data properties > connection properties and change the Connection Name to Qr.

I'm assumint that you are doing this in the SOURCE WORKBOOK to name the SOURCE TABLE RANGE. It is the SOURCE TABLE RANGE that YOU have names Qr in your recorded code example.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Bilberry]

Unfortunately, the default name was: Query from excel files
I have tried to change the value to QR, then im getting the error:

Microsoft ODBC Excel Driver The Microsoft Access database engine could not find the object 'QR'. Make sure the object exists and that you spell its name and path name correctly. If 'QR' is not a local object, check your network connection or contact the server administrator...

 

[SkipVought]

So you open the SOURCE workbook, not where you VBA code is. You apparently are IMPORTIMG data from some other Excel workbook, into your SOURCE workbook, that needs to have the QueryTable Range Name changed to Qr.

Am I correct so far?

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Bilberry]

Hi Skip,
You are (always) correct!

Ah, i need to define the range in the SOURCE workbook. It was my mistake to check where the VBA code was located. But if i open the SOURCE workbook, there is no table link. So when right clicking, i cant find the table option. Is there another way to change the name and range for the SOURCE workbook?

 

[Bilberry]

Found to place to change this part! I will keep testing, for all the others, who want to change the range name:

You have to do that from the define name window.
Press Ctrl F3 (Or go to Insert | Names, Define...), select the named range that you want to modify, and change the "RefersTo" box. After, press the Add button to make sure that it gets "saved

 

[SkipVought]

Where does the SOURCE WORKBOOK come from?

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Bilberry]

Hi Skip I have put it in a local directory. I have changed the named range there and also the range of the cells (after deleting some records, the range wasnt set properly). I think this was the issue.

I have used also a pivottable to do some calculations. Im getting a run time error 1004, The pivot table field name is not valid. Here a short copy of the code

Sheets("Pivot").Select
ActiveWindow.SelectedSheets.Delete
Sheets.Add After:=ActiveSheet
ActiveSheet.Select
ActiveSheet.Name = "Pivot"
     
     
Worksheets(1).Select ' which contains the source records (querytable)
     
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:=ActiveSheet.UsedRange).CreatePivotTable _
TableDestination:="Pivot!R1C1", TableName:="PivotTable1", DefaultVersion:=xlPivotTableVersion10
    
Worksheets(4).Select

With ActiveSheet.PivotTables("PivotTable1").PivotFields("Kent")
        .Orientation = xlRowField
        .Position = 1
End With

' ....and so on....

It stops at line:

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:=ActiveSheet.UsedRange).CreatePivotTable _
TableDestination:="Pivot!R1C1", TableName:="PivotTable1", DefaultVersion:=xlPivotTableVersion10

Is there a smarter way to get this realized with VBA?

 

[SkipVought]

You have not answered my question. I'm trying to determine how your source workbook arrives with the data. It seems that you are creating unnecessary work for yourself by Naming the Range Qr. IS the table the only data on the sheet?

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[SkipVought]

Try this for your pivot table

'
    Application.DisplayAlerts = False
    Sheets("Pivot").Delete
    Application.DisplayAlerts = True
    
    Sheets.Add After:=ActiveSheet
    ActiveSheet.Name = "Pivot"
    
    With Sheets("Pivot")
        ActiveWorkbook.PivotCaches.Add( _
            SourceType:=xlDatabase, _
            SourceData:=Worksheets(1).UsedRange).CreatePivotTable _
            TableDestination:=.Range("A1"), _
            TableName:="PivotTable1"
                
        With .PivotTables("PivotTable1").PivotFields("Kent")
                .Orientation = xlRowField
                .Position = 1
        End With

    End With

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Bilberry]

Hi Skip,
Yes, the source workbook is send by the vendor through email. They will send it only once a month. January is the first. Normally, they should be the same. The workbook contains one worksheet. And the table is the only data on the sheet. The naming should also be the same, but i will check this again in february.