Probs with File Save as prompt

Perksy_no1

Well-known Member
Joined
Oct 27, 2011
Messages
598
Office Version
  1. 365
Platform
  1. Windows
Hi there,

The other day I found the following code on this site which I've but in ThisWork Workbook_Open module to prompt the user to save the file as some else before they start using it, which is working fine.

Code:
Dim fNameAndPath As Variant
fNameAndPath = Application.GetSaveAsFilename(InitialFileName:="", FileFilter:="Excel Files (*.XLS), *.XLS", Title:="Save As")
If fNameAndPath = False Then Exit Sub
Me.SaveAs Filename:=fNameAndPath

I've then tryed to put it in another work book and im now coming up with the error "Complie Error: Invalid use of Me keyword"

I've tried putting this code in a standard module as per below

Code:
Sub MoveContainerKeyedSheet()
Set ThisWkb = ThisWorkbook
Set NewBook = Workbooks.Add

ThisWkb.Sheets("Containers").Copy Before:=NewBook.Sheets(1)
    With NewBook
    On Error Resume Next
        .Sheets("Sheet1").Delete
        .Sheets("Sheet2").Delete
        .Sheets("Sheet3").Delete
        .Sheets("Sheet4").Delete
        .Sheets("Containers").Select
        ActiveSheet.Buttons.Delete
    End With
Dim fNameAndPath As Variant
fNameAndPath = Application.GetSaveAsFilename(InitialFileName:="", FileFilter:="Excel Files (*.XLS), *.XLS", Title:="Save As")
If fNameAndPath = False Then Exit Sub
Me.SaveAs fileName:=fNameAndPath
 
End Sub

Not sure where im going wrong so any help would be much appreciated

Thanks in advance

Mark

[/code]
 

Some videos you may like

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,406
When used in 'ThisWorkbook', the keyword 'Me' represents the object 'ThisWorkbook'. However, a standard module does not represent an object. Therefore, the keyword 'Me' cannot be used. Try replacing...

Code:
[FONT=monospace]Me.SaveAs fileName:=fNameAndPath[/FONT]

with

Code:
[FONT=monospace]NewBook[/FONT][FONT=monospace].SaveAs fileName:=fNameAndPath[/FONT]
 

Perksy_no1

Well-known Member
Joined
Oct 27, 2011
Messages
598
Office Version
  1. 365
Platform
  1. Windows
Brilliant! thanks very much works a treat and I've learnt something new today :)
 

Watch MrExcel Video

Forum statistics

Threads
1,122,689
Messages
5,597,559
Members
414,156
Latest member
WDMix

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
Top