Copy Closed Workbook Sheet1 Not Retaining Worksheets("name")

[Shiseiji]

I'm working on creating a template workbook to create a worksheet with the current date and then bring in a previous day's report for comparison. Prior to the code below running, the workbook is renamed and sheet1 is renamed "User Accounts 10-31-18."

The closed workbook is a previous report so has sheet1 named "User Accounts 10-30-18" I'm not knowledgeable enough to select the worksheets to copy using a partial string so elected to use sheet1.
Abbreviated code after choosing and accessing the closed work:

Dim  otherwb As Workbook  
Dim  thiswb As Workbook   
Dim  otherws1 As Worksheet
    otherwb.Activate
    Set otherws1 = Sheet1
    otherws1.Activate
    otherws1.Copy After:=Thiswb.Sheets(Thiswb.Sheets.Count)

However instead of a worksheet named "User Accounts 10-30-18" the imported worksheet is named "User Accounts 10-31-18 (1)"

Options for fixing this?

TIA

Ron

 

[Logit]

.
The following macro will create a new sheet, from a TEMPLATE SHEET, and name it for the next date. You can edit the line of code (indicated at bottom) so the TAB NAME
is for the current date instead. Use this as a basis for your project and add your code for copying the data from the other workbook.

Option Explicit


Sub CreateNew()
Dim wsRecipes As Worksheet, wsTEMP As Worksheet, wasVISIBLE As Boolean
Dim shNAMES As Range, Nm As Range
Dim i As Long
Dim wsIndex
On Error Resume Next




With ThisWorkbook                                               'keep focus in this workbook
Application.CopyObjectsWithCells = False
    Set wsTEMP = .Sheets("Template")                            'sheet to be copied
    wasVISIBLE = (wsTEMP.Visible = xlSheetVisible)              'check if it's hidden or not
    If Not wasVISIBLE Then wsTEMP.Visible = xlSheetVisible      'make it visible
      
    Application.ScreenUpdating = False                          'speed up macro


    wsTEMP.Copy After:=.Sheets(.Sheets.Count)                   '...create it from template
    
    wsIndex.Activate                                            'return to the master sheet
    If Not wasVISIBLE Then wsTEMP.Visible = xlSheetHidden       'hide the template if necessary
    Application.ScreenUpdating = True                           'update screen one time at the end


Application.CopyObjectsWithCells = True
End With
Sheets(Sheets.Count).Select


Dim szTodayDate As String
szTodayDate = Format(Date + 1, "mm-dd-yy")                      'Edit this line to Format(Date, "mm-dd-yy")
ActiveSheet.Name = szTodayDate                                  'if you want the tab named for current date.
MsgBox "New sheet created. "
End Sub

 

[Shiseiji]

I must not have stated what I'm trying to do very clearly, sorry.
I've used my template to create a new file and then import the text for today's report. I name the worksheet with the current date.
I then select a previous report, usually the previous day, but not always, that also has worksheets with the date the report was created.
I'm attempting to copy the worksheets, always sheet & sheet2, from the selected report into the workbook I've just created with worksheets dated "today"

So in the end I want to have worksheets named |User Accounts 10-31-18| User Accounts 10-30-18|
But when I copy in sheet1 from the previous report, named |user Accounts 10-30-18|, I end up with |User Accounts 10-31-18| User Accounts 10-31-18(2)| not |User Accounts 10-30-18| as the worksheet is named in the other workbook.

Make any more sense? If I create a worksheet and copy in the values, then it becomes necessary to try to work with the other file's modified date or a date in the file name to put a date in the worksheet name. By dating the worksheets daily, I'm trying to avoid all that.

R

 

[Logit]

.
Ok ... from what I understand of your second post ...

Anytime you have a sheet named X, and you then import an entire sheet from another workbook with the same, it will be called X(1). If you import another sheet with the same name it will be named X(2). Excel
does that automatically ... there is nothing you can do to change that ... UNLESS you name the worksheet (new template sheet or the imported sheet) something different.

So, from the information you have provided so far .. you have two choices :

