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

--
AmyTaylo
-----------------------------------------------------------------------
AmyTaylor's Profile: http://www.excelforum.com/member.php...fo&userid=2097
View this thread: http://www.excelforum.com/showthread.php?threadid=53920

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)

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

--
AmyTaylo
-----------------------------------------------------------------------
AmyTaylor's Profile: http://www.excelforum.com/member.php...fo&userid=2097
View this thread: http://www.excelforum.com/showthread.php?threadid=53920

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

It might be MISSING references, so go to ToolsReferences 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" wrote
in message ...

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