Problem with compiling this VBA - help please

  • Thread starter Thread starter AmyTaylor
  • Start date Start date
A

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
 
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)
 
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)
 
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
 
do what Bob said. After the error go to Tools=>References in the VBE and
resolve the reference that is shown as MISSING.
 
Back
Top