Code copies worksheet to source workbook rather than other destination workbook

Status
Not open for further replies.

Ark68

Well-known Member
Joined
Mar 23, 2004
Messages
4,562
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
The code below opens a second workbook (if not already open), and creates, names and saves a third.

Upon creation of the third workbook, which is hidden, two worksheets are copied from the 1st (main) workbook to the newly created workbook (workbook 3).

But I am having problems copying the sheets to the 3rd workbook. With the line in red below, I encounter the error ""Cannot rename a sheet to the same name as another sheet...". It would appear that my code is attempting to copy the sheet and paste and rename it back to the same workbook instead of the newly created 3rd workbook.

Rich (BB code):
Sub master_worksheet()

    Dim wb_base As Workbook, wksh_book As Workbook
    Dim ws_masterwksh As Worksheet, ws_servicewksh As Worksheet, ws_vh As Worksheet, ws_core As Worksheet, ws_corestaff As Worksheet, wksh_book As Workbook, ws_wkservices As Worksheet
    Dim fac_rng As Range as range, rcore As Range
    Dim qfile2 As String, st_srchfn As String, dir_name As String, path2 As String, ws_name As String
    Dim vParts
    Dim norec As Long

   
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    
    Set ws_masterwksh = Workbooks("sports15b.xlsm").Worksheets("MasterWKSH")
    Set ws_servicewksh = Workbooks("sports15b.xlsm").Worksheets("ServicesWKSH")
    Set ws_vh = Workbooks("sports15b.xlsm").Worksheets("VAR_HOLD")
    Set fac_rng = Workbooks("Sports15b.xlsm").Worksheets("Facilities").Range("A:G")
    
    qfile2 = ws_vh.Range("B4")
    st_srchfn = "H:\PWS\Parks\Parks Operations\Sports\Sports15\DATA1\" & qfile2
    dir_name = Format(ws_vh.Range("B2"), "ddd dd-mmm-yy")
    path2 = "H:\PWS\Parks\Parks Operations\Sports\Sports15\WORKORDERS\" & dir_name
    ws_name = "WS " & Format(ws_vh.Range("B2"), "dd-mmm-yy") & ".xlsx"
    
    On Local Error Resume Next
    MkDir path2						    'create directory
    
    vParts = Split(st_srchfn, "\")			    'open datasource if not open already. Define workbook.
    On Error Resume Next
    Set wb_base = Workbooks(vParts(UBound(vParts)))
    If Err.Number Then Set wb_base = Workbooks.Open(st_srchfn)
    On Error GoTo 0
    
    On Error Resume Next				    'define datasource worksheets. Hide workbook.
    Windows(wb_base.Name).Visible = False
    On Error GoTo 0
    Set ws_core = wb_base.Worksheets("CORE")
    Set ws_corestaff = wb_base.Worksheets("Staff")

    norec = WorksheetFunction.Count(ws_core.Range("C:C"))   'last row in source (ws_core)
    Set rcore = ws_core.Range("A2:EE" & norec + 1)          'source range (ws_core)
    
    Set wksh_book = Workbooks.Add			    'create and save new workbook, hide it
    wksh_book.SaveAs Filename:=path2 & "\" & ws_name
    wksh_book.Windows(1).Visible = False
    
    With ws_servicewksh                                     'copy services worksheet from main book to book created above. Define new (services) worksheet.
        .Copy After:=Sheets(Sheets.Count)
        ActiveSheet.Name = "Services"
        Set ws_wkservices = wksh_book.Worksheets("Services")
    End With
    
    With ws_masterwksh                                      'copy master worksheet from main book to book created above. Define new (master) worksheet.
        .Copy After:=Sheets(Sheets.Count)
        ActiveSheet.Name = "Master"
        Set ws_wkmaster = wksh_book.Worksheets("Master")
    End With
    
    With wksh_book					    'delete redundant worksheets created during new workbook creation
        On Error Resume Next
        .Sheets("Sheet1").Delete
        .Sheets("Sheet2").Delete
        .Sheets("Sheet3").Delete
        On Error GoTo 0
    End With

    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
    wksh_book.Windows(1).Visible = True

End Sub
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
On it's own Sheets(Sheets.Count) will refer to the active workbook, try specifying which workbook Sheets(Sheets.Count) should reference.

For example if that workbook was the one created earlier and referred to as wksh_book.
Code:
  With ws_servicewksh                                     'copy services worksheet from main book to book created above. Define new (services) worksheet.
        .Copy After:=wksh_book.Sheets(Sheets.Count)
        wksh_book.ActiveSheet.Name = "Services"
        Set ws_wkservices = wksh_book.Worksheets("Services")
    End With
 
Upvote 0
Hi Norie, thank you for your help.

I made the changes as suggested, and am now experiencing a "Subscript out of range" error with the line in red.

Rich (BB code):
    With ws_servicewksh                                     'copy services worksheet from main book to book created above. Define new (services) worksheet.
        .Copy After:=wksh_book.Sheets(Sheets.Count)
        wksh_book.ActiveSheet.Name = "Services"
        Set ws_wkservices = wksh_book.Worksheets("Services")
    End With

The destination workbook has been created, but it is hidden.
 
Upvote 0
Oops, need a workbook reference for Sheets.Count too.
Code:
With wksh_book
    ws_servicewksh.Copy After:=.Sheets(.Sheets.Count)
    .ActiveSheet.Name = "Services"
    Set ws_wkservices = .Worksheets("Services")
End With
 
Upvote 0
OK,

Rich (BB code):
    With wksh_book                                     'create services worksheet
        ws_servicewksh.Copy After:=.Sheets(.Sheets.Count)
        .ActiveSheet.Name = "Services"
        Set ws_wkservices = .Worksheets("Services")
    End With
    
    With wksh_book                                      'create master worksheet
        ws_masterwksh.Copy After:=.Sheets(.Sheets.Count)
        .ActiveSheet.Name = "Master"
        Set ws_wkmaster = .Worksheets("Master")
    End With

Now results in "Method 'Copy' of object '_Worksheet' failed" errors with the lines in red.
 
Upvote 0
What happens if you don't hide wksh_book?
 
Upvote 0
Hi Norie,

With the sheet not hidden, it works.

But, I have had to rewrite this portion of code in order to keep it's activity out of sight of the user. I wish to keep the user focused on the application's main sheet ("DYNAMIC") and have the activity in this procedure kept in the background. I hope there is a way to still do this.
 
Upvote 0
Status
Not open for further replies.

Forum statistics

Threads
1,213,531
Messages
6,114,167
Members
448,554
Latest member
Gleisner2

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