Active Window Question

[Guest]

The overall goal is for my macro to copy a sheet from the active workbook,
paste it in a new workbook created by the macro. The user will then be asked
to save the new workbook via the "save as" menu. Below is what I have so
far. If anyone has a different method, please let me know.

Option Explicit
Private Sub CreateInfoFile_Click()
Dim mod1name As String
Dim infoname As String
Dim newbook

'inserting full name of active file in Exported Sheet
Sheets("ExportedSheet").Range("B1").Value = mod1name

'Creating New Workbook
Set newbook = Workbooks.Add
With newbook
.SaveAs Filename:="Blank.xls"
End With

'Copy Exported Sheet to new file
Sheets("ExportedSheet").Copy Before:=Workbooks("Blank.xls").Sheets(1)
'*PROBLEM

'Deleting blank sheets
Sheets("Sheet1").Select
ActiveWindow.SelectedSheets.Delete
Sheets("Sheet2").Select
ActiveWindow.SelectedSheets.Delete
Sheets("Sheet3").Select
ActiveWindow.SelectedSheets.Delete
Sheets("Sheet4").Select
ActiveWindow.SelectedSheets.Delete

'Saving new file
Do
infoname = Application.GetSaveAsFilename
Loop Until infoname <> False
newbook.SaveAs Filename:=infoname

'Getting filename and pasting in Exported Sheet
Sheets("ExportedSheet").Range("B2").Value = infoname

End Sub

First, I create the newworkbook. No problem. Then I try and copy the sheet
to it. As far as I know, to do this you have to activate the workbook with
the "ExportedSheet" sheet. Since the name of this file will be changing, I
need to refer to it some other way than it's real name. (That is where I was
going with "mod1name".)

So, is there a way to activate a window using a variable name? (I know you
can open a file using a variable name.)

Thanks in advance.

-Chris

 

[Alan]

Chris,

As soon as you have created your new workbook save it with your chosen
name and then read the new name into a string variable which you can
then use to activate that workbook when each time that it is necesary.

dim NewwbName

NewwbName=ActiveWorkbook.name

Windows(NewwbName).Activate '(when required)


As far as content of your macro is concerned you could create a new
workbook with only a single sheet and then just copy the data across
rather than deleting all of the spare sheets.

Alan

 

[Alan]

CORRECTION Sorry ...

dim NewwbName as string

Alan

 

[Dave Peterson]

This look like it's code associated with a button from the Control toolbox
toolbar.

I'd use that newbook variable to refer to that new workbook. And I'd use either
me.parent (or ThisWorkbook) to refer to the workbook holding the code.

But I don't understand how/where mod1Name and infoname get changed.

But this did compile for me and may give you some thoughts on how to approach
it:

Option Explicit
Private Sub CreateInfoFile_Click()
Dim Mod1Name As String
Dim InfoName As Variant 'can be boolean False
Dim NewBook As Workbook

'inserting full name of active file in Exported Sheet
'mod1name is blank at this time!
'and why populate it in the original workbook--
'just wait and do it after the copy
'Sheets("ExportedSheet").Range("B1").Value = mod1name

'Creating New Workbook
Set NewBook = Workbooks.Add(1) 'single sheet only!
NewBook.Worksheets(1).Name = "deletemelater"

'Copy Exported Sheet to new file
ThisWorkbook.Sheets("ExportedSheet").Copy _
Before:=NewBook.Sheets(1)

'delete that dummysheet in the new workbook
Application.DisplayAlerts = False
NewBook.Worksheets("deletemelater").Delete
Application.DisplayAlerts = True

'shouldn't that cell be populated before the save?
With NewBook.Worksheets(1)
'mod1name = "something"
.Range("B1").Value = Mod1Name

'Saving new file
Do
InfoName = Application.GetSaveAsFilename
If InfoName = False Then
'keep going
Else
.Range("B2").Value = InfoName
.Parent.SaveAs Filename:=InfoName
Exit Do
End If
Loop
End With
End Sub

Again, it did compile, but I didn't test it.

 

[Guest]

Thanks Alan. I had done something similar but was calling the full name
instead of just the name. It is always something. Thanks again.

-Chris