Saving Multiple tabs to Excel

[Keval Khichadia]

Hi ,

 

I have a data file that looks like this

 

ID    Major         AppliedforGraduation

 

1        Art                Yes

2        Art                Yes

3        Bio                Yes       

4        Bio                Yes   

5        Math              No   

6        Math                Yes   

7        Math                No

 

 

I would like to save this to an excel spreadhseet with each major on a separate tab.

 

The only way I know how to do it is:

 

temp.

select if major = 'Art'.

save translate

/connect = ' '

/type = ''

/table = 'Art'.

 

I would have to do that for 20 or so majors and was wondering if there was a better way.

 

 

Thanks in advance,

 

Keval

 

   

 

[Wilhelm Landerholm | Queue]

Hi Keval!

 

Why not do it in Excel?

 

I wrote a little macro that splits one Worksheet to many based on column A.

 

Just remember to sort the data on column A.

 

Option Explicit

 

Sub SPLIT_BY_C1()

Dim wb As Excel.Workbook

Dim ws1 As Excel.Worksheet

Dim ws2 As Excel.Worksheet

Dim i, j, k As Long

 

Set wb = ActiveWorkbook

Set ws1 = wb.ActiveSheet

 

i = 2

j = 1

k = 2

 

aa:

If Len(ws1.Cells(1, j)) = 0 Then GoTo bb:

j = j + 1

GoTo aa:

bb:

 

j = j - 1

 

Set ws2 = wb.Worksheets.Add

ws2.Name = ws1.Cells(i, 1)

ws2.Range(ws2.Cells(1, 1), ws2.Cells(1, j)).Value = ws1.Range(ws1.Cells(1, 1), ws1.Cells(1, j)).Value

 

cc:

If Len(ws1.Cells(i, 1)) = 0 Then GoTo dd:

 

ws2.Range(ws2.Cells(k, 1), ws2.Cells(k, j)).Value = ws1.Range(ws1.Cells(i, 1), ws1.Cells(i, j)).Value

 

If ws1.Cells(i, 1) <> ws1.Cells(i + 1, 1) And Len(ws1.Cells(i + 1, 1)) <> 0 Then

Set ws2 = wb.Worksheets.Add

ws2.Name = ws1.Cells(i + 1, 1)

k = 1

ws2.Range(ws2.Cells(1, 1), ws2.Cells(1, j)).Value = ws1.Range(ws1.Cells(1, 1), ws1.Cells(1, j)).Value

End If

 

k = k + 1

i = i + 1

GoTo cc:

 

dd:

 

End Sub

 

All the best

 

Wilhelm Landerholm

Queue Sweden AB / STATB.COM – http://www.statb.com

[phone]

 

Från: SPSSX(r) Discussion [mailto:[hidden email]] För Keval Khichadia
Skickat: den 20 april 2009 23:50
Till: [hidden email]
Ämne: Saving Multiple tabs to Excel

 

Hi ,

 

I have a data file that looks like this

 

ID    Major         AppliedforGraduation

 

1        Art                Yes

2        Art                Yes

3        Bio                Yes       

4        Bio                Yes   

5        Math              No   

6        Math                Yes   

7        Math                No

 

 

I would like to save this to an excel spreadhseet with each major on a separate tab.

 

The only way I know how to do it is:

 

temp.

select if major = 'Art'.

save translate

/connect = ' '

/type = ''

/table = 'Art'.

 

I would have to do that for 20 or so majors and was wondering if there was a better way.

 

 

Thanks in advance,

 

Keval