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

A

agentkramr

Board Regular
Joined
Dec 27, 2021
Messages
98
Platform
  1. Windows
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.

VBA Code: 
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
VBA Code: 
ActiveSheet.Name = Val
 
Sort by date Sort by votes
agentkramr said:
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
Click to expand...

Here is the situation. I have simulated your data, filenames, and file locations as closely as I can. I have used that to test run the code I posted. It works. It gets "Val" from cell AI1 and successfully renames the worksheet to the value stored in variable Val. Let me say that again: everything you want the code to do, it does for me when I run it.

The debug message box you posted, this box here:

1662062958455.png


Tells me that the variable Val is successfully getting the value from cell AI1. Note that at the point in the code where the debug window pops up, the sheet renaming as not taken place yet. That window is just to confirm that all the pieces are in place. If you press "Ok" then either the sheet will be renamed, or you will get this error message:

1662063733507.png


Since you have not mentioned this error message in your last few posts, it means it did not occur and so the sheet should have been renamed.

At this point, I'm thinking that you may not have incorporated all the changes I've posted, so my recommendation is for you to re-post your entire Sub Copy here so I can review it.
 
Upvote 0

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
rlv01 said:
Here is the situation. I have simulated your data, filenames, and file locations as closely as I can. I have used that to test run the code I posted. It works. It gets "Val" from cell AI1 and successfully renames the worksheet to the value stored in variable Val. Let me say that again: everything you want the code to do, it does for me when I run it.

The debug message box you posted, this box here:

View attachment 73049

Tells me that the variable Val is successfully getting the value from cell AI1. Note that at the point in the code where the debug window pops up, the sheet renaming as not taken place yet. That window is just to confirm that all the pieces are in place. If you press "Ok" then either the sheet will be renamed, or you will get this error message:

View attachment 73051

Since you have not mentioned this error message in your last few posts, it means it did not occur and so the sheet should have been renamed.

At this point, I'm thinking that you may not have incorporated all the changes I've posted, so my recommendation is for you to re-post your entire Sub Copy here so I can review it.
Click to expand...
If i click yes it will copy the sheet to the book but the copied sheet keeps its origianl name instead of naming it to the value of AI1 here is the VBA
VBA Code: 
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 = "\\serverpath\2022 2 Week Counts\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("2018 1 Hour Counts").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("2018 1 Hour Counts").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 = WB1.Worksheets("2018 1 Hour Counts").Range("AI1").Text
     S = S & "Val = " & Val
    MsgBox S
    ' end debug

    'Assign new sheet name
    TmpStr = WS2.Name
    Val = WB1.Worksheets("2018 1 Hour Counts").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
 
Upvote 0
rlv01 said:
Here is the situation. I have simulated your data, filenames, and file locations as closely as I can. I have used that to test run the code I posted. It works. It gets "Val" from cell AI1 and successfully renames the worksheet to the value stored in variable Val. Let me say that again: everything you want the code to do, it does for me when I run it.

The debug message box you posted, this box here:

View attachment 73049

Tells me that the variable Val is successfully getting the value from cell AI1. Note that at the point in the code where the debug window pops up, the sheet renaming as not taken place yet. That window is just to confirm that all the pieces are in place. If you press "Ok" then either the sheet will be renamed, or you will get this error message:

View attachment 73051

Since you have not mentioned this error message in your last few posts, it means it did not occur and so the sheet should have been renamed.

At this point, I'm thinking that you may not have incorporated all the changes I've posted, so my recommendation is for you to re-post your entire Sub Copy here so I can review it.
Click to expand...
is there also a way to not have the box prompt as i am trying to fully automate this procedure
 
Upvote 0
Try this:
VBA Code: 
Sub Copy()
    Dim WB1 As Workbook, WB2 As Workbook
    Dim WS As Worksheet, WS2 As Worksheet
    Dim Val As String, TmpStr As String, FilePath As String
    Dim DelCnt As Long

    '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 = "\\serverpath\2022 2 Week Counts\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("2018 1 Hour Counts").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

    'Assign new sheet name
    TmpStr = WS2.Name
    Val = WB1.Worksheets("2018 1 Hour Counts").Range("AI1").Text

    For Each WS In WB2.Worksheets
        If WS.Name = Val Then
            Application.DisplayAlerts = False
            WS.Delete                                 'delete any existing sheet with the same name so that the rename operation will succeed.
            DelCnt = DelCnt + 1
            Application.DisplayAlerts = True
        End If
    Next WS

    '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 (" & DelCnt & ")" & vbCr & vbCr & _
                           "Debug:" & vbCr & "   TmpStr = '" & TmpStr & "'" & vbCr & "   Val = '" & Val & "'" & vbCr & vbCr & _
                           "Continue with file save operation?", vbYesNo + vbCritical, "Invalid File Name Error")
        Case vbNo
            WB2.Close SaveChanges:=False
            Exit Sub
        End Select
    End If

    'Save and close  "Copy-To" workbook
    WB2.Close SaveChanges:=True
