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
Bookmarks