Combining 3 Macros into 1 - Save As Cell Names

[Helen842000]

Hi all,

I've been working on this for a little while now and it is probably obvious but I'm having trouble combining 3 pieces of VB script to into one routine.

The context is that a team regularly have to take an excel workbook and go through several stages to process it.

They have to...

1) Unprotect the workbook with a known password
2) Copy one worksheet out (it has a fixed name) as current values into a new workbook of its own and save in a network location
3) This file is also saved with a file name that comes from cell text - stored in D4 and B2 combined.

Needless to say doing this multiple times per day is frustrating & prone to error.

I seem to have each step running individually but each time I try to combine them I get various errors that leads to me just making it worse.

So far for each respective macro I have :-

1) To unprotect the sheet with a known password
ActiveSheet.Unprotect "password"

2) Copy a worksheet as values to a new workbook of it's own and save in a fixed network location
Dim wbNew As Workbook

Application.DisplayAlerts = False

Worksheets("SheetName").Copy

Set wbNew = ActiveWorkbook

With wbNew

With .Worksheets(1).UsedRange
.Value = .Value
End With

.SaveAs "C:\Folder\Folder\Folder SheetName.xls"

.Close True

End With

Application.DisplayAlerts = True

3) Save as a filename that comes from D4 and B2 combined.
Dim part1 As String
Dim part2 as String


part1 = Range("D4").Value
part2 = Range("B2").Value


ActiveWorkbook.SaveAs Filename:= _
"C:\Folder\Folder\Folder\" & part1 & "-" & part2 & ".xlsm", FileFormat:= _
xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False


If anyone has any thoughts on how to combine these together or simplify it in any way that would be really appreciated.

Thanks!

 

[Helen842000]

Thinking about it further. I don't really need much of the 3rd macro.

I just need to modify the 2nd to reference D4 and B2 instead of calling it SheetName.xlsm

Any thoughts?