A very useful macro is to make a new book from a page in your current book. The scenerio is this you take some information using formulas and look ups and now you need to save it specifying a new name, clear the old sheet, then increment the save name to be unique next save.
So how to do. One thing you need is the new name. You can use a generated cell to create the name with this code. Perhaps you use a customers last name and then incremented value to combine into cell a1.
Dim sCopyName As String
sCopyName = Range("a1")
Now lets copy a section of the sheet with the data on it to put in a new book.
Range("a10:l49").Select
Selection.Copy
Workbooks.Add
Activesheet.Paste
ActiveWorkbook.saveas Filename:=sCopyName, _
FileFormat:=xlOpenXMLWorkbookMacroEnabled
ActiveWorkbook.Save
ActiveWorkbook.Close
I added the extra save just in case you want to add some format changes. Once closed it returns to your old book. The increment is just one line:
Range("a1").Value = Range("a1").Value + 1
You can change a1 to any number and then it will increment each time you run the code. You also might want to empty the active sheet a bit with this code showing some different clearing methods.
Range("E8").Select
Selection.ClearContents
Now lets put it all together
Sub Post()
'
' Post Macro
'
Dim sCopyName As String
sCopyName = Range("a1")
Range("a10:l49").Select
Selection.Copy
Workbooks.Add
ActiveSheet.Paste
ActiveWorkbook.SaveAs Filename:=sCopyName, _
FileFormat:=xlOpenXMLWorkbookMacroEnabled
ActiveWorkbook.Save
ActiveWorkbook.Close
Range("E8").Select
Selection.ClearContents
Range("a1").Value = Range("a1").Value + 1
End Sub
If this helps please comment. If you would like to know more about my Excel consulting see my design website: http://hilbrandsdesign.com/
Comments