ActiveWorkbook.SaveAs question(s)

[Rick S.]

When I use "ActiveWorkbook.SaveAs" in the following code I lose focus from
the macro it originated from .

'=========
With ActiveSheet
sSheetName = ActiveSheet.Name
If ActiveSheet.Name = "Sheet1" Then
GoTo EndIt
End If
Sheets(sSheetName).Select
Sheets(sSheetName).Copy
ActiveWorkbook.SaveAs Filename:= _
sNewItem & "\Template ISO " & sSheetName & " Audit mm.dd.yy.xlsx", _
FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False, WriteResPassword:="2000"
'=========

It seems any commands I use after the above the macro will stop processing
unless I use a msgbox prompt right after and that only works under certain
variables used or always if I use a string like msgbox "ok"?
After clicking on the msgbox prompt the macro appears to continue on. There
are no errors being reported.

My intention is to create a new workbook (wb) from an existing wb, add a
worksheet to the new wb, rename the worksheet in the new wb and continue on
with more code but I can not get past this stumbling block.

--
Regards

Rick
XP Pro
Office 2007

 

[Joel]

Use THISWORKBOOK to access data from the macro where the code is running
from. Open workbooks switches focus to the new workbook. After the workbook
is open I usally put the following statement to allow me to go back and forth
between THISWORKBOOK and the new workbook

workbooks.open filename:= filename
set newwbk = activeworkbook

'some code working on newly opened book

thisworkbook.activate

'some more code

newworkbook.activate

 

[FSt1]

hi
something odd about your code.
you select a sheet then copy that sheet but don't do anthing with the copied
data.
?!?!
try this....

Sheets(sSheetName).Select
Sheets(sSheetName).Copy
Workbooks.Add '*********
Range("A1").PasteSpecial xlPasteAll '*********
ActiveWorkbook.SaveAs Filename:= _
sNewItem & "\Template ISO " & sSheetName & " Audit mm.dd.yy.xlsx", _
FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False, _
WriteResPassword:="2000"
Activeworkbook.close '*********

Adding a workbook would make the new workbook the active workbook and when
you close the active workbook, your macro book should still be open and
running.

untested. using xp here.

Regards
FSt1

 

[Rick S.]

Thank you Joel and FSt1 for your replies.
It turns out you have to be very specific where your code goes to move from
sheet to sheet.
I simply had to work out where the code should be after activating a
worksheet.

--
Regards

Rick
XP Pro
Office 2007