i'm using:
Set wb = Application.Workbooks.Open("c:\temp\temp.xlsx")
If Err.Number <> 0 Then
MsgBox Err.Description & "help"
Else
ws.Copy After:=wb.Sheets(wb.Sheets.Count) 'error: "Object required..." on this line
wb.Save
wb.Close
to copy out the worksheet to another file, but i get an error: "Object required..."
???
thank you.
Full Version: Save Worksheet To Another File
Where is ws defined?
What about something along the lines of:
What about something along the lines of:
CODE
Function Testing()
On Error GoTo Error_Handler
Dim wb As Workbook
Dim ws As Worksheet
Set ws = Application.ActiveSheet
Set wb = Application.Workbooks.Open("C:\Users\Daniel\Desktop\test.xlsx")
ws.Copy After:=wb.Sheets(wb.Sheets.Count)
wb.Close True 'close and save changes
Error_Handler_Exit:
On Error Resume Next
Set ws = Nothing
Set wb = Nothing
Exit Function
Error_Handler:
MsgBox "The following error has occured." & vbCrLf & vbCrLf & _
"Error Number: " & Err.Number & vbCrLf & _
"Error Source: YourProcedureName()" & vbCrLf & _
"Error Description: " & Err.Description, _
vbCritical, "An Error has Occured!"
Resume Error_Handler_Exit
End Function
On Error GoTo Error_Handler
Dim wb As Workbook
Dim ws As Worksheet
Set ws = Application.ActiveSheet
Set wb = Application.Workbooks.Open("C:\Users\Daniel\Desktop\test.xlsx")
ws.Copy After:=wb.Sheets(wb.Sheets.Count)
wb.Close True 'close and save changes
Error_Handler_Exit:
On Error Resume Next
Set ws = Nothing
Set wb = Nothing
Exit Function
Error_Handler:
MsgBox "The following error has occured." & vbCrLf & vbCrLf & _
"Error Number: " & Err.Number & vbCrLf & _
"Error Source: YourProcedureName()" & vbCrLf & _
"Error Description: " & Err.Description, _
vbCritical, "An Error has Occured!"
Resume Error_Handler_Exit
End Function
thank you...
Dim wb As Workbook
Dim ws As Worksheet
Set wb = Application.Workbooks.Open("c:\temp\temp.xlsx")
ws.Copy After:=wb.Sheets(wb.Sheets.Count)
wb.Save
wb.Close
i get the error..."object variable not set..."
thank you.
Dim wb As Workbook
Dim ws As Worksheet
Set wb = Application.Workbooks.Open("c:\temp\temp.xlsx")
ws.Copy After:=wb.Sheets(wb.Sheets.Count)
wb.Save
wb.Close
i get the error..."object variable not set..."
thank you.
QUOTE (mjschukas @ May 29 2012, 07:10 PM) 
"object variable not set..."
Exactly, the error message is very clear, where is your set statement for your ws variable? You set your wb, but you don't set your ws anywhere.
Also be careful when you set it, because you need to set it prior to opening the second workbook. So it should probably be the first thing you set in your code.
thank you...
...
and would you keep the error handling:
If Err.Number <> 0 Then
MsgBox Err.Description & "help"
???
thank you.
...
and would you keep the error handling:
If Err.Number <> 0 Then
MsgBox Err.Description & "help"
???
thank you.
That's up to you. I personally prefer the type of error handling I used in my sample procedure. It is very versatile and I have been using this structure for years now and never had any problems. As you can see, with it, you can display your error message and then cleanup after yourself.
merci...
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.