How can I change my code to get the ActiveWorkbook.Save as to stop failing?

stevobo

New Member
Joined
Aug 7, 2013
Messages
8
Hi all,

I have been trying to get a macro to work to copy a sheet from one workbook to a new book, copy formulas 'as values en finally save the file to the temp folder with a name based on certain content in the sheet.
For some reason in continues to fail at the same point (of saving). Can you please help?

The code is currently as per below:

Sub ResourceSheet_Voorbereiden()
'
' ResourceSheet_Voorbereiden
'
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Sheets("Resource dashboard").Select
Sheets("Resource dashboard").Copy
Range("F5").Select
Selection.Hyperlinks.Delete
Range("F5:J123").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
ActiveSheet.Shapes.Range(Array("Gebruikt in ConOps 1", "Cluster 1", "Categorie 1", "CONOPS 1",
"Beheerder 1", "Type resource 1", "Knop 1", "Knop 2")).Select
Selection.Delete

Sheets("Resource dashboard").Select
Sheets("Resource dashboard").Copy After:=Sheets(1)
Range("A1").Select
Worksheets("Resource dashboard (2)").Visible = False

Sheets("Resource dashboard").Select
Range("F10:J123").Select
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=NIET(F7='Resource dashboard (2)'!F7)"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Font
.Color = -16777024
.TintAndShade = 0
End With
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 13421823
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
Range("A1").Select
Application.DisplayAlerts = True

Dim Naam As String
Naam = "BCM Resource Overview " & Range("F6") & ".xlsx"

Application.DisplayAlerts = False

'Now the above all works, but when trying to save the code fails!

ActiveWorkbook.SaveAs Filename:="C:\Windows\Temp" & Naam, FileFormat:=51, CreateBackup:=False


Application.DisplayAlerts = True
Application.ScreenUpdating = True

End Sub
 

Some videos you may like

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

afonsomira

Board Regular
Joined
Aug 11, 2020
Messages
62
Office Version
  1. 365
Platform
  1. Windows
Hello.

Try to put only this on the error line.

VBA Code:
ActiveWorkbook.SaveAs Filename:="C:\Windows\Temp" & Naam

Or maybe you don´t have privilege to save on that folder.
 

stevobo

New Member
Joined
Aug 7, 2013
Messages
8
Hello.

Try to put only this on the error line.

VBA Code:
ActiveWorkbook.SaveAs Filename:="C:\Windows\Temp" & Naam

Or maybe you don´t have privilege to save on that folder.
It could indeed be related to the fact that I am not allowed to save to that folder... Is there a way to change to location to your downloads folder in such a way that it will not just work on my computer?

Tnx,
 

afonsomira

Board Regular
Joined
Aug 11, 2020
Messages
62
Office Version
  1. 365
Platform
  1. Windows
To work on more computers it is better to save the file on the network.

If you save on your downloads folder will only work on the local machine.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,626
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

To save to the downloads folder you can use
VBA Code:
ActiveWorkbook.SaveAs Filename:=Environ("userprofile") & "\Downloads\" & Naam, FileFormat:=51, CreateBackup:=False
 

stevobo

New Member
Joined
Aug 7, 2013
Messages
8
To save to the downloads folder you can use
VBA Code:
ActiveWorkbook.SaveAs Filename:=Environ("userprofile") & "\Downloads\" & Naam, FileFormat:=51, CreateBackup:=False
That's brilliant! It works. Many thanks.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,626
Office Version
  1. 365
Platform
  1. Windows
Glad we could help & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,738
Messages
5,626,606
Members
416,195
Latest member
tonmcg

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