excel to excel, excel to word

nancyo

Active Member
Joined
Mar 25, 2002
Messages
255
I am looking for a simple code to do the following:

1. Copy ONE spreadsheet from an excel workbook into a new workbook.

2. Create the new workbook as part of the macro.

3. Save the new workbook as part of the macro.

4. The saved filepath will be different only in the FILENAME, which somehow must be referenced from the original excel file (possibly from a specific cell reference from a different spreadsheet).

5. Fantasy macro - do the same to word.

I have tried several variations on macros offerend in this website and others, and can't get anything to work. I am not a programmer at all, but have been able to modify other macros in the past to work.

Any help is appreciated!!!!!!!
 
I would define ws as a worksheet and fname as a string in my loop, like the following:

Code:
Dim ws As Worksheet, fname As String
For Each ws In ThisWorkbook.Worksheets
fname = ws.Name 'now save files as fname & ".xls"
Next

Hope this helps.

_________________
Cheers,<font size=+2><font color="red"> Nate<font color="blue">O</font></font></font>
This message was edited by NateO on 2002-04-30 11:55
 
Upvote 0

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.
OK- I got it to save a new workbook using each subsequent worksheet's tab name. But it saved sheet 1 under each name instead of saving every sheet with each name.

I believe the code problem resides in Sheets(1).Copy. I tried Sheets.Copy and got a complete copy of the whole workbook under each sheet name...

Thanks again---this will be a real time saver!
 
Upvote 0
I think you'll need to incorporate the following into your macro, taking advatage of the definitions in place:

Code:
Sub test6()
Dim ws As Worksheet, fname As String
For Each ws In ThisWorkbook.Worksheets
ws.Copy
fname = ws.Name
Set ws = nothing
Next
End Sub

Where you copy ws.



_________________
Cheers,<font size=+2><font color="red"> Nate<font color="blue">O</font></font></font>
This message was edited by nateo on 2002-05-01 08:45
 
Upvote 0
PUNT!

I've tried to incorporate the code into my macro and can't get it to work... Better to start fresh?

I have a master workbook of financial statements for 15 individuals. I'd like to copy each of the 15 worksheets to its own workbook and save that new workbook as the person's name (located on the worksheet tab of the master).

Sorry to be such a know nothing but I'm learning thanks to you!
 
Upvote 0
ni ine has approached the excel to word bit,

suggest looking at samples.xls in iffice ... the file give VBA to do this ..
 
Upvote 0
ExecEd, and I thought we were getting closer with my choppy advice. I've taken the liberty of compiling my choppy advice, hope this helps:

Code:
Sub alShts()
Dim ws As Worksheet, fname As String
ChDrive ("C") ' drive letter
ChDir ("c:temp") ' target directory
For Each ws In ThisWorkbook.Worksheets
fname = ws.Name
ws.Copy
ActiveWorkbook.Close True, Filename:=fname & ".xls"
Next ws
End Sub
No need to worry, glad to hear you're learning. Make sure the directory in your vba module shows sing s (where this post may show 2 or 1). Hope this helps.

_________________
Cheers,<font size=+2><font color="red"> Nate<font color="blue">O</font></font></font>
This message was edited by nateo on 2002-04-30 14:36
 
Upvote 0
GOT IT!!!!!

Sub CopyWorksheettoNewFile()
Dim ws As Worksheet, fname As String
current = 1
For Each Worksheet In ThisWorkbook.Worksheets
Set ws = Sheets(current)
fname = ws.Name 'now save files as fname & ".xls"
ws.Copy 'Copy first sheet to a new workbook
'On Error GoTo errorhandler
ChDrive ("C") ' drive letter
ChDir ("c:statements") ' target directory
If fname <> "" Then 'make sure fname is not blank
ActiveWorkbook.SaveAs Filename:=fname & ".xls"
Else: MsgBox "Please Enter a value in A1 and retry."
End If
Windows("FY 2002 200204.xls").Activate
'Exit Sub
'errorhandler:
'MsgBox "Can 't find your target directory"
current = current + 1
If current > 15 Then Exit For
Next
End Sub
 
Upvote 0
Ed, this looks good, couple of comments:
Code:
ChDrive ("C") ' drive letter 
ChDir ("c:statements") ' target directory
<pre>-You may want this above your loop so as not too repeat this command for each worksheet</pre>

Code:
If fname<> "" Then 'make sure fname is not blank 
ActiveWorkbook.SaveAs Filename:=fname & ".xls" 
Else: MsgBox "Please Enter a value in A1 and retry."
<pre>-You can't have a blank worksheet tab (I think), so there's no need for this if statement</pre>

Job well done. Glad to be of help. Have a good one.

_________________
Cheers,<font size=+2><font color="red"> Nate<font color="blue">O</font></font></font>
This message was edited by NateO on 2002-04-30 14:59
 
Upvote 0
NateO: I tried your code, keeps stopping at

sheets("name")? 'Run time error 9'

Any suggestions?
 
Upvote 0

Forum statistics

Threads
1,213,563
Messages
6,114,329
Members
448,564
Latest member
ED38

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