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