Excel exporting of worksheets in workbooks

Chaza

New Member
Joined
Jul 5, 2011
Messages
8
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
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

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
 
Upvote 0
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 #.
 
Upvote 0
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 ?
 
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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...
 
Upvote 0

Forum statistics

Threads
1,224,616
Messages
6,179,912
Members
452,949
Latest member
beartooth91

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top