each WorksheetSizes

[Seesee]

Hi,
Appear the error message : "1004 cannot copy the sheet" when run the below vba coding, please help to fix.

Sub WorksheetSizes()
Dim wks As Worksheet
Dim c As Range
Dim sFullFile As String
Dim sReport As String
Dim sWBName As String
sReport = "Size Report"
sWBName = "Erase Me.xls"
sFullFile = ThisWorkbook.path & _
Application.PathSeparator & sWBName
' Add new worksheet to record sizes
On Error Resume Next
Set wks = Worksheets(sReport)
If wks Is Nothing Then
With ThisWorkbook.Worksheets.Add(Before:=Worksheets(1))
.Name = sReport
.Range("A1").Value = "Worksheet Name"
.Range("B1").Value = "Approximate Size (kb)"
End With
End If
On Error GoTo 0
With ThisWorkbook.Worksheets(sReport)
.Select
.Range("A1").CurrentRegion.Offset(1, 0).ClearContents
Set c = .Range("A2")
End With
Application.ScreenUpdating = False
' Loop through worksheets
For Each wks In ActiveWorkbook.Worksheets
If wks.Name <> sReport Then
wks.Copy
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs sFullFile
ActiveWorkbook.Close SaveChanges:=False
Application.DisplayAlerts = True
c.Offset(0, 0).Value = wks.Name
c.Offset(0, 1).Value = Format(FileLen(sFullFile) / 1000, "#,##0.00")
Set c = c.Offset(1, 0)
Kill sFullFile
End If
Next wks
Range("a1:b1").WrapText = True
Application.ScreenUpdating = True
End Sub

 

[Michael M]

Are there hidden sheets in the workbook ? This can cause the error

 

[Seesee]

Are there hidden sheets in the workbook ? This can cause the error

Hi,
Thanks for your prompt reply. If keep invisible sheets, could you amend the coding to get same result. Thanks.

 

[Michael M]

You will have to unhide / hide the wks, like this
So after the for each line

For Each wks In Worksheets
wks.Visible = true

And then before the Next wks

wks.Visible =false
Next wks

 

[Seesee]

Than you so much Michael

 

[Michael M]

Glad to help

 

[Seesee]

….1st pop-up:show macro is not found and 2nd pop-up:show worksheet is not ready for copy successfully ….could you help me please

 

[Michael M]

How are you activating the macro?
Where is the macro located, module, worksheet, ??

 

[Seesee]

Good morning Michael
In vbe edtitor press F5 to execute a macro, the marco is in the same module under an excel project only…..
I try this code to execute by another desktop, it is okay…whether the excel file is got the virus

 

[Seesee]

Hi Michael,
How steps to make a screen capture ‘VBA Code:’ share with everyone?
Thank you for your teaching