Problem with compiling this VBA - help please

[AmyTaylor]

Hi all, we have this VBA from Ron deBruins website, when we try to compile it brings back the error message "compile error Ccan't find 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
Thanks

[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" <[email protected]> wrote
in message news:[email protected]...
>
> Hi all, we have this VBA from Ron deBruins website, when we try to
> compile it brings back the error message "compile error Ccan't find
> 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
> Thanks
>
>
> --
> AmyTaylor
> ------------------------------------------------------------------------
> AmyTaylor's Profile:
http://www.excelforum.com/member.php...o&userid=20970
> View this thread: http://www.excelforum.com/showthread...hreadid=539200
>

[[email protected]]

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)

[AmyTaylor]

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

Sorry to be a pain !
Amy xx

[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.

--
Regards,
Tom Ogilvy


"AmyTaylor" wrote:

>
> Hi both, tried pasting again as suggested, it still doesnt work, it gets
> stuck at the word Left on the line
> FolderName = WbMain.Path & "\" & Left(WbMain.Name, Len(WbMain.Name) -
> 4)
>
> Sorry to be a pain !
> Amy xx
>
>
> --
> AmyTaylor
> ------------------------------------------------------------------------
> AmyTaylor's Profile: http://www.excelforum.com/member.php...o&userid=20970
> View this thread: http://www.excelforum.com/showthread...hreadid=539200
>
>