PDA

View Full Version : Req: Combo with 5 options



TDR
07-31-2006, 11:40 AM
Windows XP Pro:
Excel 2002:

Hi there, I hope someone can help with my query.

I have a workbook that contains around 120 worksheets, all with different names, and would like a way, preferably via a combo box on a form, to allow one of five options:

1 - An option to remove/export a named sheet, or the active sheet, to an archive file stored within the same folder on the drive.
2 - An option to copy the sheet to a new Excel file and give the user an option of where to store it on their drive - I think i have this one...
Application.ScreenUpdating = False
ActiveSheet.Copy
On Error Resume Next
Range("Print_Area").Copy
ActiveSheet.PasteSpecial Paste:=xlPasteValues
Application.Dialogs(xlDialogSaveAs).Show
ActiveWindow.Close False
Application.ScreenUpdating = True

3 - An option copy the sheet to a new Word file and give the user an option of where to store it on their drive - I think i have this...

ActiveSheet.Range("A4:W76").Copy
Set wdApp = CreateObject("Word.Application")
wdApp.Documents.Open "C:\Documents and Settings\TDR\My Documents\ExcelTests\the.doc"
wdApp.Selection.GoTo What:=wdGoToPage, Which:=wdGoToNext, Name:="2"
wdApp.Selection.Paste
Application.CutCopyMode = False
wdApp.Visible = True

4 - An option to copy the sheet as a new bitmap/JPEG file and give the user an option of where to store it on their drive
5 - An option to rename the active sheet

Sorry if this appears a bit jumbled or too much,

Thanks,

TDR :)