Changing Worksheet Naming to Cell Value

[ben33]

Hi
im trying to change the macro to save the workbook under the sheet number displayed in cell A30. This is what i have now:

>>> use code - tags <<<

Sub NewWBandPasteSpecialALLSheets()
   Dim wb As Workbook
   Dim wbNew As Workbook
   Dim sh As Worksheet
   Dim shNew As Worksheet

   Set wb = ThisWorkbook
   Workbooks.Add ' Open a new workbook
   Set wbNew = ActiveWorkbook

   On Error Resume Next

   For Each sh In wb.Worksheets
      sh.Range("A1:O100").Copy

      'add new sheet into new workbook with the same name
      With wbNew.Worksheets

          Set shNew = Nothing
          Set shNew = .Item(sh.Name)

          If shNew Is Nothing Then
              .Add After:=.Item(.Count)
              .Item(.Count).Name = sh.Name
              Set shNew = .Item(.Count)
              Sheet1.Visible = xlSheetVisible
          End If
       
      End With

      With shNew.Range("A1")
        .PasteSpecial xlPasteAll
        .PasteSpecial xlPasteColumnWidths
      End With
     
   Next
         wbNew.Worksheets("Sheet1").Visible = xlVeryHidden

   wbNew.SaveAs Filename:="D:\Dropbox\Reports\" & folname & "\" & "DAILY REPORTS.xlsx", FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False

End Sub

 

[Marc L]

Hi,​

​

thanks to edit your post to use the code tags via this icon :​

​

 

[ben33]

Sub NewWBandPasteSpecialALLSheets()
Dim wb As Workbook
Dim wbNew As Workbook
Dim sh As Worksheet
Dim shNew As Worksheet

Set wb = ThisWorkbook
Workbooks.Add ' Open a new workbook
Set wbNew = ActiveWorkbook

On Error Resume Next

For Each sh In wb.Worksheets
sh.Range("A1:O100").Copy

'add new sheet into new workbook with the same name
With wbNew.Worksheets

Set shNew = Nothing
Set shNew = .Item(sh.Name)

If shNew Is Nothing Then
.Add After:=.Item(.Count)
.Item(.Count).Name = sh.Name
Set shNew = .Item(.Count)
Sheet1.Visible = xlSheetVisible
End If

End With

With shNew.Range("A1")
.PasteSpecial xlPasteAll
.PasteSpecial xlPasteColumnWidths
End With

Next
wbNew.Worksheets("Sheet1").Visible = xlVeryHidden

wbNew.SaveAs Filename:="D:\Dropbox\Reports\" & folname & "\" & "DAILY REPORTS.xlsx", FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False

End Sub
R

 

[Marc L]

Try wbNew.SaveAs "D:\Dropbox\Reports\" & folname & "\" & [A30].Text, xlOpenXMLWorkbook …​

​

Your variable folname may be an issue if empty.​

​

 

[ben33]

Try wbNew.SaveAs "D:\Dropbox\Reports\" & folname & "\" & [A30].Text, xlOpenXMLWorkbook …

The codes above are messing with the workbook saving. I actually need the worksheet workaround, i trust for codes below:

'add new sheet into new workbook with the same name
      With wbNew.Worksheets

          Set shNew = Nothing
          Set shNew = .Item(sh.Name)

          If shNew Is Nothing Then
              .Add after:=.Item(.Count)
              .Item(.Count).Name = sh.Name
              Set shNew = .Item(.Count)
              Sheet1.Visible = xlSheetVisible
          End If

Pls assist tq