# Excel Macro to save individual sheets in a workbook to a value in a cell if not blank



## shawnb18 (Feb 25, 2008)

First of all, thank you for your help so far.

I have a workbook with multiple sheets and I would like to create a macro that saves each sheet into a seperate workbook and save that workbook as a value included in that sheet, but only if there that cell is not blank.

For example the logic would run something like this on each sheet in the main workbook;

If sheets in range B11 <> "", Save current sheet as c:\documents\invoices\B11.xls, do nothing

If the file name already exists I am assuming it will append a # to the end which is perfect.

I hope that makes since. If there is a beter way of doing this please enlighten me. I am basically creating a list of invoices on different sheets and on sheet 1 I am using it for the data entry which then transfers the data to the invoices in the same workbook however I need to save each sheet as a seperate invoice and not grouped together in 1 workbook. The sheets also need to be saved as the persons name which would be the value in B11.

Thank you again.


----------



## Glaswegian (Sep 16, 2005)

Hi again

This should work. Note that there is no error checking here and I've therefore added in the day and month to the filename. Excel will not add any characters for you if it finds another sheet with the same name - it will simply stop the code. Bear in mind that this loop through all sheets in your workbook each time you run it.

```
Sub SaveASheet()
Dim fName As String
Dim myPath As String
Dim sht As Worksheet

myPath = "C:\Documents\Invoices\"

For Each sht In ThisWorkbook.Worksheets
    If sht.Range("B11").Value <> "" Then
        sht.Copy
            With ActiveWorkbook
                .SaveAs myPath & ActiveSheet.Range("B11").Value & " " & Format(Date, "dd mmm") & ".xls"
                .Close
            End With
    End If
Next sht
            
End Sub
```
Post back with any queries.


----------



## shawnb18 (Feb 25, 2008)

Thank you! Seems perfect. I just picked up a book on VBscript and am trying to learn the language.


----------



## Glaswegian (Sep 16, 2005)

VBscript is slightly different from VBA (Visual Basic for Applications) but the basic ideas are the same - good luck!


----------



## shawnb18 (Feb 25, 2008)

Thank you! I am using VBscript for our website thats why I am trying to learn it. I guess it would be a good time to pickup some info on VBA as well. Thank you for help once again.


----------

