# Code to Save Sheet with Sheet Name



## howard (Monday at 8:01 AM)

I have the sheet name in Cell AC2

I am looking for code to save the sheet with the same name as sw in AC2

I have this snippet of code



```
Sheets("Sales bR2").Copy
   
       With ActiveWorkbook
       Sheets("Sales BR2").Select
       
.SaveAs Environ("TMP") & "\" & ThisWorkbook.ActiveSheet.Range("AC2").Name & ".xlsx"
```


I need the .saveas Environ to be amended


----------



## HaHoBe (Monday at 8:14 AM)

hi howard,

I'm not sure I got your problem  right:


```
Sheets("Sales bR2").Copy
  
  With ActiveWorkbook
    .SaveAs Environ("TMP") & "\" & ActiveSheet.Range("AC2").Value & ".xlsx", FileFormat:=51
    .Close False
  End With
```

Holger


----------



## howard (Monday at 8:36 AM)

Many Thanks Holger

I have one more snipest of code that I need amended to attsch sheet to outlook


```
.Attachments.Add Environ("TMP") & "\" & ActiveSheet.Range("AB2").Name & ".xlsx", FileFormat:=51
```



```
With CreateObject("Outlook.Application").CreateItem(0)
        .To = Join(Application.Transpose(ActiveSheet.Range("AA1:AA6").Value), ";")
               .Subject = "Overaged Inventorty for : " & ThisWorkbook.ActiveSheet.Range("B1")

        .Body = Ztext
        
      .Attachments.Add Environ("TMP") & "\" & ActiveSheet.Range("AB2").Name & ".xlsx", FileFormat:=51
        .Display
```


----------



## HaHoBe (Monday at 8:54 AM)

Hi howard,

slightly alter the code to have a variable hold the path, name and extension of workbook and use that variable to save, attach and delete if wanted like


```
Dim strAtt As String
  '...
  
  '...
  Sheets("Sales bR2").Copy
  strAtt = Environ("TMP") & "\" & ActiveSheet.Range("AC2").Value & ".xlsx"
  With ActiveWorkbook
    .SaveAs strAtt, FileFormat:=51
    .Close False
  End With
  
  '...
  
  With CreateObject("Outlook.Application").CreateItem(0)
    .to = Join(Application.Transpose(ActiveSheet.Range("AA1:AA6").Value), ";")
    .Subject = "Overaged Inventorty for : " & ThisWorkbook.ActiveSheet.Range("B1")
    .Body = Ztext
    .Attachments.Add strAtt
    .Display
  End With
  '...
```

Ciao,
Hiolger


----------



## howard (Monday at 10:06 AM)

Many Thanks Holger

It makes so much sense doing it this way


----------



## howard (Thursday at 10:28 AM)

Hi Holger

I have a similar macro to email a sheet, howver the entire worbook is attached

I only want sheet "Comm_BR1" containing cells A:M115" to be attsched

The sheet to be copied must be saved used the  name in cell A2



```
Sub Email_BR1Comms()
Dim strAtt As String
Sheets("Comm_BR1").Select
Sheets("Comm_BR1").Range("A1:E40").Copy
strAtt = Environ("TMP") & "\" & ActiveSheet.Range("A2").Value & ".xlsx"
With ActiveWorkbook
   .SaveAs strAtt, FileFormat:=51
   Close False
 End With
             
        
Application.DisplayAlerts = True
With CreateObject("Outlook.Application").CreateItem(0)
 .To = Join(Application.Transpose(ActiveSheet.Range("M1:M6").Value), ";")
 .Subject = "Slaes Comms for : " & ThisWorkbook.ActiveSheet.Range("A2")
  .Body = Ztext
  Attachments.Add strAtt
  .Display
End With
End Sub
```

It would be appreciated if you could kindly aemnd my code


----------



## HaHoBe (Thursday at 11:25 AM)

Hi howard,

something like


```
Sub Email_BR1Comms_mod()
  Dim strAtt As String
  Dim Ztext As String
  
  '/// just to get some data into the body of the mail
  Ztext = "blablabla"
  
  '/// copy whole sheet, delete was is not needed
  Sheets("Comm_BR1").Copy
  ActiveSheet.Range("N1", Cells(1, Columns.Count)).EntireColumn.Delete
  ActiveSheet.Range("A115", Cells(Rows.Count, 1)).EntireRow.Delete
  
'  '///another possibility:
'  Workbooks.Add (xlWBATWorksheet)
'  '/// adjust the range as you indicated --A:M115"-- missing the row for Column A
'  ThisWorkbook.Sheets("Comm_BR1").Range("A1:M115").Copy
'  ActiveSheet.Range("A1").PasteSpecial xlPasteValues
  
  strAtt = Environ("TMP") & "\" & ActiveSheet.Range("A2").Value & ".xlsx"
  With ActiveWorkbook
    .SaveAs strAtt, FileFormat:=51
    '/// the code you posted missed the dot at the beginning
    .Close False
  End With
            
       
  Application.DisplayAlerts = True
  With CreateObject("Outlook.Application").CreateItem(0)
    '/// put in the sheet name for the next 2 codelines
    .To = Join(Application.Transpose(Sheets("Comm_BR1").Range("M1:M6").Value), ";")
    .Subject = "Sales Comms for : " & ThisWorkbook.Sheets("Comm_BR1").Range("A2")
    .Body = Ztext
    '/// the code you posted missed the dot at the beginning
    .Attachments.Add strAtt
    .Display
  End With
End Sub
```

Like mentioned in the code you should take a little more caution for leading dots on codelines when copying code (especially when it's inside a With...End With clause).

Holger


----------



## howard (Thursday at 11:44 AM)

Many Tanks Holger


----------



## howard (Thursday at 9:22 PM)

Hi Holger

My network at the office was down last night so could not test 

Have twsted this moerning and code works perectly

Many thanks for all your help. Much appreciated


----------



## HaHoBe (Yesterday at 4:33 AM)

Hi howard,

thanks for the update, happy to help.

Holger


----------