#1 - Import only the DATA from the other workbook and paste it into the named sheet you desire.

#2 - Do not have your TEMPLATE create a new sheet with the current or yesterday's date. Have the sheet you import from the other workbook be the only sheet with the date current or yesterday's date.

If I have misunderstood please explain further.

 

[Shiseiji]

Hummmm. Right now, I "thought" I was doing #2 ,

. . . Have the sheet you import from the other workbook be the only sheet with the date current or yesterday's date.

.

Only the workbook is a template. Once the VB is started, the workbook is renamed with the date, the form control button is deleted and an instrutional text box. Then after the two text files are imported, sheet1 is renamed from worksheets("User Accounts") to sheet1 / worksheets("User Accounts todays date") and sheet2 from worksheets("Admin Accounts") to sheet2 / worksheets("Admin Accounts todays date"). No other worksheets are created.

The user is then prompted to select a workbook from which to import sheet1 / worksheets("User Accounts past date") and sheet2 /worksheets("Admin Accounts past date")

Because the "past date" part of the worksheets("name") is different in every workbook, I had used the sheet code name. But the worksheets("User Accounts past date") name isn't being recognized as a unique name, rather only as sheet1 and sheet2 thus duplicating.

What makes #1 difficult is then the worksheet has to be manually named or try to work with either the other file's modified date property or a date in the file name to put a date in the worksheet name.

Appreciate your patience with this.

Ron

 

[Logit]

.
Post the code you are using to import the sheet to your TEMPLATE workbook.

 

[Logit]

.
Disregard .... let's cut to the chase ....

Here is a macro that will copy the data from Sheet1 of the Workbook named New_Workbook_Name.xlsx .

It then pastes that data (located in range A1:Z50) to Sheet2 of the active workbook (this would be your TEMPLATE workbook).
You'll need to edit the paths and workbook name copying from. I presume you are using the FileDialogOpen for the user to select which
workbook to copy from ? You'll need to insert that code into the macro to continue that functionality.

Sub Update()


    Dim wbk As Workbook
    Dim folderPath As String
    Dim Filename As String
    Dim wb As Workbook
    folderPath = "C:\Users\My\Desktop\" 'contains folder path
    If Right(folderPath, 1) <> "\" Then folderPath = folderPath + "\"
    Filename = Dir(folderPath & "New_Workbook_Name.xlsx")   '
    Do While Filename <> ""
        Application.DisplayAlerts = False
        Application.ScreenUpdating = False
        Set wb = Workbooks.Open(folderPath & Filename)
        Range("A1:Z50" & Range("A" & Rows.Count).End(xlUp).Row).Copy
        ThisWorkbook.Worksheets("Sheet2").Range("a" & Rows.Count).End(xlUp)(2).PasteSpecial
       
        Workbooks(Filename).Close True
        Filename = Dir
    Loop
    
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True


End Sub

 

[Shiseiji]

You can probably read what I'm trying to do better then I'm doing explaining it . . .

