Active Workbork Issue

[Denise Pollock]

I have a macro that takes a copy of one sheet in a workbook pastes the values
in a new sheet in a new workbook, then does a "save as" in a location on the
network. The macro works fine, however I have one user that keeps making the
macro malfunction. Because he is saving on a distant network it takes a
while for the macro to complete. I have ScreenUpdating and EnableEvents
turned off while the macro is running. Somehow, he is managing to activate
the main workbook while the macro is running which causes the entire
mainworkbook to be saved on the network, and he is left with book1 open his
computer. I have no idea how he is managing to do this as he assures me he
is not clicking anything. It's not the macro cause it is working fine for 10
other users. However, is there a way I can change it so it is specifying the
new workbook when it does the save and closes the file instead of using
active workbook?

Here is the code:

ActiveWorkbook.Save
Sheets("Sheet1").Select
Sheets("Sheet1").Copy

Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

DrawingObjects:=True, Contents:=True, Scenarios:=True

ActiveWorkbook.SaveAs Filename:= _
strSourceFolder & "\TEST.xls", _
FileFormat:=xlNormal, _
Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
CreateBackup:=False
ActiveWorkbook.Close

 

[Joshua Fandango]

Hi Denise,

This should sort you out...(with the code in your main workbook)

Sub Test()
Dim NewWbk As Workbook
Dim MainWbk As Workbook
Set MainWbk = ThisWorkbook
Workbooks.Add
Set NewWbk = ActiveWorkbook

With NewWbk
.SaveAs Filename:= _
strSourceFolder & "\TEST.xls", _
FileFormat:=xlNormal, _
Password:="", WriteResPassword:="",
ReadOnlyRecommended:=False, _
CreateBackup:=False
.Close
End With
MainWbk.Close False 'To close without saving changes if you want
End Sub

Declaring the individual workbooks & then setting their values means
they can then be referenced specifically.

HtH,
JF.

 

[Denise Pollock]

That is not doing what I want it to. If I use this then it switches to the
new workbook before it is even created which makes a blank workbook get
saved. And then I end up with the workbook I wanted to save still open on my
desktop as book1. I need a macro that names the new workbook but does not
create it, the macro I am using already creates a new workbook.

 

[Jim Thomlinson]

Try something more like this... It uses a workbook object to specifically
identify the workbooks you are working with... Note that I got rid of the
pastespecial as it is not needed. You can just stet the values equal...

Sub Test()
Dim wbkNew As Workbook

ThisWorkbook.Save
Sheets("Sheet1").Copy
Set wbkNew = ActiveWorkbook
wbkNew.Sheets(1).UsedRange.Value = wbkNew.Sheets(1).UsedRange.Value

'wbkNew.SaveAs Filename:=strSourceFolder & "\TEST.xls", _
FileFormat:=xlNormal, _
Password:="", WriteResPassword:="",
ReadOnlyRecommended:=False, _
CreateBackup:=False
wbkNew.Close

End Sub

 

[Denise Pollock]

That appears to work. I won't know for sure until I update this for my user
with the problem.

Thanks,

 

[Joshua Fandango]

Hi Denise,

It wasn't supposed to be the answers to end all answers, but to point
you in the right direction and show you the methods to use - feel free
to adapt them however you choose.

JF

 

[Denise Pollock]

Well it worked for me but it still did not work for my problem user. I had
to modify it for use in a different document that has to copy 2 sheets, so
maybe I messed it up?

What it should be doing is taking the open workbook, saving it, copying both
sheets to a new workbook, selecting the sheet "master" and saving it at the
specified location, then closing the new workbook. When he runs the macro
his original is being saved on the network with an copy of one of the sheets
but not both and then closed, and he is left with book1 open on his computer
with the copies of the sheets.

ThisWorkbook.Save
ChDir strSourceFolder
Dim First As Boolean
Dim sht As Variant
Dim wbkNew As Workbook
First = True
For Each sht In ThisWorkbook.Sheets
If First = True Then
'copy sheet into new workbook
sht.Copy
Set wbkNew = ActiveWorkbook
First = False
Else
sht.Copy after:=wbkNew.Sheets(wbkNew.Sheets.Count)
End If
Next sht
wbkNew.Sheets(2).UsedRange.Value = wbkNew.Sheets(2).UsedRange.Value
Sheets("Master").Select

wbkNew.SaveAs Filename:=strSourceFolder & "\FLGShip.xls", _
FileFormat:=xlNormal, _
Password:="", WriteResPassword:="",
ReadOnlyRecommended:=False, _
CreateBackup:=False
wbkNew.Close

 

[Denise Pollock]

Well it worked for me but it still did not work for my problem user. I had
to modify it for use in a different document that has to copy 2 sheets, so
maybe I messed it up? Although the one I had for copying 1 sheet does not
work for him either.

What it should be doing is taking the open workbook, saving it, copying both
sheets(Master, Sorted) to a new workbook, selecting the sheet "master" and
saving it at the specified location with the name FLGShip.xls, then closing
the new workbook. When he runs the macro his original is being saved at the
location with an extra copy of the sorted sheet and then closed, and he is
left with book1 open on his computer with the copies of the sheets.

ThisWorkbook.Save
ChDir strSourceFolder
Dim First As Boolean
Dim sht As Variant
Dim wbkNew As Workbook
First = True
For Each sht In ThisWorkbook.Sheets
If First = True Then
'copy sheet into new workbook
sht.Copy
Set wbkNew = ActiveWorkbook
First = False
Else
sht.Copy after:=wbkNew.Sheets(wbkNew.Sheets.Count)
End If
Next sht
wbkNew.Sheets(2).UsedRange.Value = wbkNew.Sheets(2).UsedRange.Value
Sheets("Master").Select

wbkNew.SaveAs Filename:=strSourceFolder & "\FLGShip.xls", _
FileFormat:=xlNormal, _
Password:="", WriteResPassword:="",
ReadOnlyRecommended:=False, _
CreateBackup:=False
wbkNew.Close