excel to excel, excel to word

[nancyo]

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!!!!!!!

[Anne Troy]

Do we know the name of the original files?

[NateO]

Howdy, #5 is a bit out of my reach but here's a stab at 1-4:

Sub fsy()
fname = Sheets(2).[a1].Value 'set filename as cell a1's value on 2nd sheet
Sheets(1).Copy 'Copy first sheet to a new workbook
'On Error GoTo errorhandler
ChDrive ("C") ' drive letter
ChDir ("c:temp") ' 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
'Exit Sub
'errorhandler:
'MsgBox "Can 't find your target directory"
End Sub

Hope this helps.

_________________
Cheers, [html-disabled] Nate

[ This Message was edited by: nateo on 2002-04-29 15:05 ]

[Chris Davison]

[html-disabled] Nate

Get well soon Nate !!!!!

[NateO]

Feelin' better already Chris, thank-you. Just wanted single s, makes copying code easier for the OP.

_________________
Cheers, NateO

[ This Message was edited by: NateO on 2002-04-29 14:37 ]

[nancyo]

Dreamboat: Yes, the original file will have a different filepath than the second file. The second workbook is intended for another department, all they need is one worksheet (which is protected). They mostly use Word, so ideally I want to be able to automate the copy step so one of my techs doesn't have to manually do it (and make mistakes). I can teach them how to look up the worksheet in excel for now.

NateO: I will look at your code this am and reply with questions.

Thanks!!!

[ExecEd]

This has been helpful as I have a similar situation.

How can I modify the posted macro so that:
1. The process loops for each of worksheet in the workbook.
2. The Save as name is cell A8 of the current worksheet?

Thanks!

[ExecEd]

Got the current sheet name figured out---just the loop remains as the question...

[NateO]

Perhaps the following loop will help:

For Each worksheet In ThisWorkbook.Worksheets
'your code
Next

_________________
Cheers, NateO

[ This Message was edited by: NateO on 2002-04-30 11:48 ]

[ExecEd]

Thanks---that gets the loop going---
Looks like my efforts on naming the subsequent file as the worksheet name didn't work. How do you save the newly created file the same name as the worksheet tab?