Problem with compiling this VBA - help please

[AmyTaylor]

Hi all, we have this VBA from Ron deBruins website, when we try t
compile it brings back the error message "compile error Ccan't fin
Project or Library".
What reference do I need to include to make this work ?
We are using vba 6.3
Many thanks for any help you can give.


Sub Copy_All_Sheets_To_New_Workbook()
Dim WbMain As Workbook
Dim Wb As Workbook
Dim sh As Worksheet
Dim DateString As String
Dim FolderName As String

Application.ScreenUpdating = False
Application.EnableEvents = False
DateString = "Now"
Set WbMain = ThisWorkbook
FolderName = WbMain.Path & "\" & Left(WbMain.Name, Len(WbMain.Name
- 4) '& " " & DateString
MkDir FolderName
For Each sh In WbMain.Worksheets
If sh.Visible = -1 Then
sh.Copy
Set Wb = ActiveWorkbook

' Use also this to make values from the formulas
' With Wb.Sheets(1)
' .UsedRange.Copy
' .UsedRange.PasteSpecial xlPasteValues
' .Cells(1).Select
' Application.CutCopyMode = False
' End With

Wb.SaveAs FolderName _
& "\" & Wb.Sheets(1).Name & ".xls"
Wb.Close False
End If
Next sh

MsgBox "Look in " & FolderName & " for the files"
Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub
Thank

 

[aidan.heritage]

The code runs fine when I copy and paste it - as long as it's running
from Excel it should work fine (same version of VBA - and cannot see
anything here that isn't in native VBA)

 

[Bob Phillips]

It might be MISSING references, so go to Tools>References and if any items
have MISSING in them, uncheck them,

If not, it might have been wrap-around, so try this

Sub Copy_All_Sheets_To_New_Workbook()
Dim WbMain As Workbook
Dim Wb As Workbook
Dim sh As Worksheet
Dim DateString As String
Dim FolderName As String

Application.ScreenUpdating = False
Application.EnableEvents = False
DateString = "Now"
Set WbMain = ThisWorkbook
FolderName = WbMain.Path & "\" & _
Left(WbMain.Name, Len(WbMain.Name) - 4)
MkDir FolderName
For Each sh In WbMain.Worksheets
If sh.Visible = -1 Then
sh.Copy
Set Wb = ActiveWorkbook

' Use also this to make values from the formulas
' With Wb.Sheets(1)
' .UsedRange.Copy
' .UsedRange.PasteSpecial xlPasteValues
' .Cells(1).Select
' Application.CutCopyMode = False
' End With

Wb.SaveAs FolderName _
& "\" & Wb.Sheets(1).Name & ".xls"
Wb.Close False
End If
Next sh

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

 

[AmyTaylor]

Hi both, tried pasting again as suggested, it still doesnt work, it get
stuck at the word Left on the line
FolderName = WbMain.Path & "\" & Left(WbMain.Name, Len(WbMain.Name)
4)

Sorry to be a pain !
Amy x

 

[Tom Ogilvy]

do what Bob said. After the error go to Tools=>References in the VBE and
resolve the reference that is shown as MISSING.