Activate new 'save as' sheet

Spartan300

Board Regular
Joined
Jul 1, 2008
Messages
71
Hello,

I have written some code that activates a new excel sheet then saves it as..

Code:
ActiveWorkbook.SaveAs "C:\Documents\Saved Reports\Monthly Report/Monthly Report Emailed on " & Format(Date, "dd mm yyyy") & ".xls"

I then need to activate this sheet later on in the code. I know the code to activate it...
Code:
Windows("Book1").Activate
etc.

But my question is, with a variable in the file name (Format(Date,"dd mm yyyy") how can I successfully reference the newly saved sheet to activate it?

Thanks for your help.
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
Greetings,

Is the ActiveWorkbook a different workbook than ThisWorkbook? That is to say, different than the wb the code is in?
 
Upvote 0
Hi GTO,

Yes the code is not in the Activeworkbook. The Activeworkbook command refers to the newly opened Excel sheet that has been opened by VBA.

The code is in a different sheet to this.

Thanks
 
Upvote 0
try this you had / it should be \
Code:
Sub test_this()
ActiveWorkbook.SaveAs "G:\Documents\Saved Reports\Monthly Report\Monthly Report Emailed on " & Format(Date, "dd mm yyyy") & ".xls"
End Sub
 
Upvote 0
Hi GTO,

Yes the code is not in the Activeworkbook. The Activeworkbook command refers to the newly opened Excel sheet that has been opened by VBA.

The code is in a different sheet to this.

Thanks


Hi Spartan,

When you open the workbook, set a reference to it. This way you have control over it as an object, rather than depending on what workbook is currently active. Something like (use your path and filename)

Code:
Option Explicit
Sub ex()
Dim wbOne As Workbook
    Set wbOne = Workbooks.Open(Filename:=ThisWorkbook.Path & _
                                         Application.PathSeparator & _
                                         "OldName.xls")
                                         
    wbOne.SaveAs Filename:=ThisWorkbook.Path & _
                                         Application.PathSeparator & _
                                         "NewName.xls"
    
End Sub

Hope this helps,

Mark
 
Upvote 0
Thats great, thanks very much!

I had wondered about that but didn't know how I could carry it out.

Thanks
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,044
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top