Copy sheet from .xlsm to xlsx and rename copied sheet in xlsx to contents of a cell

[agentkramr]

Basically titler says it all, i have an xlsm that is retieving data from Oracle it waits for the refresh to occur, copies the page and should paste it to a workbook xlsx and name that page to the contents of a defined cell.

Sub Copy()
'Refresh Workbook
Workbooks("COUNT 2022").RefreshAll
'Open a workbook

  'Open method requires full file path to be referenced.
  Workbooks.Open "\\filepapth\TEST\2022 2 Week Count.xlsx"
  
  Workbooks("COUNT 2022.xlsm").Activate
  
  Sheets("New").Copy After:=Workbooks("2022 2 Week Count.xlsx").Sheets(Workbooks("2022 2 Week Count.xlsx").Sheets.Count)
    ActiveSheet.Range("A1:AC84").Copy
    ActiveSheet.Range("A1:AC84").PasteSpecial xlPasteValues
    'ActiveSheet.Shapes("Rectangle: Rounded Corners 1").Delete
    'ActiveSheet.Shapes("Rectangle: Rounded Corners 2").Delete
    Dim Val As String
    Val = Sheets("New").Range("AI1").Value
        
    'Columns("AF:AI").Delete

    ActiveWorkbook.Save
    ActiveWorkbook.Close
End Sub

everything works except renaming the sheet after the cell contents the contents is just todays date which is just named month then day( AUG 26). it still has the original sheet name and i get a runtime 1004 application defined or object defined error,
if i hit debug it takes me to the line

ActiveSheet.Name = Val

 

[agentkramr]

Sub Copy()
    Dim WB1 As Workbook, WB2 As Workbook
    Dim WS2 As Worksheet
    Dim Val As String, TmpStr As String, FilePath As String

    'Assign "Copy-From" workbook. Presumed to be already open
    On Error Resume Next
    Set WB1 = Application.Workbooks("COUNT 2022.xlsm")    ' "Copy-From" workbook
    On Error GoTo 0

    If WB1 Is Nothing Then
        MsgBox "Workbook :'" & "COUNT 2022.xlsm" & "' is not open", vbOKOnly Or vbExclamation, "Workbook not found"
        Exit Sub
    End If

    'Refresh Workbook
    WB1.RefreshAll

    'Open "Copy-To" workbook. Open method requires full file path to be referenced.
    FilePath = "\\filepapth\TEST\2022 2 Week Count.xlsx"    'specify workbook to be opened.

    'Make sure it is a valid file
    With CreateObject("Scripting.FileSystemObject")
        If Not .FileExists(FilePath) Then
            MsgBox "File not found:" & vbCr & FilePath, vbOKOnly Or vbExclamation, "File name Error"
            Exit Sub
        End If
    End With

    'Open workbook
    Set WB2 = Workbooks.Open(Filename:=FilePath)      ' "Copy-To" workbook

    'Copy sheet from "Copy-From" workbook to "Copy-To" workbook
    WB1.Sheets("New").Copy After:=WB2.Sheets(WB2.Sheets.Count)
    Set WS2 = WB2.Sheets(WB2.Sheets.Count)

    'Convert any formulas to values
    WS2.Range("A1:AC84").Value = WS2.Range("A1:AC84").Value

    'debug
    Dim S As String, R As Range
    S = "WB1 = " & WB1.Name & vbCr
    S = S & "WB2 = " & WB2.Name & vbCr & vbCr
    With WB1.Worksheets("New").Range("AI1")
        S = S & .Address(0, 0, , 1) & " = " & .Text & vbCr
    End With
    With WS2.Range("AI1")
        S = S & .Address(0, 0, , 1) & " = " & .Text & vbCr & vbCr
    End With
    Val = WS2.Range("AI1").Text
     S = S & "Val = " & Val
    MsgBox S
    ' end debug

    'Assign new sheet name
    TmpStr = WS2.Name
    Val = WS2.Range("AI1").Text

    'Test for validity
    On Error Resume Next
    WS2.Name = Val
    On Error GoTo 0

    If WS2.Name = TmpStr Then
        Select Case MsgBox("Error - Sheet name '" & Val & _
                           "' is invalid or already exists" & vbCr & vbCr & _
                           "Continue with file save operation?", vbYesNo, Application.Name)
        Case vbNo
            WB2.Close SaveChanges:=False
            Exit Sub
        End Select
    End If

    'Save and close  "Copy-To" workbook
    WB2.Close SaveChanges:=True
End Sub

thank you !here is a screen snip of the error, also when i execute this the sheet probably wont be open since i am summoning it from a VBscript so it probably doesnt need the check if the sheet is open

 

[rlv01]

Change this

    Val = WS2.Range("AI1").Text

to this

    Val = WB1.Worksheets("New").Range("AI1").Text

 

[agentkramr]

Change this

    Val = WS2.Range("AI1").Text

to this

    Val = WB1.Worksheets("New").Range("AI1").Text

this is the new error

 

[rlv01]

The new graphic suggests that you only replaced one out of the two instances. Some questions:

1. Do you know how to set breakpoints in the VBA editor/debugger?
2. Do you know how to use the VBA editor/debugger to single-step through your code?
3. Do you know what the Immediate Window is and how to use it?

 

[agentkramr]

The new graphic suggests that you only replaced one out of the two instances. Some questions:

1. Do you know how to set breakpoints in the VBA editor/debugger?
2. Do you know how to use the VBA editor/debugger to single-step through your code?
3. Do you know what the Immediate Window is and how to use it?

my bad i didnt realize there were 2 entries to it
i do know how to set breakpoints and have set them i believe
i do know how to use single step
dont know anything about the immediate window
this is the new error after fixing the other entry

 

[rlv01]

So does the sheet get renamed now?

 

[agentkramr]

So does the sheet get renamed now?

no it still has its original name and pops with that error i last posted

 

[rlv01]

no it still has its original name and pops with that error i last posted

The last thing you posted was the debug message box, not an error message. What was the error message? According to the info in the debug window, it should have renamed the sheet to "Aug 31" - unless the workbook already had a sheet named that, in which case you would get an error message.

 

[rlv01]

If the variable Val = "Aug 31" , then the worksheet should be renamed to that UNLESS you already have a worksheet with that name. I've tested that code using the same cell AI1 value of Aug 31 as you use and it works for me without any error.

My recommendation is that you use the debugger to set a watch on these three variables

Val​

TmpStr​

WS2.Name​

Then use the debugger to single-step execution (F8) while watching to see how those variables change. The goal is to see why the new worksheet is not being renamed.

Some references:

How to debug VBA code:​

VBA: How to Debug Code​

​

How to use the Immediate window:​

5 Ways to Use the VBA Immediate Window - Excel Campus​

 

[agentkramr]

If the variable Val = "Aug 31" , then the worksheet should be renamed to that UNLESS you already have a worksheet with that name. I've tested that code using the same cell AI1 value of Aug 31 as you use and it works for me without any error.

My recommendation is that you use the debugger to set a watch on these three variables

Val​

TmpStr​

WS2.Name​

Then use the debugger to single-step execution (F8) while watching to see how those variables change. The goal is to see why the new worksheet is not being renamed.

Some references:

How to debug VBA code:​

VBA: How to Debug Code​

​

How to use the Immediate window:​

5 Ways to Use the VBA Immediate Window - Excel Campus​

here is a shot with the whole thing, it doesnt rename the sheet as you can see, in theory if it were functioning it would say Sep 01 as that is the value in AI1. Or is there another way to make it name the tab name itself to todays date with that format ? format in Excel is MMM DD