1

I am working on hard coding a file, SaveCopyAs, and then changing the active workbook to the newly created file so i can mail out the file without any formulas, etc. later on in the code. I have tried Workbook.Activate but cant seem to get the correct file from my own code.

Thanks for the help!

Sheets("Send").Visible = True
Sheets.Select
Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
ActiveSheet.Paste
Application.CutCopyMode = False

Dim thisWb As Workbook, d As Integer

Set thisWb = ActiveWorkbook
d = InStrRev(thisWb.FullName, ".")
'ActiveWorkbook.SaveAs Filename:=Left(thisWb.FullName, d - 1) & "-Prelims" & 
Mid(thisWb.FullName, d)
Sheets("Send").Visible = False
ActiveWorkbook.SaveCopyAs Filename:=Left(thisWb.FullName, d - 1) & "- Prelims" & Mid(thisWb.FullName, d)
ActiveWorkbook.Close savechanges:=False
Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
Michael Gallagher
  • 17
  • 4

1 Answers1

1

After you save a copy of the file, you need to open it:

At this line, revise:

ActiveWorkbook.SaveCopyAs Filename:=Left(thisWb.FullName, d - 1) & "- Prelims" & Mid(thisWb.FullName, d)
ActiveWorkbook.Close savechanges:=False

Add these statements before those lines, let's capture the output filename in a string variable:

Dim newFileName as String
newFileName = Left(thisWb.FullName, d - 1) & "- Prelims" & Mid(thisWb.FullName, d)

And then we can do:

thisWb.SaveCopyAs Filename:=newFileName  ' use thisWb instead of ActiveWorkbook

Finally, open the new one:

Dim newWorkbook as Workbook
Set newWorkbook = Workbooks.Open(newFileName)

Putting it all together:

Dim newFileName as String
' build the filename parmaeter:
newFileName = Left(thisWb.FullName, d - 1) & "- Prelims" & Mid(thisWb.FullName, d)
thisWb.SaveCopyAs Filename:=newFileName
thisWb.Close savechanges:=False

' Open the new workbook:
Dim newWorkbook as Workbook
Set newWorkbook = Workbooks.Open(newFileName)
David Zemens
  • 53,033
  • 11
  • 81
  • 130
  • Thanks @David Zemens, This worked! Will any code I apply after this pertain to the new workbook? e.g. mailing out the file, highlighting cells or will i need to add newFileName.Activate? – Michael Gallagher Feb 28 '19 at 16:56
  • 1
    Opening the workbook will automatically make it the *active* workbook, so the short answer is "yes". The long answer is that if you want to be 100% sure (and this is more important when you are working between several workbooks/worksheets) you should *always* scope your variables properly, and avoid relying on things like `ActiveWorkbook`, `ActiveSheet`, `Selection`, and unqualified objects (e.g., `Cells(1,1)` or `Range("A1")` which use the *active* sheet by default. – David Zemens Feb 28 '19 at 17:02
  • [This question](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) goes in to more detail about how & why you should avoid implicit references. It's a good habit to get into :) – David Zemens Feb 28 '19 at 17:03