End Sub
 
Upvote 0
rlv01 said:
Try this:
VBA Code: 
Sub Copy()
    Dim WB1 As Workbook, WB2 As Workbook
    Dim WS As Worksheet, WS2 As Worksheet
    Dim Val As String, TmpStr As String, FilePath As String
    Dim DelCnt As Long

    '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 = "\\serverpath\2022 2 Week Counts\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("2018 1 Hour Counts").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

    'Assign new sheet name
    TmpStr = WS2.Name
    Val = WB1.Worksheets("2018 1 Hour Counts").Range("AI1").Text

    For Each WS In WB2.Worksheets
        If WS.Name = Val Then
            Application.DisplayAlerts = False
            WS.Delete                                 'delete any existing sheet with the same name so that the rename operation will succeed.
            DelCnt = DelCnt + 1
            Application.DisplayAlerts = True
        End If
    Next WS

    '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 (" & DelCnt & ")" & vbCr & vbCr & _
                           "Debug:" & vbCr & "   TmpStr = '" & TmpStr & "'" & vbCr & "   Val = '" & Val & "'" & vbCr & vbCr & _
                           "Continue with file save operation?", vbYesNo + vbCritical, "Invalid File Name Error")
        Case vbNo
            WB2.Close SaveChanges:=False
            Exit Sub
        End Select
    End If

    'Save and close  "Copy-To" workbook
    WB2.Close SaveChanges:=True
End Sub
Click to expand...
i get a debug box
click debug
the third pic is a snip of the tabs of the workbook it open to paste and rename to date
 

Attachments

  • Capture.PNG
    Capture.PNG
    4.4 KB · Views: 6
  • Capture1.PNG
    Capture1.PNG
    17.9 KB · Views: 7
  • Image.png
    Image.png
    2.8 KB · Views: 5
Upvote 0
Sorry, that code had a test statement I forgot to take out.

VBA Code: 
Sub Copy()
    Dim WB1 As Workbook, WB2 As Workbook
    Dim WS As Worksheet, WS2 As Worksheet
    Dim Val As String, TmpStr As String, FilePath As String
    Dim DelCnt As Long

    '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 = "\\serverpath\2022 2 Week Counts\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("2018 1 Hour Counts").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

    'Assign new sheet name
    TmpStr = WS2.Name
    Val = WB1.Worksheets("2018 1 Hour Counts").Range("AI1").Text

    For Each WS In WB2.Worksheets
        If WS.Name = Val Then
            Application.DisplayAlerts = False
            WS.Delete                                 'delete any existing sheet with the same name so that the rename operation will succeed.
            DelCnt = DelCnt + 1
            Application.DisplayAlerts = True
        End If
    Next WS

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

'begin debug
MsgBox "Debug:" & vbCr & "   Val = '" & Val & "'" & vbCr & vbCr & _
"   TmpStr = '" & TmpStr & "'" & vbCr & vbCr & _
"   WS2.Name = '" & WS2.Name & "'" & vbCr & vbCr & _
"   Parent = '" & WS2.Parent.Name _
, vbInformation, "Debug Message"
'end debug

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

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

For the automation error, consider rebooting your PC.
 
Upvote 0
rlv01 said:
Sorry, that code had a test statement I forgot to take out.

VBA Code: 
Sub Copy()
    Dim WB1 As Workbook, WB2 As Workbook
    Dim WS As Worksheet, WS2 As Worksheet
    Dim Val As String, TmpStr As String, FilePath As String
    Dim DelCnt As Long

    '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 = "\\serverpath\2022 2 Week Counts\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("2018 1 Hour Counts").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

    'Assign new sheet name
    TmpStr = WS2.Name
    Val = WB1.Worksheets("2018 1 Hour Counts").Range("AI1").Text

    For Each WS In WB2.Worksheets
        If WS.Name = Val Then
            Application.DisplayAlerts = False
            WS.Delete                                 'delete any existing sheet with the same name so that the rename operation will succeed.
            DelCnt = DelCnt + 1
            Application.DisplayAlerts = True
        End If
    Next WS

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

'begin debug
MsgBox "Debug:" & vbCr & "   Val = '" & Val & "'" & vbCr & vbCr & _
"   TmpStr = '" & TmpStr & "'" & vbCr & vbCr & _
"   WS2.Name = '" & WS2.Name & "'" & vbCr & vbCr & _
"   Parent = '" & WS2.Parent.Name _
, vbInformation, "Debug Message"
'end debug

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

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

