Excel exporting of worksheets in workbooks

[Chaza]

Hi there

I have to work on exporting multiple excel worksheets into multiple workbooks, I don't think it is possible but I am also not too familiar with macros and how they work- I am still learning

Wondering if this is possible and if somebody could advise me on how and where to start looking to get me started

I have tried googling it but to no avail

Thanks

 

[AlphaFrog]

Excel VBA save worksheet as a workbook

 

[Chaza]

Excel VBA save worksheet as a workbook

Thank you for that, I didn't know it was even possible

tested and worked

This is the one I implemented:

Sub Macro5()
For i = 1 To ActiveWorkbook.Worksheets.Count
Sheets(i).Copy 'to new workbook
Set NewWks = ActiveSheet
With NewWks.Parent
Application.DisplayAlerts = False
.SaveAs Filename:="C:\Macros\" & NewWks.Name & ".xls"
Application.DisplayAlerts = True
.Close SaveChanges:=False
End With
Next i
MsgBox "Done!"

Range("H41").Select
ActiveWindow.Close
End Sub

is there a way I can have my workbook names as the name of the data contained in the second column
I was thinking I could use a starting range but the starting range of the desired cell is dependent on the amount of rows in the cell next to it


many thanks

 

[AlphaFrog]

You're welcome. I'm glad you found a solution.

is there a way I can have my workbook names as the name of the data contained in the second column
I was thinking I could use a starting range but the starting range of the desired cell is dependent on the amount of rows in the cell next to its

I don't follow what this means other than you want the file names to be from a column of cell values somewhere. That can be done.

Forum Tip: Pasting VBA code in the forum editor
It would be best if you surround your VBA code with code tags e.g [CODE]your VBA code here[/CODE]
It makes reading your VBA code much easier.
When you're in the forum editor, highlight your pasted VBA code and then click on the icon with the pound or number sign #.

 

[Chaza]

Yes that is exactly what I am trying to do

thanks

 

[Chaza]

You're welcome. I'm glad you found a solution.



I don't follow what this means other than you want the file names to be from a column of cell values somewhere. That can be done.

Forum Tip: Pasting VBA code in the forum editor
It would be best if you surround your VBA code with code tags e.g [CODE]your VBA code here[/CODE]
It makes reading your VBA code much easier.
When you're in the forum editor, highlight your pasted VBA code and then click on the icon with the pound or number sign #.

I am using report builder 2.0 to generate a report which is then exported to excel. The excel sheet can contain as much as 40 or more worksheets which with the help of this forum I was able to export into individual workbooks. However these workbooks read as sheet1, sheet2 up to 42 depending on how many worksheets they were

For example if my report contained information of students enrolled in a course, then it was grouped by student ID or individual student data, hence each individual worksheet contained individual student information

I export my worksheet into workbooks and now each individual workbook contains individual student data. I want the workbook to be named by the student name or ID when created instead of the sheet1
any thoughts ?

 

[AlphaFrog]

What is the name of the worksheet with the student names and what range of cells has their names?

Or does the student name occur in the same cell on their individual sheet?

 

[Chaza]

What is the name of the worksheet with the student names and what range of cells has their names?

Or does the student name occur in the same cell on their individual sheet?

Hi there
Student Names appear in column B throughout however cell range changes depending on number of rows in column c for instance if there is only one row in column c then student name appears in cell 2-4 if there are 3 or 4 rows in column c than student name begins in cell 3 and continues till 5 or 6

 

[AlphaFrog]

I want the workbook to be named by the student name or ID when created instead of the sheet1
any thoughts ?

I'm trying to answer your question but I need to know specifics on where can the macro get the student name for each of the worksheets.

I didn't understand this.

Student Names appear in column B throughout however cell range changes depending on number of rows in column c for instance if there is only one row in column c then student name appears in cell 2-4 if there are 3 or 4 rows in column c than student name begins in cell 3 and continues till 5 or 6

 

[Chaza]

I'm trying to answer your question but I need to know specifics on where can the macro get the student name for each of the worksheets.

I didn't understand this.

any change i can attach sample or snapshots?please let me know...