Sub m_Import_Past_AccountsXLSX() 'Import a previous AD report for comparison to the current report.
 ' https://www.mrexcel.com/forum/excel-questions/828654-visual-basic-applications-open-copy-paste-close.html
    '
    ' modified 10-31-18
    '
    Application.DisplayAlerts = False
    Application.DisplayStatusBar = False
    Application.ScreenUpdating = False
    '
    Dim Thiswb  As Workbook
    Dim otherwb As Workbook
    '
    Dim otherws1 As Worksheet
    Dim otherws2 As Worksheet
    '
    Dim fNameAndPath As Variant
    '
    Set Thiswb = ActiveWorkbook
    '
    ChDrive "S:"
    ChDir "\AD_Listing\"
    '
    '----------------------------------------------------------
    MsgBox ("Select a recent AD report to import.")
    
    Do
      fNameAndPath = Application.GetOpenFilename(fileFilter:="Excel Files (*.xls;*.xlsx),*.xls;*.xlsx", _
                     Title:="Select AD Report File To Be Opened")
    
    If fNameAndPath = "False" Then
        Exit Sub
    End If
    
    If InStr(fNameAndPath, "AD") = 0 Then
        MsgBox "You can only import an Active Directory report file.  Please select a recent file with 'AD...' in the file name."
    End If
    '
    Loop Until InStr(fNameAndPath, "AD") <> 0
    '
    '
    Set otherwb = Workbooks.Open(FileName:=fNameAndPath)
    otherwb.Activate
    Set otherws1 = Sheet1
    otherws1.Activate
    If otherws1.AutoFilterMode Then
       AutoFilterMode = False
    End If
    otherws1.Copy After:=Thiswb.Sheets(Thiswb.Sheets.Count)
    '
    otherwb.Activate
    Set otherws2 = Sheet2
    otherws2.Activate
    If otherws2.AutoFilterMode Then
       AutoFilterMode = False
    End If
    otherws2.Copy After:=Thiswb.Sheets(Thiswb.Sheets.Count)
    otherwb.Close SaveChanges:=False
    Thiswb.Activate
    Sheet1.Activate
    Call m_RemoveConnections
    Application.DisplayStatusBar = True
    Application.ScreenUpdating = True
End Sub

 

[Logit]

.
Yes, your macro is copying the Sheet and placing it after the existing sheets in your TEMPLATE workbook.

[COLOR=#333333]otherws1.Copy After:=Thiswb.Sheets(Thiswb.Sheets.Count)[/COLOR]

vs



this line of the example macro that copies a range in the sheet (it doesn't copy the entire sheet):

[COLOR=#333333]Range("A1:Z50" & Range("A" & Rows.Count).End(xlUp).Row).Copy[/COLOR]

THEN

it pastes that data into the master workbook (which would be your TEMPLATE workbook) Sheet2 with this line :

[COLOR=#333333]ThisWorkbook.Worksheets("Sheet2").Range("a" & Rows.Count).End(xlUp)(2).PasteSpecial[/COLOR]

If you know what cells you need to copy, you can specify that in the code. If you aren't certain, maybe because the range changes
sometimes, you can set the range to cover what you predict will be the maximum range. Example might be :

Range("A1:Z500") ... which would copy all the data from Column A to Z / from the 1st row down to the 500th row.
The only change you would need to make to your project is to copy the range you want, then paste it into the newly named sheet
you created in your TEMPLATE workbook.

 

[Shiseiji]

Thanks for all the time you put into helping me with this. What I find ironic is that it's possible to select the worksheets and copy them into the "Template" workbook retaining the worksheet names, but it can't be done with VBA.

To prevent having to hand jam the date into the worksheet names, I've decided to use a "helper cell" to capture the previous file's Last Modified Date and use it later to rename the worksheet.

Here's what I've got for anyone else who may need to do something similar.

Sub m_FileMdate()
    ' 11-1-2018
    ' https://excel.tips.net/T002285_Displaying_the_Last_Modified_Date.html
    '
    Application.DisplayAlerts = False
    Application.DisplayStatusBar = False
    Application.ScreenUpdating = False
    '
    Dim LastCol       As Integer
    Dim LastRow     As Long
    Dim rng            As Range
    Dim sLMD         As String
    Dim fs              As Variant
    Dim f               As Variant
    Dim ws            As Worksheet
'
    Set ws = ActiveSheet
    Set fs = CreateObject("Scripting.FileSystemObject")
    Set f = fs.GetFile(ActiveWorkbook.Path & "\" & ActiveWorkbook.Name)
    '
    LastRow = Cells(Cells.Rows.Count, "A").End(xlUp).Row
    LastCol = Cells(1, Columns.Count).End(xlToLeft).Column
    Cells(1, LastCol).Offset(0, 1).Name = "cel_fmDate"
    '
    sLMD = Left(f.DateLastModified, 10)
    With Range("cel_fmDate")
        .NumberFormat = "mm-dd-yyyy"
        .Value = sLMD
        .Copy
        .PasteSpecial xlPasteValues
    End With
    Application.CutCopyMode = False    
End Sub

Thanks again

Ron