For the automation error, consider rebooting your PC.
Click to expand...
i still isnt naming the tab correctly when it copies to the other workbook, it should be naming it the contents of AI1 instead it is naming it what the original sheet is named
 

Attachments

  • Capture.PNG
    Capture.PNG
    2.8 KB · Views: 5
Upvote 0
rlv01 said:
Sorry, that code had a test statement I forgot to take out.

VBA Code: 
Sub Copy()
    Dim WB1 As Workbook, WB2 As Workbook
    Dim WS As Worksheet, WS2 As Worksheet
    Dim Val As String, TmpStr As String, FilePath As String
    Dim DelCnt As Long

    '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 = "\\serverpath\2022 2 Week Counts\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("2018 1 Hour Counts").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

    'Assign new sheet name
    TmpStr = WS2.Name
    Val = WB1.Worksheets("2018 1 Hour Counts").Range("AI1").Text

    For Each WS In WB2.Worksheets
        If WS.Name = Val Then
            Application.DisplayAlerts = False
            WS.Delete                                 'delete any existing sheet with the same name so that the rename operation will succeed.
            DelCnt = DelCnt + 1
            Application.DisplayAlerts = True
        End If
    Next WS

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

'begin debug
MsgBox "Debug:" & vbCr & "   Val = '" & Val & "'" & vbCr & vbCr & _
"   TmpStr = '" & TmpStr & "'" & vbCr & vbCr & _
"   WS2.Name = '" & WS2.Name & "'" & vbCr & vbCr & _
"   Parent = '" & WS2.Parent.Name _
, vbInformation, "Debug Message"
'end debug

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

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

For the automation error, consider rebooting your PC.
Click to expand...
also instead of it copying the formatting and contents of the cells i get this.

contents of a cell looks like this
=SUMIF('C:\Users\me\Desktop\[COUNT 2022.xlsm]ATTENDANCE'!$B:$B,'2018 1 Hour Counts'!B7,'C:\Users\me\Desktop\[COUNT 2022.xlsm]ATTENDANCE'!$J:$J)
 

Attachments

  • Capture.PNG
    Capture.PNG
    35.4 KB · Views: 5
Upvote 0
agentkramr said:
i still isnt naming the tab correctly when it copies to the other workbook, it should be naming it the contents of AI1 instead it is naming it what the original sheet is named
Click to expand...

That is as it should be. The copy operation and the renaming operation are two separate steps.

1. First the sheet is copied to the new workbook . Code = WB1.Sheets("2018 1 Hour Counts").Copy After:=WB2.Sheets(WB2.Sheets.Count).
2. Then the copied worksheet in the new workbook is renamed. Code = WS2.Name = Val
 
Upvote 0
agentkramr said:
also instead of it copying the formatting and contents of the cells i get this.

contents of a cell looks like this
=SUMIF('C:\Users\me\Desktop\[COUNT 2022.xlsm]ATTENDANCE'!$B:$B,'2018 1 Hour Counts'!B7,'C:\Users\me\Desktop\[COUNT 2022.xlsm]ATTENDANCE'!$J:$J)
Click to expand...
Replace this:

VBA Code: 
    'Copy sheet from "Copy-From" workbook to "Copy-To" workbook
    WB1.Sheets("2018 1 Hour Counts").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

with this:

VBA Code: 
    'Copy sheet from "Copy-From" workbook to "Copy-To" workbook
    With WB1.Sheets("2018 1 Hour Counts")
        .Copy After:=WB2.Sheets(WB2.Sheets.Count)
        Set WS2 = WB2.Sheets(WB2.Sheets.Count)

        'Convert any formulas to values
         WS2.Range("A1:AC84").Value = .Range("A1:AC84").Value
    End With
 
Upvote 0
Solution
You must log in or register to reply here.

Similar threads

S
  • Question Question
Conditional Formatting changes from XLSM to XLSX when I use VBA code to save a new file
Replies
1
Views
481
JerrExcel
JerrExcel
A
  • Question Question
Copy workbook sheet to a network located workbook then rename page to todays date
Replies
2
Views
552
agentkramr
A
A
  • Solved
Sheet Copy/Rename VBA Error
Replies
6
Views
599
Joe4
Joe4
A
  • Question Question
Macro that refreshes the workbook, copies the sheet to a network workbook and changes the name in the copied to workbook to todays date
Replies
1
Views
524
agentkramr
A
B
  • Question Question
VBA exports worksheet from XLSM to XLXS matching all source properties doesn't keep print area
Replies
0
Views
128
brentcole
B

Forum statistics

Threads
1,224,822
Messages
6,181,165
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top