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
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