+ Reply to Thread
Results 1 to 4 of 4

excel VBA - setting sheet names within workbook copy using existing wb sheet names

Hybrid View

  1. #1
    Forum Contributor
    Join Date
    02-25-2013
    Location
    Colorado, US
    MS-Off Ver
    Microsoft 365 Apps
    Posts
    518

    excel VBA - setting sheet names within workbook copy using existing wb sheet names

    Hello,
    I am getting a subscript out of range error when trying to create a workbook copy. I don't understand why this isn't working. It seems pretty legit to me.
    Sub Export()
    
        Dim wb as Workbook, twb As Workbook 'target or new workbook
        Dim sh As Worksheet, tws As Worksheet
        
            Set wb = ThisWorkbook
    
            With Application
                .ScreenUpdating = False
                .DisplayAlerts = False
                .EnableEvents = False
            End With
            
            Workbooks.Add
            Set twb = ActiveWorkbook
            
            For Each sh In wb.Worksheets
                sh.Range("A1").CurrentRegion.Copy
    
                With twb.Worksheets
                    Set tws = Nothing
                    Set tws = .Item(sh.Name) 'subscript out of range
                    If tws Is Nothing Then
                        .Add after:=.Item(.Count)
                        .Item(.Count).Name = sh.Name
                        Set tws = .Item(.Count)
                    End If
                End With
                
    '            With tws.Range("A1")
    '                .PasteSpecial (xlPasteColumnWidths)
    '                .PasteSpecial (xlFormats)
    '                .PasteSpecial (xlValues)
    '            End With
            Next
            
            twb.Activate
            twb.CheckCompatibility = False
            twb.SaveAs fileName:="somefile.xlsx", FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
            twb.Close
    
            With Application
                .ScreenUpdating = True
                .DisplayAlerts = True
                .EnableEvents = True
            End With
            
    End Sub

  2. #2
    Forum Expert
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,448

    Re: excel VBA - setting sheet names within workbook copy using existing wb sheet names

    Maybe

    Set tws = .Sheets(sh.Name)
    My General Rules if you want my help. Not aimed at any person in particular:

    1. Please Make Requests not demands, none of us get paid here.

    2. Check back on your post regularly. I will not return to a post after 4 days.
    If it is not important to you then it definitely is not important to me.

  3. #3
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS 365 Family 64-bit 2404
    Posts
    24,758

    Re: excel VBA - setting sheet names within workbook copy using existing wb sheet names

    The short answer is

                    Set tws = sh
    Also there may be other problems with how Item is being used; I have not used Item for the Worksheets collection. If you have other problems try this:

            For Each sh In wb.Worksheets
                sh.Range("A1").CurrentRegion.Copy
    
                With twb
                    Set tws = Nothing
                    Set tws = sh
                    If tws Is Nothing Then
                        .Add after:=.Worksheets(.Worksheets.Count)
                        .Worksheets(Worksheets.Count).Name = sh.Name
                        Set tws = .Worksheets(.Worksheets.Count)
                    End If
                End With
    Last edited by 6StringJazzer; 03-25-2019 at 03:04 PM.
    Jeff
    | | |會 |會 |會 |會 | |:| | |會 |會
    Read the rules
    Use code tags to [code]enclose your code![/code]

  4. #4
    Forum Contributor
    Join Date
    02-25-2013
    Location
    Colorado, US
    MS-Off Ver
    Microsoft 365 Apps
    Posts
    518

    Re: excel VBA - setting sheet names within workbook copy using existing wb sheet names

    Thank you both. I tried both methods, but both throw errors.
    mehmetcik - method/data member not found on .Sheets
    Jeff - method/data member not found on .WorkSheets.Count

    I know it's generally frowned upon, but if I have On Error Resume Next initially, it works. The workbook is created, all sheets copied over, files saves and closes. I don't like that I have to put that in there, but if it works, it works I guess..unless you can see more clarity with the preceding thrown errors.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Copy sheet from another workbook based on ComboBox names of the Workbook and sheet
    By igormigor in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-01-2016, 10:07 AM
  2. [SOLVED] copy sheets to new workbook when sheet names are similar.
    By kleptilian in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-19-2016, 09:19 AM
  3. Replies: 3
    Last Post: 11-06-2014, 01:23 AM
  4. [SOLVED] Copy named sheets from one workbook to names sheet in other
    By abjac in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 08-30-2014, 07:42 AM
  5. [SOLVED] Copy Non-compliant Names to Another Sheet in The Same Workbook
    By BellaxPalus in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 08-21-2013, 05:43 AM
  6. [SOLVED] Copy Values From Each Workbook in Folder to a Single Sheet in New Workbook +Workbook names
    By Arsham24 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 09-21-2012, 07:42 PM
  7. Using Sheet names & Workbook names in VBA coding
    By Colin Foster in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 07-07-2006, 02:10 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1