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

[stevobo]

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

 

[afonsomira]

Hello.

Try to put only this on the error line.

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

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

 

[stevobo]

Hello.

Try to put only this on the error line.

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]

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]

To save to the downloads folder you can use

ActiveWorkbook.SaveAs Filename:=Environ("userprofile") & "\Downloads\" & Naam, FileFormat:=51, CreateBackup:=False

 

[stevobo]

To save to the downloads folder you can use

ActiveWorkbook.SaveAs Filename:=Environ("userprofile") & "\Downloads\" & Naam, FileFormat:=51, CreateBackup:=False

That's brilliant! It works. Many thanks.

 

[Fluff]

Glad we could help & thanks for the feedback.