I finally finished my latest Excel macro. (my second one ever).
It copies each sheet of my workbook onto a new sheet, converts it into values and format, shifts it to a new workbook and deletes the temp copy. One cycle for each sheet. Automated. Awesome. Took me long enough though.
Hey visual basic looks easier than I thought. Haha.
Also learnt that when you password protect an excel file, it cannot be size-reduced by zipping. Dang.
Option Explicit Sub Copier() Dim ws As Worksheet Dim wsNew As Worksheet Dim NewName As String Dim wsOriginalName As String 'On Error GoTo Errorcatch If MsgBox("1. Copy to new sheet. 2. Change to values. 3. Move to new workbook" & vbCr & _ "New sheets will be pasted as values, named ranges removed" _ , vbYesNo, "NewCopy") = vbNo Then Exit Sub ' Input box to name new file NewName = InputBox("Please Specify the month name of your new workbook", "New Copy") With Application .ScreenUpdating = False .DisplayAlerts = False 'iterate through all worksheets For Each ws In ThisWorkbook.Worksheets 'ignore hidden worksheets If ws.Visible = xlSheetVisible Then 'copy sheet within original workbook wsOriginalName = ws.Name ws.Copy After:=Sheets("FAQ") 'switch to copied sheet Set wsNew = ActiveSheet 'convert to values and format With wsNew.UsedRange .Copy .PasteSpecial xlPasteValuesAndNumberFormats .PasteSpecial xlPasteFormats .Cells(1, 1).Select End With 'save into new workbook wsNew.Copy ActiveWorkbook.SaveAs ThisWorkbook.Path & "" & "MIS-FY2013-" & NewName & "-" & wsOriginalName ActiveWorkbook.Close 'MsgBox ("going to try to delete: " & wsNew.Name) 'delete copied sheet wsNew.Delete End If Next ws End With End Sub